SQLCA and SQLCODE basics in DB2 COBOL programs

What the SQLCA is

The SQLCA is a 136-byte structure. Db2 overlays it after each executable SQL statement (SELECT INTO, FETCH, INSERT, OPEN, COMMIT, and so on). Declarative statements such as DECLARE CURSOR do not execute at run time and do not set a new SQLCODE for “running the query.” OPEN does.

You almost never hand-code the layout. You ask the SQL processor to generate it:

1
2
3
4
       WORKING-STORAGE SECTION.
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.

INCLUDE SQLCA is an SQL statement, not a COBOL COPY of a random copybook. The precompiler or the Enterprise COBOL SQL coprocessor expands it into the official fields: SQLCAID, SQLCABC, SQLCODE, SQLERRM (SQLERRML + SQLERRMC), SQLERRP, SQLERRD (six integers), SQLWARN0–SQLWARN7, SQLWARN8–SQLWARNA, and SQLSTATE.

SQLCAID is the eye-catcher SQLCA. If the sixth byte is L, a line number related to a dynamic statement or a native SQL procedure may be present in SQLERRD. SQLCABC is the length, 136. SQLERRP starts with DSN on Db2 for z/OS and can name the detecting module when something fails.

SQL INCLUDE

EXEC SQL INCLUDE has three everyday uses in COBOL:

• INCLUDE SQLCA — generate the communications area
• INCLUDE SQLDA — generate the SQL descriptor area used with dynamic SQL and DESCRIBE
• INCLUDE member-name — pull a member (usually DCLGEN table/host declarations) from the include library the precompiler is given

INCLUDE member-name is processed before COBOL COPY in the SQL preparation path. Put DCLGEN members in WORKING-STORAGE with INCLUDE so host variable names match the table the precompiler thinks you are using. Mixing a stale COPYBOOK that drifted from the catalog is a classic -310 / truncation / wrong-column bug.

1
2
3
       EXEC SQL
            INCLUDE DCLGEN-EMP
       END-EXEC.

SQLCODE

SQLCODE is a signed fullword (PIC S9(9) COMP-5 in the generated COBOL SQLCA). Db2 sets it after every executable SQL statement, whether you provided an SQLCA or stand-alone SQLCODE/SQLSTATE host variables.

Codes you will memorize in the first week of COBOL + Db2 work:

• +100 — not found (FETCH past the last row, SELECT INTO with zero rows, searched UPDATE/DELETE that matched nothing)
• -803 — unique index / primary key duplicate (often SQLSTATE 23505)
• -811 — singleton SELECT returned more than one row; you needed a cursor
• -805 — package not found at the consistency token the load module expects
• -818 — timestamp / consistency-token mismatch between load module and package
• -911 — deadlock or timeout; the unit of work was rolled back
• -913 — deadlock or timeout; the unit of work was not rolled back (you decide)
• -925 / -926 — SQL COMMIT or ROLLBACK is not valid in this environment (CICS, IMS, or RRSAF — use the transaction manager or RRS instead)

Never treat “not 0” as a single bucket. +100 in a FETCH loop is the normal end. -803 on INSERT is a business duplicate. -911 means restart the unit of work. Dumping all three to the same ABEND paragraph hides the recovery path.

SQLSTATE

SQLSTATE is PIC X(5). It is set on every executable statement along with SQLCODE. The coding scheme is shared across IBM relational products, so classes of problems are stable even when the integer SQLCODE is Db2-specific.

• 00000 — unqualified success
• 01xxx — warning
• 02000 — no data (the SQLSTATE that pairs with +100)
• 08xxx — connection
• 22xxx — data exception
• 23xxx — integrity constraint
• 40xxx — transaction rollback
• 42xxx — syntax or access violation
• 50xxx and above — often product-specific Db2 conditions

You can test classes: IF SQLSTATE(1:2) = '02' is another way to spell not found. Portable middleware prefers SQLSTATE; traditional COBOL batch still branches on SQLCODE. Either is correct if you are consistent and you still look at warnings.

SQLERRMC and SQLERRML

When a SQLCODE message contains substitution variables (table name, constraint name, reason code), Db2 puts those tokens in SQLERRMC. Tokens are separated by X'FF'. SQLERRML is the length, 0 through 70. Zero means SQLERRMC is not pertinent.

Seventy bytes is a hard cap. Long schema-qualified names get truncated. IBM’s Codes book tells you to use GET DIAGNOSTICS with DB2_ORDINAL_TOKEN_n when you need the full token. Calling the IBM sample routine DSNTIAR formats the SQLCA into printable lines for SYSOUT; it still cannot invent bytes that were truncated in SQLERRMC.

