As an application programmer, you are probably accustomed to writing source code and preparing it for execution through compile and link-edit steps. If you use CIC5, you are also familiar with a translate step. Preparation of DB2 programs requires two additional steps—precompile and BIND-and modifications to your current RUN procedures.

DB2 separates the application program's source language statements (COBOL in our examples) from its SQL statements. These two sets of code are then processed in two separate streams and rejoined at execution time. Precompile, BIND, and RUN are the three steps needed to do this. Precompile separates the source code into two streams, source and SQL; BIN processes the SQL statements that are the output of the precompile step; and RUN rejoins them for execution,

Let's look at precompile now in more detail. DB2's Precompiler reads the source code, comments out the SQL statements, and replaces them with calls to DB2. It also checks each SQL statement for syntax errors and compares tables and columns named in the statements against any SQL DECLARE TABLE statements in the program. The Precompiler does not refer to DB2 Catalog tables, so DB2 does not have to be up to run the Precompiler. However, this also means that its ability to find invalid references is only as good as the program's DECLARE TABLE statements. If you type your DECLARE TABLE statements yourself rather than using the output of DCLGEN, they may not be very good. DCLGEN, the declarations generator supplied with DB2, does reference the Catalog when it produces copybook members, which is why we recommend its use prior to your program preparation. The Precompiler retrieves these and other copy members specified in SQL INCLUDE statements (like the SQLCA which DB2 supplies) and merge them into the code. The output of the Precompiler is the modified source code, a Data Base Request Module (DBRM), which holds the source code's SQL statements, and a listing, which includes error messages.

DB2 Program Preparation

DB2 Program Preparation

The modified source code that is produced by the Precompiler must be compiled and link-edited. For CICS programs, an additional CICS translate step must also be included. The output of the link-edit step is a load module.

The DBRM that is created by the precompile step is the input to the BIND. DB2 must be up to execute a BIND, which checks SQL statements for syntax errors and compares tables and columns named in the statements against the DB2 Catalog. It also verifies the authority of the binder to perform the SQL statements specified in the program. The authorization validation process can be postponed until run-time through an option on the BIND panel. Most importantly, BIND invokes DB2's Optimizer for access path selection. The Optimizer considers indexes, table sizes, etc., to decide the paths to use in selecting data. The object code which describes how each SQL request, should be satisfied is called the application plan and is the compiled output from the BIND. Application plans are stored in DB2's Directory.

The output of the link-edit step, the load module, and the output of the BIND step, the application plan, are the input to the RUN step. The sequence in which these steps must be run is as follows. DCLGEN must be run before the Precompiler, which must be run before the COBOL compiler and before the BIND. BIND may be run any time between the precompile and RUN steps. Link-edit may be run any time between the compile and RUN steps. The separation of source language and SQL statement processing could lead to inconsistency if you changed your program, precompiled, compiled, and link-edited it without rebinding the changed DBRM. The load module and the application plan would be out of synch. DB2 prevents this through a system of timestamp comparisons.

Timestamp Comparison (Timestamps on the load module and application plan must be the same)

Timestamp Comparison (Timestamps on the load module and application plan must be the same)

When you precompile a program, a timestamp is put in the DBRM and the COBOL output. BIND passes this timestamp to the application plan just as COMPILE and LINK-EDIT pass it to the load module. During program execution, when the first call is made to DB2, DB2 compares the timestamps in the load module and the application plan. If they do not match, you get an -818 SQLCODE and an abend. To avoid the abend, whenever you change a program and execute a new precompile, compile, and link-edit, you also have to issue a BIND, even if the SQL has hot changed. This puts the new timestamp into the corresponding application plan.

The steps of program preparation—DCLGEN, precompile, BIND, compile, link-edit, and RUN—can be done through batch iCL, as a TSO foreground job, or through the panels supplied by DBBI.

DCLGEN Command

