DB2 precompiler and coprocessor

What the SQL statement processor does

The first step in preparing an SQL application is to process the SQL in the program. The processor:

• Recognizes SQL — statements delimited by EXEC SQL … END-EXEC (COBOL), EXEC SQL … ; (C/C++), or the PL/I / Assembler equivalents
• Copies SQL and host-variable descriptions into a database request module (DBRM)
• Replaces SQL in the object the compiler sees with calls to the Db2 language interface so the host compiler never has to parse SELECT
• Stamps a consistency token into both the DBRM and the generated interface code so BIND and run time can pair the load module with the matching package

The precompiler is a separate job step that reads your source and writes modified source (typically SYSCIN) plus the DBRM. The coprocessor is inside the compiler: one compile step both compiles the language and writes the DBRM. Either way you still BIND PACKAGE before the SQL can run.

Db2 precompiler versus Db2 coprocessor

IBM’s tip in the Application Programming and SQL Guide is explicit: the coprocessor is the recommended method. It has fewer restrictions on SQL programs and more fully supports the latest SQL and programming-language enhancements. Shops still run DSNHPC because existing JCL, change-control, and DBRM promotion pipelines were built around a distinct precompile step.

• Precompiler (DSNHPC) — extra step, its own SYSLIB for EXEC SQL INCLUDE, generates an SQL-INIT section in COBOL (reset SQL-INIT-FLAG in called programs that reuse host variables at new addresses)
• Coprocessor — compiler option SQL, one SYSLIB for INCLUDE and COBOL COPY, no SQL-INIT-FLAG dance, smaller generated code, continuation rules that match the compiler more strictly

COBOL differences you must plan for

Enterprise COBOL documents several behavior changes when you leave DSNHPC for the integrated coprocessor:

• Continuation lines — precompiler: EXEC SQL starts in columns 12–72; continuation can start in 8–72. Coprocessor: every line of the statement, including continuation, must stay in columns 12–72
• FOR BIT DATA — the precompiler lets an alphanumeric host variable hold bit data without DECLARE VARIABLE. The coprocessor converts using CCSID rules unless you specify NOSQLCCSID, add EXEC SQL DECLARE VARIABLE … FOR BIT DATA, or let DCLGEN emit that with DCLBIT(YES)
• Host-variable uniqueness — the coprocessor requires unique host variable references; the precompiler was more lenient about names in different sections
• INCLUDE period — COBOL coprocessor wants a period on EXEC SQL INCLUDE the way other COBOL statements do; the precompiler did not require it
• File Section host variables — not allowed; the coprocessor enforces WORKING-STORAGE / LINKAGE as the Application Programming Guide states

If both COBOL SQL and SQLCCSID are in effect, COBOL passes the CODEPAGE CCSID to Db2. When that CCSID differs from DSNHDECP, Db2 converts character data. Conversion of data you thought was “bit” in a VARCHAR is a classic integrity incident. Match CODEPAGE to DSNHDECP, or use NOSQLCCSID during migration.

Precompiler options and SQL processing options

You can specify SQL processing options on DSNHPC PARM or inside the compiler SQL("…") list. The coprocessor may ignore options that duplicate a real compiler option. Decide NOFOR and STDSQL(YES) before you write the program — they change what you must code.

APOST / QUOTE are COBOL delimiters only. They do not set the SQL string delimiter. Use APOSTSQL (apostrophe delimiter, quotation mark escape) or QUOTESQL (the reverse). If you omit both in COBOL, the install field SQL STRING DELIMITER on panel DSNTIPF applies. Non-COBOL languages use apostrophe as the SQL delimiter.

STDSQL(YES) requires BEGIN DECLARE SECTION and END DECLARE SECTION around host variables. ONEPASS requires each host variable to be declared before it is used in SQL. TWOPASS (common with the precompiler) requires the declaration before use in DECLARE CURSOR. IBM recommends SOURCE and XREF so listings show every SQL line and every host name / column reference while you iterate.

