Processing SQL statements for program preparation
The first step in preparing an SQL application to run is to process the SQL statements in the program. To process the statements, use the Db2 coprocessor or the Db2 precompiler. During this step, the SQL statements are replaced with calls to Db2 language interface modules, and a DBRM is created.
Before you begin
Ensure that your application development programming languages meet the minimum requirements listed in "Programming Languages" in Db2 13 Program Directory. See Program directories for Db2 13.
About this task
Because most compilers do not recognize SQL statements, you can prevent compiler errors by using either the Db2 coprocessor or the Db2 precompiler.
You can use the Db2 coprocessor for the host language. When you use the Db2 coprocessor, the compiler (rather than the Db2 precompiler) scans the program and returns the modified source code. The Db2 coprocessor also produces a DBRM.
The 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.
The Db2 precompiler scans the program and returns modified source code, which you can then compile and link edit. The precompiler also produces a DBRM (database request module). You can bind this DBRM to a package using the BIND subcommand. When you complete these steps, you can run your Db2 application.
- Db2 version in DSNHDECP module
- When you process SQL statements in your program, if the Db2 version in DSNHDECP is the default system-provided version, Db2 issues a warning and processing continues. In this case, ensure that the information in DSNHDECP that Db2 uses accurately reflects your environment.
Procedure
To process SQL statements in application programs, use one of the following methods:
Results
- Db2 coprocessor or Db2 precompiler output
-
The main output from the Db2 coprocessor or Db2 precompiler is a database request module (DBRM). However, the Db2 coprocessor or 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 coprocessor or Db2 precompiler
-
The Db2 coprocessor or 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 the semantic checking done when the Db2 coprocessor or Db2 precompiler 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 joins. Also, when an IN‑list predicate includes a subquery, the semantic checking bypasses compatibility checks for columns in that predicate. 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
If the application contains CICS® commands, you must translate the program before you compile it. For more information, see Translating command-level statements in a CICS program.