Processing SQL statements by using the Db2 precompiler

The Db2 precompiler scans a program and copies all of the SQL statements and host variable information into a database request module (DBRM ). The Db2 precompiler also returns source code that has been modified so that the SQL statements do not cause errors when you compile the program.

Before you begin

Ensure that your application development programming languages meet the minimum requirements listed in "Building applications by using the Db2 precompiler" in Db2 13 Program Directory. See Program directories for Db2 13.

Tip: Start of changeThe Db2 coprocessor is the recommended method for processing SQL statements in application programs. Compared to the Db2 precompiler, the Db2 coprocessor has fewer restrictions on SQL programs, and more fully supports the latest SQL and programming language enhancements. See Processing SQL statements by using the Db2 coprocessor.End of change

Start of changeIf you are using the Db2 precompiler, ensure that the names of host variables and host-variable arrays are unique within the program, even if the variables and variable arrays are in different blocks, classes, procedures, functions, or subroutines. You can qualify the names with a structure name to make them unique.End of change

Tip: Before you run the Db2 precompiler, use the declarations generator (DCLGEN) to obtain accurate SQL DECLARE TABLE statements. The Db2 precompiler does limited checking of table and column references against SQL DECLARE TABLE statements in the program, not the actual tables and columns in the Db2 catalog. For more information, see Generating table and view declarations by using DCLGEN.

About this task

After the SQL statements and host variable information are copied into a DBRM and the modified source code is returned, you can compile and link-edit this modified source code.

The following figure illustrates the program preparation process when you use the Db2 precompiler. After you process SQL statements in your source program by using the Db2 precompiler, you create a load module, possibly one or more packages, and an application plan. Creating a load module involves compiling the modified source code that is produced by the precompiler into an object program, and link-editing the object program to create a load module. Creating a package or an application plan, a process unique to Db2, involves binding one or more DBRMs, which are created by the Db2 precompiler, using the BIND PACKAGE command.

Figure 1. Program preparation with the Db2 precompiler
Begin figure summary.The figure shows the process of using the DB2 precompiler to create a load module and a plan. Detailed description available.

Db2 does not need to be active when you precompile your program.

You do not need to precompile the program on the same Db2 subsystem on which you bind the DBRM and run the program. You can bind a DBRM and run it on a Db2 subsystem at the previous release level, if the original program does not use any properties of Db2 that are unique to the current release. You can also run applications on the current release that were previously bound on subsystems at the previous release level.

Procedure

To process SQL statements by using the Db2 precompiler:

  1. Ensure that your program is ready to be processed by the Db2 precompiler by performing the following actions.
    For information about the criteria for programs that are passed to the Db2 precompiler, see Input to the Db2 precompiler.
  2. If you plan to run multiple precompilation jobs and are not using the DFSMSdfp partitioned data set extended (PDSE), change the Db2 language preparation procedures (DSNHCOB, DSNHCOB2, DSNHICOB, DSNHFOR, DSNHC, DSNHPLI, DSNHASM, DSNHSQL) to specify the DISP=OLD parameter instead of the DISP=SHR parameter.
    The Db2 language preparation procedures in job DSNTIJMV use the DISP=OLD parameter to enforce data integrity. However, the installation process converts the DISP=OLD parameter for the DBRM library data set to DISP=SHR, which can cause data integrity problems when you run multiple precompilation jobs.
  3. Start the precompile process by using one of the following methods:
    Recommendation: Specify the SOURCE and XREF precompiler options to get complete diagnostic output from the Db2 precompiler. This output is useful if you need to precompile and compile program source statements several times before they are error-free and ready to link-edit.

Results

Db2 precompiler output

The main output from the Db2 precompiler is a database request module (DBRM). However, the Db2 precompiler also produces modified source statements, a list of source statements, a list of statements that refer to host names and columns, and diagnostics. For more information, see Output from the Db2 precompiler.

Limitations of semantic checking by the Db2 precompiler

The Db2 precompiler does limited semantic checking of data references in the SQL statements of the program. These checks are done against table declarations in the program and not the Db2 catalog. Examples of semantic checking done by the Db2 precompiler when it processes SQL statements include the following checks:

  • The program has at least one table declaration if it contains table or column references.
  • For singleton SELECT statements, the number of INTO host-variable clauses matches the number of select-list values, and each host variable has a compatible type.
  • For UPDATE statements, each column being set is compatible with the value it is being set to.
  • For INSERT statements, the number of values is not greater than the number of columns in the table, and the values to insert have compatible types.
  • Limited validity checking of DATE and TIME values, allowing for use CHAR data types.
  • For all SQL statements, the operands of arithmetic operations are numeric and operations of relational operations such as BETWEEN or IN are mutually compatible.

The semantic checking ends in situations where no table list exists, such as when statements contain nested table expressions or outer joins. In these situations, checks from the preceding list can be deferred until the bind process, and full checking of column and table references is always deferred until the bind process, when these references are checked against the Db2 catalog.

What to do next

Preparing a program with object-oriented extensions by using JCL
If your C++ or Enterprise COBOL for z/OS® program satisfies both of these conditions, you need special JCL to prepare it:
  • The program consists of more than one data set or member.
  • More than one data set or member contains SQL statements.

You must precompile the contents of each data set or member separately, but the prelinker must receive all of the compiler output together.

JCL procedure DSNHCPP2, which is in member DSNTIJMV of data set DSN1310.SDSNSAMP, shows you one way to do this for C++.

Precompiling a batch program
When you add SQL statements to an application program, you must precompile the application program and bind the resulting DBRM into a package, as described in Preparing an application to run on Db2 for z/OS.