Embedded SQL overview for DB2 COBOL programs

What embedded SQL is

Embedded SQL means SQL statements are part of the application source. Db2 does not compile COBOL, and the COBOL compiler does not understand SELECT. A precompiler (DSNHPC) or the Db2 coprocessor inside the COBOL compiler finds the SQL, checks it enough to generate a DBRM, and replaces it with a call to the language interface. You then compile, link-edit, and BIND as described on the compile-and-bind JCL page.

Statements fall into two practical groups:

• Declaration statements — DECLARE CURSOR, INCLUDE SQLCA, INCLUDE SQLDA, BEGIN/END DECLARE SECTION, DECLARE TABLE. They do not run at SQL execution time; they describe objects to the precompiler.
• Executable statements — SELECT INTO, INSERT, UPDATE, DELETE, OPEN, FETCH, CLOSE, COMMIT, ROLLBACK, CALL, SET, MERGE, and the dynamic statements PREPARE, EXECUTE, EXECUTE IMMEDIATE, DESCRIBE.

After every executable statement you inspect SQLCODE (and preferably SQLSTATE). Zero means success, +100 is not found, +nnn other positives are warnings, and negative codes are errors. Never assume a FETCH worked because the program did not abend.

EXEC SQL and END-EXEC

In COBOL, every SQL statement sits between EXEC SQL and END-EXEC. Put EXEC in Area A (column 8) or in Area B according to shop standard; both are common. End the COBOL statement with a period after END-EXEC when the SQL is a complete imperative statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.
 
       EXEC SQL
            SELECT LASTNAME
              INTO :HV-LASTNAME
              FROM HR.EMPLOYEE
             WHERE EMPNO = :HV-EMPNO
       END-EXEC.
 
           IF SQLCODE = 0
              DISPLAY 'OK ' HV-LASTNAME
           ELSE
              DISPLAY 'SQLCODE ' SQLCODE
           END-IF.

Rules that bite beginners:

• Host variables in SQL are prefixed with a colon (:HV-EMPNO). In plain COBOL you use the name without a colon.
• Do not put a COBOL period inside the SQL text. The SQL terminator is END-EXEC, not a period after SELECT.
• Comments inside SQL use SQL comment forms (-- to end of line in many contexts) or precompiler rules—not a COBOL * in column 7 in the middle of a token.
• INCLUDE is an SQL INCLUDE of a DCLGEN or SQLCA member, not COPY, unless your shop generates COPY-compatible members and you know the precompiler will still see DECLARE TABLE.
• String delimiters inside SQL must match the APOST or QUOTE precompiler option.

Other languages use the same EXEC SQL idea with different terminators. C uses a semicolon. That is why HOST(IBMCOB) versus HOST(C) matters on DSNHPC.

Static SQL

Static SQL is statement text the precompiler can see in full. You may still use host variables for values, but you cannot change the table name or the shape of the WHERE clause at run time without going dynamic. Db2 binds an access path into the package. That is the workhorse of COBOL on z/OS: predictable, cache-friendly, and easy to EXPLAIN.

1
2
3
4
5
6
       MOVE '000010' TO HV-EMPNO.
       EXEC SQL
            UPDATE HR.EMPLOYEE
               SET SALARY = SALARY * 1.03
             WHERE EMPNO = :HV-EMPNO
       END-EXEC.

Static SQL is still “dynamic” in the everyday sense that host variable values change. What is static is the statement string and therefore the bound plan shape (table, columns, join pattern, predicate structure). If you need a different table per run, that is not static SQL.

Dynamic SQL

Dynamic SQL builds or supplies statement text at run time. The simple form is EXECUTE IMMEDIATE for a string with no parameter markers. The general form is PREPARE into a statement name, then EXECUTE (optionally USING host variables or an SQLDA) or DECLARE CURSOR FOR that statement name and FETCH. Parameter markers are question marks, not host variables, in the prepared string.