The DCLGEN or Declaration Generator command is used to produce a COBOL copybook, which contains a SQL DECLARE TABLE statement along with the WORKING-STORAGE host-variable definitions for each column of the table. When the DCLGEN command is issued DB2 reads the catalog to determine the structure of the table and builds the COBOL copybook.

DCLGEN is not a required step because whatever that is generated using the DCLGEN command can be hard-coded in the application program. But it is a good practice to run the DCLGEN command for every table that will be embedded in a COBOL program. Then every program that accesses that table can INCLUDE the generated copybook. This reduces a lot of unnecessary coding. But one thing that must be remembered is that, DCLGEN will generate the host variables with the same name as the column name and if the program uses two tables, which have common column names, then edit the copybook and change the names.


The DB2 application program contains COBOL code with SQL statements embedded in it. The COBOL compiler will not be able to recognize the SQL statements and will give compilation errors. So before running the COBOL compiler, the SQL statements must be removed form the source code. This and some more things are achieved during the precompiler step. In fact the precompiler does the following:

  • Searches for and expands DB2 related INCLUDE members
  • Searches for SQL statements in the body of the program's source code
  • Creates a modified version of the source program in which every SQL statement in the program is commented and replaced with a CALL to the DB2 runtime interface module, along with applicable parameters
  • Extracts all the SQL statements and places them in a Database Request Module (DBRM)
  • Places a timestamp token in the modified source and the DBRM to ensure that these two items are inextricably tied
  • Reports on the success of failure of the precompile process


The BIND command is a type of compiler for SQL statements. BIND reads SQL statements from DBRMs and produces a mechanism to access data as directed by the SQL statements being bound. There are two types of BlNDs - BIND PLAN and BIND PACKAGE. BIND PLAN accepts one or more DBRMs produced from previous DB2 precompilations, one or more packages produced from previous BIND

PACKAGE commands or a combination of DBRMs and Package lists as input. The output of the BIND PLAN is an application plan containing the executable logic representing optimized access paths to DB2 data. An application plan is executable only with the corresponding load module. Before you can run a DB2 program, an application plan name must be specified.

The BIND PACKAGE command accepts as input and produces a single package containing the optimized access path logic. You can then bind the packages into an application plan using the BIND PLAN command. A package is not executable and cannot be specified when a DB2 program is being run. You must bind a package into a plan before using it. BIND performs the following functions:

  • Reads the SQL statements in the DBRM and checks the syntax of those statements.
  • Checks that the DB2 tables and columns being accessed confirm to the corresponding DB2 catalog information.
  • Performs authorization validations.
  • Optimizes the SQL statements into efficient access paths.


After compilation the compiled source is link-edited to an executable load module. The appropriate DB2 host language interface module also must be included in the link-edit step. The interface module is based on the environment in which the program will execute. The output of the link-edit step is an executable load module, which can be run with a plan containing the program's DBRM or package.

Running a DB2 Program

After the above-mentioned steps are completed, two separate physical components are produced; the link-edited load module and the DB2 plan. Neither is executable without the other. The plan contains the access path specifications for the SQL statements in the program and load module contains the executable machine instructions for the COBOL statements in the program. DB2 programs can be run in TSO batch. Call attach, CICS or in IMS.

Methods of Program Preparation

You can prepare a DB2 program in many ways. Some of them are using Interactive DB2 (DB2I) panels, using the standard DB2 program preparation procedure, using CLIST or REXX, etc. DB2I is an on-line TSO/ISPF based interface to DB2 commands, DB2 functions and CLISTs provided by DB2. It is a panel or menu driven application that allows a user to prepare the DB2 program. In the main DB2I panel you have the options to process SQL statements (SPUFI), Generate SQL and source language declarations (DCLGEN), prepare the program, precompile it, bind/rebind/free, Run, Issue DB2 commands, invoke DB2 utilities, set global parameters, etc. After setting the DB2I defaults, next step is to create the DCLGEN members for all the tables that will be used. Then using the DCLGEN copybooks and the source COBOL code with embedded SQL statements, you can precompile your program using DB2I form the Precompile panel. The precompiler can run in the foreground or in the background. The DB2 plans and packages can be bound, rebound or freed using the option 5 of DB2I. The option 3 of the DB2I panel, that is Program preparation, will take you step-by-step through the entire DB2 program preparation procedure displaying the appropriate panels. By entering the appropriate selections in the program preparation panel, you can completely prepare and run the source program.

