COBOL and DB2 advanced techniques

SQL precompiler versus coprocessor

The Db2 precompiler (DSNHPC) is a separate step. It reads the COBOL source, copies every SQL statement and host-variable description into a database request module (DBRM), and writes a modified source file where EXEC SQL has become calls the COBOL compiler can compile. The Enterprise COBOL SQL coprocessor (SQL compiler option) does the same extraction during the COBOL compile, which is why many shops no longer have a visible precompile step — the DBRM still appears.

The precompiler does not look at the live catalog for every column. It checks names against DECLARE TABLE statements in the program (the reason you run DCLGEN). Authorization and access paths wait for BIND.

DBRM

A DBRM is not executable. It is the SQL side of the program: statement text, sections, and a consistency token (often discussed as a precompiler timestamp) that is also planted in the modified source. At run time Db2 demands that the load module and the bound package carry the same token. If you bind an old DBRM with a new load module you get SQLCODE -818. If the package is missing you get -805.

Precompile, COBOL compile, link-edit

Link-edit must resolve DSNHLI, the language interface. Which load module actually provides DSNHLI depends on the attachment:

• DSNELI — TSO
• DSNCLI — CICS
• DFSLI000 — IMS
• DSNALI — CAF
• DSNRLI — RRSAF

Linking the TSO stub into an IMS BMP is a classic -922 / wrong-plan failure. INCLUDE the correct stub; do not rely on SYSLIB search order if two stubs both define DSNHLI.

Bind, package, and plan

BIND PACKAGE reads one DBRM, validates SQL and privileges against the catalog, chooses access paths, and stores the package in a collection. IBM’s model is one DBRM per package. You then BIND PLAN with PKLIST (package list), often collection.* so new package versions are found without rebinding the plan every time.

1
2
3
4
BIND PACKAGE (PAYROLL) MEMBER(PAY01) -
  ACTION(REPLACE) ISOLATION(CS) QUALIFIER(HR)
BIND PLAN (PAYPLAN) PKLIST(PAYROLL.*) -
  ACTION(REPLACE) RETAIN

From DSN you RUN a plan, not a package. The thread uses the plan name; SQL sections execute from the matching package. Changing one subprogram means precompile / compile / link that module and BIND PACKAGE for its DBRM — not a full plan rebind of every program in the application. That is the point of packages versus the old “all DBRMs in the plan” MEMBER style (still around as a compatibility path).

Bind options that COBOL shops argue about: ISOLATION, RELEASE, QUALIFIER, OWNER, CURRENTDATA, DEGREE, DBPROTOCOL, EXPLAIN, and for dynamic SQL DYNAMICRULES and KEEPDYNAMIC. Wrong QUALIFIER is why unqualified table names hit the owner’s schema in test and the wrong schema in prod.

DCLGEN and COPY / INCLUDE of DCLGEN output

DCLGEN (declarations generator, also a DB2I panel and a DSN command) reads a table or view from the catalog and writes:

• An SQL DECLARE TABLE for the precompiler
• COBOL host structures (and often indicator arrays) for the columns

Bring that member in with EXEC SQL INCLUDE member END-EXEC so both the SQL processor and COBOL see it. Some shops COPY a separately maintained COBOL layout; that layout can drift from the table. INCLUDE of DCLGEN output is the supported way to keep PIC clauses matched to CHAR, DECIMAL, DATE, TIMESTAMP, and VARCHAR (a 49-level length plus data).

1
2
3
4
5
6
7
8
9
       EXEC SQL
            INCLUDE DCLGEN-EMP
       END-EXEC.
       EXEC SQL
            SELECT EMPNO, LASTNAME
              INTO :EMPNO, :LASTNAME
              FROM DSN8C10.EMP
             WHERE EMPNO = :EMPNO-IN
       END-EXEC.

SQLDA

The SQL Descriptor Area describes a list of variables at run time: data type, length, address, and null indicator for each column or parameter. Static FETCH INTO :A, :B does not need it. You need an SQLDA when:

• You DESCRIBE a prepared SELECT whose column list is unknown
• You EXECUTE or OPEN with a variable number of parameter markers
• You FETCH USING DESCRIPTOR into storage you allocated after DESCRIBE

