BIND DB2 Program

The BIND command builds an access path for DB2 statements coded in an application program. This process is essential for preparing DB2 programs to be run. The BIND command converts the SQL statements in the program into an executable form that DB2 can process. COBOL + DB2 program should bound to either package or plan. IKJEFT01 utility is used to bound the DBRM to the package or plan.

Tip! Refer Precompilation process before proceeding for better understanding.

Why BIND?

The precompilation process produces the load module (compiled and link-edited source code) and database request module (DBRM) for every application program. The DBRM is not the executable component to run the application program. To execute the application program, the DBRM should be bound to a package of a specific application plan. Once the bound is successful, then we can run the application program that has DB2 SQL statements.

What is BIND?

The bind process builds a connection between an application program and its relational data(table data). The binding process is responsible for detecting errors and correcting them before the execution of the program.

The Process -

DB2 BIND process completes the below actions -

Validates the object (tables, views, and column names) references in the SQL statements of the application program against the DB2 catalog.
Verifies the authorization of the program owner and authorization level to access the data that was requested by the SQL statements in the application program.
Selects the best access path to access the data by DB2. Table size, indexes, and other factors are considered while selecting the best access path.

Each package can contain only one DBRM or multiple DBRM. We can run a plan by specifying it. However, we can't run a package without specifying a plan name along with it. Because the plan is executable and the package is not executable.

IKJEFT01 utility is used to bind the DBRM to the package.

Note! In this topic, we have taken COBOL as an application language. So, we discussed COBOL + DB2 bind process below.

If the module is a batch module (COBOL + DB2), bound the module to package is sufficient to execute. Suppose the module is an online module (COBOL + DB2 + CICS). In that case, RCT entries also require to execute the module after bound it to the package.

BIND PACKAGE JCL -

Note! Below BIND PACKAGE JCL works as it is when you change your project-specific utility libraries and DBRM libraries.

BIND PACKAGE JCL -

----+----1----+----2----+----3----+----4----+----5----+
//MTH001B JOB MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*************************************************
//*  DB2 BIND JCL
//*************************************************
//BIND     EXEC PGM=IKJEFT01
//STEPLIB  DD  DISP=SHR,DSN=mthA10.DBAG.SDSNEXIT 
//         DD  DISP=SHR,DSN=mthA10.SDSNLOAD
//DBRMLIB  DD  DSN=MTH001.COBDB2.DBRMLIB,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSTSIN  DD  *
DSN SYSTEM (system-name)
BIND  PACKAGE   (package-name)      -
      MEMBER    (program-name)      -
      ACTION    (action-options)    -
      ISOLATION (isolation-options) -
      VALIDATE  (validate-options)  -
      RELEASE   (release-options)   -
      EXPLAIN   (explain-options)   -
      OWNER     (owner-id)          -
      QUALIFIER (qualifier-name)    -
      ENCODING  (encoding-options)