1
2
3
4
5
6
7
8
9
       STRING 'UPDATE HR.EMPLOYEE SET SALARY = SALARY * 1.03'
              ' WHERE WORKDEPT = ?' DELIMITED BY SIZE
              INTO HV-SQL-TEXT.
       EXEC SQL
            PREPARE S1 FROM :HV-SQL-TEXT
       END-EXEC.
       EXEC SQL
            EXECUTE S1 USING :HV-DEPT
       END-EXEC.

Dynamic SQL is required for ad-hoc tools (DSNTEP2 is itself a dynamic SQL program) and for user-driven query builders. Costs include PREPARE CPU, more injection risk if you concatenate unchecked text, and access paths that may differ from last week. Prefer parameter markers over gluing literals into the string. Static SQL remains the default for fixed business transactions.

SQL PL versus COBOL SQL

SQL PL (SQL procedural language) is the language of native SQL procedures and SQL functions: BEGIN...END, IF, WHILE, DECLARE handlers, SIGNAL. It runs inside Db2. You create it with CREATE PROCEDURE and you do not precompile COBOL to ship the procedure body.

COBOL embedded SQL is the opposite direction: application code in the allied address space calls Db2. COBOL has no native WHILE that is an SQL statement; you WRITE COBOL PERFORM loops and FETCH. You can mix the two: COBOL CALL a native procedure that contains SQL PL. Do not confuse SQL PL condition handlers with COBOL IF SQLCODE logic—they live in different programs.

External stored procedures can be COBOL programs with embedded SQL, prepared like any batch/CICS program and then defined with CREATE PROCEDURE ... LANGUAGE COBOL. That is still COBOL SQL, not SQL PL, even though Db2 invokes it like a routine.

Batch COBOL + Db2

A typical batch job is IKJEFT01, DSN SYSTEM(ssid), RUN PROGRAM(name) PLAN(plan). The load module is linked with DSNELI. The program issues EXEC SQL, checks SQLCODE, and issues COMMIT at designed points (end of a unit of work, every n rows for long updates). If the job steps abend, uncommitted work rolls back.

Alternatives: CAF (DSNALI) if the program must CONNECT and DISCONNECT itself, and RRSAF (DSNRLI) when the unit of recovery is RRS-coordinated with other resource managers. Stored procedures and many modern batch frameworks use RRSAF. The SQL you write does not change much; the JCL and link-edit stub do.

Batch programs often DECLARE a cursor, OPEN, FETCH in a PERFORM UNTIL SQLCODE = +100, then CLOSE. Single-row SELECT INTO is fine when you know there is at most one row (primary key lookup). Multiple rows without a cursor yield SQLCODE -811.

CICS + COBOL + Db2

CICS programs are translated for CICS commands and prepared for Db2. Link-edit includes DSNCLI. Thread reuse, protected threads, and DB2CONN/DB2ENTRY resources are CICS systems-programmer topics, but application programmers must know:

• The unit of work is usually the CICS unit of work. EXEC CICS SYNCPOINT commits Db2 work together with CICS resources. SYNCPOINT ROLLBACK backs both out.
• Do not sprinkle SQL COMMIT as if you were in TSO batch unless your site explicitly designed that (it is rarely the CICS pattern).
• Pseudo-conversational designs must not hold cursors or locks across a CICS RETURN that ends the task. OPEN, FETCH what you need, CLOSE, SYNCPOINT, then RETURN.
• The plan or package collection is associated with the CICS DB2ENTRY / thread, not with a DSN RUN command.

Abend handling should roll back. A CICS ASRA that skips syncpoint rollback can leave surprises; use HANDLE ABEND or your framework's equivalent and be explicit.

IMS + COBOL + Db2

IMS MPP, BMP, and IFP programs use the IMS attachment (DFSLI000). Db2 and IMS share the IMS syncpoint. A successful IMS checkpoint or application GU that triggers commit commits Db2 as well. SQL COMMIT in an IMS environment is not the way you coordinate DL/I and Db2 together.