Another option that is followed in some installations is to create a CLIST or REXX EXEC that can be invoked to prompt the user to enter program preparation options. The CLIST or REXX EXEC reads the options as specified by the programmer and builds the JCL to invoke the program preparation using those parameters. This method lets the programmer to change the precomile, compile' and link-edit parameters, without requiring them to explicitly change parameters in the JCL that they may not fully understand. This method can be used to force certain options, by not allowing the users to change them.

A DBRM is nothing more than a module containing SQL statements extracted from the source program by the DB2 precompiler. It is stored as a member of a partitioned data set. It is not stored in the DB2 catalog or directory. Although there is a DB2 catalog table named SYSIBM.SYSDBRM, it does not contain the DBRM. It consists of information about DBRMs that have been bound into application plans and packages. If aDBRM is created and never bound, it is not referenced in this table.

When a DBRM is bound to a plan, all the SQL statements are placed into the SYSIBM.SYSSTMTDB2 catalog table. When a DBRM is bound to a package, all of its SQL statements are placed into the SYSIBM.SYSPACKSTMT table.

Application Plan

A plan is an executable module containing the access path logic provided by the DB2 optimizer. It can be composed of one or more DBRMs and packages. Plans are created by the BIND command. When a plan is bound, DB2 reads the following catalog tables: SYSIBM.SYSCOLDIST, SYSIBM.SYSCOLUMNS, SYSIBM.SYSFIELDS, SYSIBM.SYSINDEXES, SYSIBM.SYSPLAN, SYSIBM.SYSPLANAUTH, SYSIBM.SYSTABLES, SYSIBM.SYSTABLESPACE and SYSIBM*SYSUSERAUTH.



A package is a single, bound DBRM with optimized access paths. Before DB2 V2.3 the only bind option available was at the plan level. By using packages, the table access logic is packaged at a lower level for granularity - at the package or program level.

To execute a package it must be first included in the package list of a plan. Packages can never be directly executed. They are only executed when the plan in which they are contained is executed. A pan can consist of one or more DBRMs, one or more packages, or a combination of packages and DBRMs.

Package information is stored in its own DB2 catalog tables. When a package is bound, DB2 reads the following catalog tables: SYSIBM.SYSCOLDIST, SYSIBM.SYSCOLUMNS,SYSIBM.SYSFIELDS,SYSIBM.SYSINDEXES, SYSIBM.SYSPACKAGE, SYSIBM.SYSPACKAUTH, SYSIBM.SYSTABLES, SYSIBM.SYSTABLESPACE and SYSIBM.SYSUSERAUTH. Of the above tables the SYSIBM.SYSUSERAUTH table is read only for BIND ADD. Information about the plan is stored in the following DB2 catalog tables: SYSIBM.SYSPACKAGE, SYSIBM.SYSPACKAUTH, SYSIbM.SYSPACKDEP, SYSIBM.SYSPLSYSTEM, SYSIBM.SYSSSTMT and SYSIBM.SYSTABAUTH.

The DB2 catalog stores only information about the packages. The executable form of the package is stored as a skeleton package table in the DB2 directory in the SYSIBM.SPT01 table. A package also contains a location identifier, a collection identifier and a package identifier. These identifiers are used to uniquely identify the package.


A collection is a user-defined name (1 to 18 characters) that the programmer must specify for every package. A collection is not an actual, physical database object. A collection is a grouping of DB2 packages. By specifying different collection identifier for a package," the same DBRM can be bound to different packages. This capability permits the programmers to use the same DBRM for different packages, enabling easy access to tables that have the same structure but different owners.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

IBM Mainframe Topics