BIND
Prepares a program's SQL ahead of time and stores the plan Db2 will use to run it.
Also written package, plan, rebind
Why it matters. A COBOL program with SQL cannot run until it is bound. Change the SQL, and you bind again.
See it
IKJEFT01 runs TSO commands in batch.
Syntax
BIND PLAN(plan) MEMBER(dbrm) [ACTION(REPLACE)] BIND PACKAGE(collection) MEMBER(dbrm) [ACTION(REPLACE)] BIND PLAN(plan) PKLIST(collection.*)
- PLAN
- The plan to make.
- MEMBER
- The DBRM (database request module) the precompiler wrote.
- PACKAGE
- Bind a DBRM as a package in a named collection.
- PKLIST
- The packages a plan may use.
- ACTION
- ADD (new) or REPLACE (change an existing one).
- ISOLATION
- How much a program sees of other users' changes: CS, RS, RR or UR.
- QUALIFIER
- The schema added to table names written without one.
- VALIDATE
- Check table access at bind time (BIND) or when the program runs (RUN).
Try it BIND PLAN
- 1
You're in the SYSTSIN lines of job ZBBIND. DSN SYSTEM(DB2P) is above you, and END is below. Write the command that binds the plan ZBPLAN from the member ZBINT01.
SYSTSIN BIND PLAN(ZBPLAN) MEMBER(ZBINT01) - 2
Submit the job ZBBIND from your DEV JCL library.
SUBMIT 'ZB0001.DEV.JCL(ZBBIND)' - 3
Read the output of the job.
OUTPUT ZBBIND
Remember
- BIND stores how Db2 will run the program's SQL. Modern sites bind a package (BIND PACKAGE), then name it in a plan with PKLIST.
- BIND runs under the DSN command, in a batch job or in TSO. Binding a DBRM straight into a plan, as here, is deprecated by IBM.
- Change the SQL, precompile, compile and link-edit again, then bind again.
The usual mistake
Running the program after you changed its SQL, without a new bind
The plan still holds the old SQL. The program and the plan no longer match. Db2 compares their timestamps (the consistency token) and refuses with SQLCODE -818.
Precompile, compile, link-edit, then BIND again
On real z/OS
MF/OS prints DSNT200I but binds nothing. The SQL is read when the program runs, so SQLCODE -805 and -818 can't happen. A real bind also checks that the tables exist and that you may use them. IBM deprecated binding a DBRM straight into a plan (BIND PLAN MEMBER) in Db2 12, and recommends packages.
Where can I practise BIND?
Right here, at ZedBank. You take a junior job at a simulated bank and use BIND in real work, with Zed beside you. There's nothing to install.
Start at ZedBankRelated
- Embedded SQLSQL statements written directly inside a COBOL program, marked off so they can be prepared before compiling.
- DB2 for z/OSIBM's relational database on the mainframe, where a great deal of the world's banking and insurance data is stored.
- SQLThe language used to query and change relational data. The same SQL used everywhere else, running against mainframe tables.
- CompileTurning COBOL source code into machine instructions, checking the syntax and producing a listing of any errors.
- IndexA structure that lets a database find rows by value without reading the whole table.