INCLUDE SQLDA generates the structure. You set SQLN (how many SQLVAR entries you allocated), DESCRIBE fills SQLD (how many you need). If SQLD > SQLN you must grow the SQLDA and DESCRIBE again. Each SQLVAR holds TYPE, LEN, DATA pointer, and INDC pointer. This is the COBOL equivalent of walking a JDBC ResultSetMetaData — more work, same idea.

Dynamic SQL in COBOL

Static SQL is written in the source and bound in the package. Access paths are chosen at BIND (with rebind/autobind later). Dynamic SQL is a character string you build or receive at run time.

EXECUTE IMMEDIATE

One shot for a non-SELECT string with no parameter markers: PREPARE and EXECUTE in one go. Fine for occasional DDL or a simple DELETE. Weak for a statement you run thousands of times.

1
2
3
4
5
6
       01  DYN-STMT           PIC X(80).
           MOVE 'DELETE FROM HR.TEMP_WK WHERE WK_DATE < CURRENT DATE'
             TO DYN-STMT
           EXEC SQL
                EXECUTE IMMEDIATE :DYN-STMT
           END-EXEC.

PREPARE and EXECUTE

PREPARE statement-name FROM :host-string creates a prepared statement.EXECUTE statement-name runs a non-SELECT. For SELECT, DECLARE cursor-name CURSOR FOR statement-name then OPEN / FETCH / CLOSE. PREPARE once, EXECUTE many times with different values.

1
2
3
4
5
6
7
8
9
10
11
12
       01  STMT-BUF           PIC X(200).
       01  HV-DEPT            PIC X(3).
 
           MOVE 'UPDATE DSN8C10.EMP SET SALARY = SALARY * 1.02 WHERE WORKDEPT = ?'
             TO STMT-BUF
           EXEC SQL
                PREPARE S1 FROM :STMT-BUF
           END-EXEC
           MOVE 'A00' TO HV-DEPT
           EXEC SQL
                EXECUTE S1 USING :HV-DEPT
           END-EXEC.

Dynamic parameter markers

The ? in the string is a parameter marker. You cannot write :HV-DEPT inside the string and expect the precompiler to see it — that host variable is not in the static SQL. Markers are always treated as nullable from the descriptor’s point of view. EXECUTE USING :hv1, :hv2 ... matches markers from left to right. When the number of markers varies, build an SQLDA and EXECUTE ... USING DESCRIPTOR :SQLDA.

Do not concatenate user input into the SQL text. Markers keep types clean, allow statement cache reuse, and avoid SQL injection. DYNAMICRULES on the package controls which authorization ID applies to dynamic SQL. KEEPDYNAMIC(YES) can keep prepared statements across COMMIT — useful and easy to misuse if you do not understand thread reuse.

After PREPARE, SQLERRD(4) holds a timeron cost estimate for the prepared statement. SQLERRD(5) can point at a syntax error position if PREPARE fails.

Putting it together

A change to a static WHERE clause is a source change: precompile, compile, link, BIND PACKAGE. A change to a table that invalidates the package may autobind or fail until REBIND. A truly variable statement (user-chosen columns, optional predicates you cannot list) is dynamic SQL plus SQLDA. Most production COBOL stays static for the hot path and uses dynamic SQL only where the statement shape must move.

Explain It Like I'm Five

Writing EXEC SQL in COBOL is like writing a recipe on a sticky note. The precompiler photocopies the recipe onto a card (the DBRM) and replaces the sticky note with “call the kitchen.” BIND is the chef deciding the fastest way to cook that recipe and filing it in a folder (package) inside a cookbook (plan). At dinner the waiter opens the cookbook, not the original sticky note. If you reprint the sticky note but forget to give the chef the new card, the stamps on the card and the book do not match (-818). Dynamic SQL is making up a recipe at the table and asking the chef to cook it right then (PREPARE) using blank spaces (?) you fill with tonight’s ingredients (USING).

Exercises

1. List every output data set of a traditional precompile / compile / link / bind job and who consumes it next.
2. Explain how a consistency token ties the load module to the package and which SQLCODE appears when they differ.
3. Run DCLGEN mentally for a table with VARCHAR and DECIMAL: what COBOL 49-level and COMP-3 pictures would you expect?
4. Convert a static UPDATE with a host variable into PREPARE with a parameter marker and EXECUTE USING.
5. When would you choose EXECUTE IMMEDIATE versus PREPARE once and EXECUTE in a loop?