BIND PROCESS

What BIND?

The bind process takes the DBRM (Database Request Module) generated from the precompilation of a COBOL + DB2 program. It links the DBRM to a package or plan to establish the connection between an application program and its specific DB2 database. This process ensures that the SQL statements in the program are optimized, validated, and authorized for execution within the DB2 database.

The bind step is essential because:

1. It establishes a link between the COBOL program and the DB2 database.
2. It ensures that SQL statements are optimized for performance.
3. It validates whether the program has the necessary privileges to access DB2 objects.

Process -

DB2 BIND process completes the below actions -

1. Optimizes SQL statements & Validates SQL syntax: Validates the object (tables, views, and column names) references in the SQL statements of the application program against the DB2 catalog.
2. Validates authorization: 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.
3. Creates a package or plan: 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 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 plan or package.

If we are doing manual bind using JCL, we need the package or plan details. If we are compiling the module using version control tools (Endevor, changeman etc), bind step will be completed along with compilation of the module.

BIND 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
//SYSTSIN  DD  *
  DSN SYSTEM (system-name)
  BIND  PACKAGE (package-name) or 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
/*
//SYSPRINT DD  SYSOUT=*
//SYSTSPRT DD  SYSOUT=*
//SYSUDUMP DD  SYSOUT=*

BIND -

• BIND is the process of finding the optimized access path to retrieve the data from the DB2 for the SQLs coded in the program.
• The BIND process also checks some of the syntax errors of DB2 statements. For Example - Whenever we use the GROUP BY and ORDER BY clause, we get a BIND error if we can't specify the column in SELECT.
• It takes DBRM as input and generates the optimized access path with the help of an optimizer that considers the factors below -
  • Table size
  • Number of columns retrieving
  • Number of rows retrieving
  • The availability of primary key
  • The availability of Foreign key
  • And many more.

PACKAGE (package-name) -

• A compiled and bound version of SQL statements from a single DBRM contains an optimized access path (OAP) generated by the optimizer of the BIND Process.
• The package is an intermediate component between the DBRM and PLAN.
• It is a non-executable component and can contain one or more DBRMs.
• The package's main advantage is that the rebound is not required when changing the sub-program. Compiling subprograms alone is only sufficient.
• We should bind the packages locally regardless of whether they are local or remote.

PLAN (plan-name) -

• The plan also contains an optimized access path (OAP) but is an executable component.
• It is a collection of packages and used to execute the program.
• The plan 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) assigns the packages to the application plan.
• The plan contains information about bound packages and data the application programs will use.
• The plan is stored in the DB2 catalog.

MEMBER (program-name) -

• MEMBER is the DBRM name where the application program DBRM stored.

ACTION (action-options) -

• It specifies whether the plan or package should be replaced or not.
• The available options are REPLACE and ADD.

ISOLATION (isolation-options)

• The ISOLATION option defines how far to isolate an application/application program from the effects of other running applications/application programs.
• Multiple users can access the database at a time (Concurrency).
• The Isolation level is how 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) or Dirty Read (DR)

VALIDATE (validate-options) -

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

RELEASE (release-options) -

• The RELEASE option is used to decide when to release resources that are used by the program.
• They release the resource at each commit point or when the program terminates.
• The validate-options are COMMIT and DEALLOCATE.
• If COMMIT is coded in RELEASE, the Exclusive lock will only get released once or if the COMMIT is executed.

EXPLAIN (explain-options) -

• The EXPLAIN option obtains information about how SQL statements are executed in the package or plan. The available options are - NO and .

OWNER (owner-id) -

• The OWNER option specifies the owner-authorized ID of the plan or package.
• The owner should have the privilege to execute the SQL statements from the program.

QUALIFIER (qualifier-name) -

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

ENCODING (encoding-options) -

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

LIB -

• LIB is the library that specifies the PDS where the DBRMs are stored.
• If the DBRMLIB is not coded explicitly in the JCL, the LIB option with the DBRM library is required in the BIND command.
• If the DBRMLIB is coded explicitly, the LIB option with the DBRM library in the BIND command is not needed.

Practical Example -

Scenario - Below example describes how the COBOL + DB2 program bind process using JCL.

Input required -

• DBRMLIB - MATEGJ.COBDB2.DBRMLIB
• PLAN - MATEGJC

BIND JCL Code -

----+----1----+----2----+----3----+----4----+----5----+
//MATEGJB JOB MSGLEVEL=(1,1),NOTIFY=&SYSUID
...
//BIND     EXEC PGM=IKJEFT01
...
//DBRMLIB  DD  DSN=MATEGJ.COBDB2.DBRMLIB,DISP=SHR
//SYSTSIN  DD  *
DSN SYSTEM (DBAG   )
BIND  PLAN      (MATEGJC ) -
      MEMBER    (SELECT1 ) -
      ACTION    (REP)      -
      ISOLATION (CS)       -
      VALIDATE  (BIND)     -
      RELEASE   (COMMIT)   -
      OWNER     (MATEGJ )  -
      QUALIFIER (MATEGJ )  -
       ENCODING  (1047)
 END
 /*
...

JOB Result - MAXCC

JOB Result - BIND Step

JOB Result - BIND Step Final Report