Design the same way you design IMS+Db2 locking: keep the unit of work short, do not FETCH a huge cursor across a checkpoint without understanding cursor with hold and IMS rules, and bind with isolation that matches IMS throughput needs. BMP jobs look “batch-like” but they are still IMS syncpoint, not TSO DSN COMMIT, unless you are in a TSO BMP exception your site documented.

INCLUDE, SQLCA, and WHENEVER

Almost every COBOL Db2 program starts with EXEC SQL INCLUDE SQLCA. The SQLCA is the communications area: SQLCODE, SQLSTATE, SQLERRMC, SQLERRD, and SQLWARN flags. Under STDSQL(YES) you may declare SQLCODE and SQLSTATE as standalone hosts instead; most z/OS COBOL shops still INCLUDE SQLCA.

WHENEVER SQLERROR GOTO and WHENEVER NOT FOUND GOTO are precompiler-generated IF SQLCODE tests after each executable statement. They are easy to overuse: a GOTO out of a cursor loop on +100 is fine, but a global SQLERROR GOTO that skips CLOSE and ROLLBACK logic becomes a leak. Many modern shops prefer explicit IF SQLCODE after each statement for readability. Either style is embedded SQL; pick one per program and stay consistent.

INCLUDE of a DCLGEN member brings DECLARE TABLE plus host structures. The precompiler uses DECLARE TABLE to type-check your static SQL against the table as it was when you generated the DCLGEN—not necessarily against today's catalog until you bind. VALIDATE(BIND) then checks the real catalog. Keep DCLGEN regeneration on the same change ticket as ALTER TABLE.

A minimal COBOL skeleton

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
       IDENTIFICATION DIVISION.
       PROGRAM-ID. EMPINQ.
       DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL INCLUDE SQLCA END-EXEC.
       EXEC SQL INCLUDE EMPLOYEE END-EXEC.
       01  HV-EMPNO             PIC X(6).
       PROCEDURE DIVISION.
           MOVE '000010' TO HV-EMPNO.
           EXEC SQL
                SELECT LASTNAME, SALARY
                  INTO :DCL-EMPLOYEE.LASTNAME,
                       :DCL-EMPLOYEE.SALARY
                  FROM HR.EMPLOYEE
                 WHERE EMPNO = :HV-EMPNO
           END-EXEC.
           IF SQLCODE = 0
              DISPLAY LASTNAME OF DCL-EMPLOYEE
           ELSE
              DISPLAY 'NOT FOUND OR ERROR ' SQLCODE
           END-IF.
           GOBACK.

Replace the DISPLAY path with your shop's SQL error paragraph (often DSNTIAR to format SQLCA). Add indicator variables for nullable columns. The next pages cover host variables and indicators in depth.

Explain It Like I'm Five

Embedded SQL is writing a library request on a sticky note that lives inside your COBOL story. EXEC SQL and END-EXEC are the sticky-note borders so the special Db2 helper can find them. Static SQL means the sticky note's wording is printed in the book; only the fill-in-the-blank numbers change. Dynamic SQL means you write a new sticky note while the program is already running. Batch, CICS, and IMS are three different school buildings: the sticky notes look similar, but you hand them to a different office (attachment) and you wait for the bell (commit) in different ways. SQL PL is a recipe that stays inside the library kitchen; COBOL SQL is you standing at the counter placing orders.

Exercises

1. Mark which statements in a typical inquiry program are declarations versus executable SQL.
2. Rewrite a concatenated WHERE clause as static SQL with a host variable, and explain what you would lose if the column name itself had to vary.
3. For a CICS pseudo-conversational update, list when you OPEN, FETCH, CLOSE, and SYNCPOINT.
4. Explain one reason a BMP should not issue SQL COMMIT to “finish” a DL/I update.
5. Write EXEC SQL CALL for a native SQL procedure with two host-variable arguments.