END
/*

BIND PLAN JCL -

Note! Below BIND PLAN JCL works as it is when you change your project-specific utility libraries and DBRM libraries.

BIND PLAN JCL Code -

----+----1----+----2----+----3----+----4----+----5----+
//MTH001B JOB MSGLEVEL=(1,1),NOTIFY=&SYSUID
//*************************************************
//*  DB2 BIND JCL
//*************************************************
//BIND     EXEC PGM=IKJEFT01
//STEPLIB  DD  DISP=SHR,DSN=MTHA10.DBAG.SDSNEXIT
//         DD  DISP=SHR,DSN=MTHA10.SDSNLOAD
//DBRMLIB  DD  DSN=MTH001.COBDB2.DBRMLIB,DISP=SHR
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*
//SYSTSIN  DD  *
DSN SYSTEM (system-name)
BIND  PLAN      (plan-name)         -
      MEMBER    (program-name)      -
      ACTION    (action-options)    -
      ISOLATION (isolation-options) -
      VALIDATE  (validate-options)  -
      RELEASE   (release-options)   -
      EXPLAIN   (explain-options)   -
      OWNER     (owner-id)          -
      QUALIFIER (qualifier-name)    -
      ENCODING  (encoding-options)
END
/*

MEMBER (program-name) -

It is the DBRM name used for the application program stored in the DBRM library.

PACKAGE (package-name) -

It contains optimized access path (OAP) that is generated by optimizer of BIND Process.
It is a non-executable component and can contain one or more DBRMs.
It is an intermediate component between the DBRM and PLAN.
The package's main advantage is that the rebound is not required when any change in the sub-program. Compilation of subprogram is only sufficient.
We should need to bind the plans locally regardless of packages are local or remote.
If the package is running at a remote location, the package needs to be bound at the remote location as well as local.

PLAN (plan-name) -

It also contains optimized access path (OAP) that is generated by optimizer of BIND Process.
It is an executable component and is used to execute the program.
It has the best access path to retrieve the data from the database by using the SQLs coded in the application program.
BIND PLAN command with ACTION(REPL) or ACTION(REPLACE) is used to assign the packages to the application plan.
Plans are collections of packages. Suppose we specify one or more DBRMs to include in the plan (using the MEMBER option of BIND PLAN). In that case, Db2 automatically binds those DBRMs into packages and then binds those packages into the plan.
The plan has the information about bound packages and data that the application programs panning to use.
The plan is stored in the DB2 catalog.

ACTION (action-options) -

It defines whether the plan or package replaces an existing one with the same name or is new.
The available options are REPLACE and ADD.

ISOLATION (isolation-options) -

It defines how far to isolate an application/application program from the effects of other running applications/application programs. In other words, how the program execution is isolated for accessing the database that is accessed by the other programs concurrently.
Multiple users can access the database at a time (Concurrency).
The isolation level is the way to maintain the concurrency for various users by setting the levels of access to the database.
ISOLATION is the BIND parameter that determines the page lock duration.
Types of isolation levels -
1. Cursor Stability (CS)
2. Repeatable Read (RR)
3. Read Stability (RS)
4. Uncommitted Read (UR)/Dirty Read (DR)

Isolation level is explained in DB2 tutorial.

VALIDATE (validate-options) -

It determines of rechecking of BIND or REBIND errors while the program is running.
The validate-options are RUN and BIND. If not coded anything, it takes RUN as default. i.e., VALIDATE(RUN).
The error types are "OBJECT NOT FOUND" and "NOT AUTHORIZED".
Validate will not throw any error messages during the BIND process. It throws warning messages at the time of program running.

RELEASE (release-options) -

It is used to decide when to release resources that are used by the program.
They are releasing the resource either at each commit point or when the program terminates.
The validate-options are COMMIT and DEALLOCATE.
For example, DB2 requires an exclusive lock to update a particular table. The Exclusive lock taken by the program is released whenever the options in the RELEASE is executed.
If COMMIT is coded in RELEASE, the Exclusive lock will not get released until or unless the COMMIT is executed.

EXPLAIN (explain-options) -

It is used to obtain information about how SQL statements are executed in the package or plan.
The available options are - NO and YES.

OWNER (owner-id) -

It specifies the owner authorization id of the plan or package.
The owner should have the required privileges to execute the SQL statements from the program.

QUALIFIER (qualifier-name) -

It specifies the implicit qualifier for the tables, views, indexes, and aliases in the plan or package.
The qualifier is mainly used when performing BIND for remote systems.

ENCODING (encoding-options) -

It specifies application encoding for all host variables used in SQL statements in plan or package.
The available options are - ASCII, EBCDIC, UNICODE, and ccsid.

LIB -

It is the library used to specify the partitioned dataset where the DBRMs are stored.
If the DBRMLIB is not coded explicitly in the JCL, the LIB option with the DBRM library in the BIND command is required.
If the DBRMLIB is coded explicitly, the LIB option with the DBRM library in the BIND command is not required.

Runtime Supervisor -

It is responsible for validating the timestamps of COBOL and DB2 object codes. It checks the timestamp tokens of the object code of COBOL (T1) & object code of DB2 (T2).
If T1=T2, the program is ready for execution. If T1!=T2, the program ABENDs with SQLCODE -818 (timestamp mismatch error).

Practical Example -

Scenario - Binds the COBOL + DB2 program (SELECT1) to the plan (MATEPKA) using isolation level CS.

Input required -

DBRMLIB - MATEPK.COBDB2.DBRMLIB
PLAN - MATEPKA

BIND JCL Code -

Full Example

----+----1----+----2----+----3----+----4----+----5----+
...
//BIND     EXEC PGM=IKJEFT01
//STEPLIB  DD  DISP=SHR,DSN=DSNA10.DBAG.SDSNEXIT 
//         DD  DISP=SHR,DSN=DSNA10.SDSNLOAD
//DBRMLIB  DD  DSN=MATEPK.COBDB2.DBRMLIB,DISP=SHR
//SYSTSIN  DD  *
DSN SYSTEM (DBAG)
BIND  MEMBER    (SELECT1 ) -
      PLAN      (MATEPKA ) -
      ACTION    (REP)      -
      ISOLATION (CS)       -
      VALIDATE  (BIND)     -
      RELEASE   (COMMIT)   -
      OWNER     (MATEPK )  -
      QUALIFIER (MATEPK )  -
      ENCODING  (1047) 
END
/*
...