1
2
3
4
5
6
7
8
9
10
11
//PRECOMP EXEC PGM=DSNHPC,
//            PARM='HOST(IBMCOB),SOURCE,XREF,APOST,APOSTSQL,STDSQL(NO)'
//STEPLIB  DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//DBRMLIB  DD DISP=SHR,DSN=PAYROLL.DBRMLIB.DATA
//SYSIN    DD DISP=SHR,DSN=PAYROLL.COBOL.SOURCE(PAYCALC)
//SYSLIB   DD DISP=SHR,DSN=PAYROLL.DCLGEN.COPYLIB
//SYSCIN   DD DSN=&&SYSCIN,DISP=(NEW,PASS),UNIT=SYSDA,
//            SPACE=(CYL,(1,1))
//SYSPRINT DD SYSOUT=*
//SYSUT1   DD UNIT=SYSDA,SPACE=(CYL,(1,1))
//SYSUT2   DD UNIT=SYSDA,SPACE=(CYL,(1,1))

DBRM, SQL source, and host-language source

Think of two source streams. SQL source is every EXEC SQL block, every host variable referenced in SQL, INCLUDE members that contain SQL, and DECLARE TABLE text. Host language source is everything the COBOL/C/PL/I/Assembler compiler must accept: divisions, paragraphs, #includes that are not SQL, COPY books that are not INCLUDE.

The DBRM member name is usually the program / DBRM name you later put on BIND PACKAGE … MEMBER(name). VERSION on the precompiler or coprocessor becomes the package version. The consistency token is generated unless you override it; at run time Db2 matches the token in the load module to the package. If you recompile without binding, or bind a different DBRM, you get the classic “package not found / consistency token mismatch” failure.

SQL INCLUDE and DCLGEN

To pull SQL or host declarations from a partitioned data set member during SQL processing, code:

1
2
3
4
5
6
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.
       EXEC SQL
            INCLUDE EMPDCL
       END-EXEC.

• Do not nest SQL INCLUDE statements
• In C, do not use #include for SQL or for host variables the coprocessor/precompiler must see — use EXEC SQL INCLUDE
• Precompiler SYSLIB can hold different members than the compiler COPY library for the same name; the coprocessor has one SYSLIB, so a name cannot mean two different members

DCLGEN (declarations generator) is a DSN subcommand that reads the catalog and writes DECLARE TABLE / DECLARE VIEW plus a host structure. The precompiler does limited semantic checking against those declarations in the program — not against live catalog tables. IBM’s tip: run DCLGEN before precompile so column names and types in the listing match the table you think you are using. For coprocessor bit-data columns, DCLGEN DCLBIT(YES) can emit DECLARE VARIABLE FOR BIT DATA automatically.

SQL statement recognition and source transformation

Recognition is margin- and delimiter-sensitive. COBOL SQL is normally columns 8–72 with EXEC SQL in Area B. C/C++ SQL is typically columns 1–72 unless you pass other MARGINS. C is case-sensitive; SQL keywords are usually coded in uppercase unless you use the FOLD option to fold SBCS ordinary identifiers to uppercase.

Transformation replaces the SQL text with a call sequence that passes addresses of host variables (DSNHADDR / DSNHADD2 style parameter lists in precompiler-generated COBOL). The coprocessor avoids generating a giant first-call initialization of every SQL area in the program — one reason coprocessor modules are smaller and first-SQL is cheaper in programs with hundreds of statements.

Host variables for the precompiler must be explicitly declared in WORKING-STORAGE or LINKAGE (COBOL). You cannot rely on implicit typing. Indicator variables are separate halfword items. Arrays use OCCURS on indicator structures or host-variable arrays, not on ordinary scalar host variables.

Precompiler errors and warnings

The processor issues DSNH-prefixed messages. Severity follows the usual IBM pattern: informational, warning, error, severe. SOURCE and XREF listings are how you find the line. Common beginner failures:

• Host variable not declared, or declared after first use under ONEPASS
• Column referenced in SQL but not present in DECLARE TABLE (DSNH206I-style messages) — coprocessor defaults can be stricter than a shop’s old DSNHPC PARM of STDSQL(NO) / SQL(DB2)
• Wrong string delimiter (APOST vs QUOTE / APOSTSQL vs QUOTESQL)
• EXEC SQL continuing in column 8 under the coprocessor
• Missing period on INCLUDE with the COBOL coprocessor
• Using #include or COPY for SQL the processor never sees