1
2
3
4
5
6
       01  ERR-MSG.
           05  ERR-LEN         PIC S9(4) COMP VALUE +960.
           05  ERR-TEXT        PIC X(120) OCCURS 8 TIMES.
       01  ERR-TEXT-LEN        PIC S9(9) COMP VALUE +120.
 
       CALL 'DSNTIAR' USING SQLCA ERR-MSG ERR-TEXT-LEN.

SQLERRD

SQLERRD is six integers. COBOL indexes them 1 through 6. C programmers see sqlerrd[0] through sqlerrd[5]. Do not mix the indexing when you read a dump from another language.

The field beginners need first is SQLERRD(3): how many rows qualified for a data-change statement, or how many rows a rowset FETCH returned. After -911 or -913 it can hold a timeout/deadlock reason code. After PREPARE, look at SQLERRD(4) (timerons) only as a relative cost hint — it is not elapsed time.

SQLWARN

A zero SQLCODE is not “everything is perfect” until you glance at SQLWARN0. If it is W, walk the other flags. Truncation (SQLWARN1) is the one that silently chops last names and then looks like bad data downstream.

COBOL SQL error handling

Two styles exist. Prefer explicit tests after each statement for new code.

Explicit SQLCODE tests

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
       EXEC SQL
            SELECT LASTNAME
              INTO :HV-LASTNAME
              FROM DSN8C10.EMP
             WHERE EMPNO = :HV-EMPNO
       END-EXEC.
       EVALUATE SQLCODE
           WHEN 0
                IF SQLWARN0 = 'W'
                   PERFORM 8000-SQL-WARNING
                END-IF
                PERFORM 2000-USE-ROW
           WHEN +100
                PERFORM 2100-NOT-FOUND
           WHEN OTHER
                PERFORM 9000-SQL-ERROR
       END-EVALUATE.

WHENEVER

WHENEVER is a directive to the precompiler: from this point in the source, generated code after SQL statements will CONTINUE or GO TO a label when the condition hits.

• WHENEVER SQLERROR — SQLCODE < 0
• WHENEVER SQLWARNING — warning (including SQLWARN0)
• WHENEVER NOT FOUND — SQLCODE +100

1
2
3
4
5
6
7
8
9
       EXEC SQL
            WHENEVER SQLERROR GO TO 9000-SQL-ERROR
       END-EXEC.
       EXEC SQL
            WHENEVER NOT FOUND GO TO 2100-NOT-FOUND
       END-EXEC.
       EXEC SQL
            WHENEVER SQLWARNING CONTINUE
       END-EXEC.

WHENEVER GOTO fights structured COBOL: a FETCH loop that GO TOs out on +100 is hard to read, and a SQLERROR paragraph that issues more SQL can recurse if you forget WHENEVER SQLERROR CONTINUE at the top of the handler. If you use WHENEVER, reset it in error routines and never GO TO into the middle of a PERFORM.

With STDSQL(YES), declare SQLCODE and SQLSTATE as host variables and skip INCLUDE SQLCA. You still must check them. GET DIAGNOSTICS is the way to retrieve ROW_COUNT, MESSAGE_TEXT, and full tokens when the SQLCA is too small.

Explain It Like I'm Five

Imagine you ask a librarian (Db2) to fetch a book. When they come back, they hand you a sticky note (the SQLCA). The big number on the note (SQLCODE) says “got it,” “no such book,” or “the shelf fell over.” A five-letter code (SQLSTATE) is the same idea in a language other libraries also speak. Extra scribbles (SQLERRMC) name which book caused trouble. Little letter flags (SQLWARN) say things like “I found the book but I had to tear off the last page to fit your tiny backpack” — that is truncation, and you should notice it even when the big number is zero.

Exercises

1. Add INCLUDE SQLCA to a skeleton COBOL program and list every elementary field the precompiler generates.
2. Write an EVALUATE on SQLCODE that treats 0, +100, -803, and -911 differently.
3. After a FETCH INTO a PIC X(10) from a VARCHAR(40) column, explain what SQLCODE 0 plus SQLWARN1 = W means for the data in the host variable.
4. Explain when you would call DSNTIAR versus GET DIAGNOSTICS MESSAGE_TEXT.
5. Describe one maintenance problem caused by WHENEVER SQLERROR GO TO in a nested PERFORM FETCH loop.