Warnings do not always stop DBRM creation. Treat them as bind-time surprises waiting to happen: a column the precompiler could not check will still fail BIND if the catalog disagrees. Copy the same SQL processing options onto the coprocessor SQL() string when you migrate, or you will “gain” errors that DSNHPC had been relaxing.

Language-specific prepare

COBOL precompile

Most z/OS shops still have a three-step COBOL + Db2 job: DSNHPC, IGYCRCTL, IEWL (or binder). Procedures DSNHCOB, DSNHICOB, and DSNHCOB2 wrap that. Match APOST/QUOTE to the COBOL compiler option. Called subprograms that receive host-variable addresses that can change between CALLs must MOVE ZERO TO SQL-INIT-FLAG before the next SQL when using the precompiler.

C and C++ precompile

Procedure DSNHC precompiles C. C++ may need DSNHCPP2 (sample in SDSNSAMP member DSNTIJMV) when the program meets IBM’s special-JCL conditions. Decimal floating-point host types need the C/C++ DFP and ARCH(7) options plus the coprocessor SQL option. Always concatenate prefix.SDSNLOAD so the coprocessor can call Db2 modules.

PL/I and Assembler

DSNHPLI / Enterprise PL/I PP(SQL("…")) is the PL/I path. Assembler uses DSNHASM and the language interface DSNHLI. SQL in Assembler is still EXEC SQL-style statements the precompiler extracts into a DBRM; run time still goes through the attachment (CAF, TSO, CICS, IMS, RRSAF).

Precompiler JCL notes

If you run multiple precompile jobs against a PDS DBRMLIB that is not a PDSE, IBM tells you to change the sample procedures (DSNHCOB, DSNHCOB2, DSNHICOB, DSNHFOR, DSNHC, DSNHPLI, DSNHASM, DSNHSQL) from DISP=SHR to DISP=OLD on the DBRM library to avoid overlay collisions. PDSEs tolerate shared write more safely.

Coprocessor compile JCL must include SDSNLOAD, a DBRMLIB DD for the DBRM, and SYSLIB concatenation that contains every INCLUDE member. From z/OS UNIX you can write the DBRM to a PDS or an HFS file and bind with the command-line processor.

1
2
3
4
5
6
7
8
9
10
//COBOL   EXEC PGM=IGYCRCTL,
//   PARM='SQL("APOSTSQL STDSQL(NO) VERSION(V1)"),NOSQLCCSID,LIB'
//STEPLIB  DD DISP=SHR,DSN=IGY.SIGYCOMP
//         DD DISP=SHR,DSN=DSN.V12.SDSNLOAD
//SYSIN    DD DISP=SHR,DSN=PAYROLL.COBOL.SOURCE(PAYCALC)
//SYSLIB   DD DISP=SHR,DSN=PAYROLL.DCLGEN.COPYLIB
//DBRMLIB  DD DISP=SHR,DSN=PAYROLL.DBRMLIB.DATA
//SYSLIN   DD DSN=&&LOADSET,DISP=(NEW,PASS),UNIT=SYSDA,
//            SPACE=(CYL,(1,1))
//SYSPRINT DD SYSOUT=*

Explain It Like I'm Five

Your COBOL story has special sentences that start with EXEC SQL. The ordinary COBOL teacher (the compiler) does not speak SQL. The precompiler is a helper who reads the story first, copies every SQL sentence onto an index card (the DBRM), and rewrites those sentences as “please call Db2.” The coprocessor is a teacher who already speaks both languages, so you skip the helper and still get the index card. Later, BIND turns the index card into a recipe Db2 can cook. If the card and the book do not share the same secret stamp (consistency token), Db2 refuses to cook.

Exercises

1. Write DSNHPC PARM options for Enterprise COBOL using apostrophes in both COBOL and SQL, SOURCE, XREF, and STDSQL(NO).
2. List three COBOL coprocessor differences that can break a program that “always worked” with DSNHPC.
3. Explain when you would use NOSQLCCSID versus DECLARE VARIABLE FOR BIT DATA.
4. Why must EXEC SQL INCLUDE members be on SYSLIB for the precompiler even if the same name exists as a COBOL COPY book?
5. Trace PAYCALC from DSNHPC (or SQL compile) through BIND PACKAGE to a plan PKLIST, and name the data set that holds the DBRM.