GET DIAGNOSTICS and SQL diagnostics in DB2

The diagnostics area and the SQLCA

Think of two views of the same event. The diagnostics area is what Db2 keeps after a statement. The SQLCA is a 136-byte copy of the most important pieces, laid out for host languages. INCLUDE SQLCA in WORKING-STORAGE (or LOCAL-STORAGE). With precompiler option STDSQL(YES) you declare stand-alone SQLCODE and SQLSTATE instead and skip the SQLCA—then GET DIAGNOSTICS is how you still see ROW_COUNT and MESSAGE_TEXT.

1
2
3
       EXEC SQL
            INCLUDE SQLCA
       END-EXEC.

Check SQLCODE after every executable statement. Zero is success—then look at SQLWARN0. +100 is not found (not a crash). Negative means the statement did not complete successfully. Warning conditions (other positives, or SQLCODE 0 with SQLWARN0 = W) mean “it worked, but read the flags.” Error conditions are negatives and SQLSTATE classes other than 00, 01, and 02.

SQLCODE, SQLSTATE, tokens, and reason codes

SQLCODE is product-oriented and what most COBOL shops still test. SQLSTATE is five characters designed so classes of problems match across IBM SQL products: 00 success, 01 warning, 02 no data. Look codes up in the IBM Codes book (SQL codes chapter) at ibm.com/docs for Db2 for z/OS. Search with the sign: -911 not 911.

Message tokens fill the blanks in IBM text (“table PAY.EMP”). They land in SQLERRMC. If the 70-byte field truncates them, GET DIAGNOSTICS DB2_ORDINAL_TOKEN_n still has each token. A reason code (hex, for example 00C90088 deadlock versus 00C9008E timeout) often appears as a token and in SQLERRD(3) for -911/-913. GET DIAGNOSTICS DB2_REASON_CODE returns that number when the message has one.

SQL messages in SYSOUT often look like DSNT408I SQLCODE = -803. That is a DSN (usually DSNT) wrapper around the same SQLCODE. Utility messages are DSNU; this diagnostics page is about SQLCODE/SQLSTATE, not DSNU010I. Abend reason codes (04E) are a different Codes-book part—do not mix them with SQL reason tokens.

WHENEVER and exception handling in COBOL

WHENEVER is a precompiler directive, not a run-time Db2 feature. It tells the precompiler to insert a test after later SQL statements:

• SQLERROR — negative SQLCODE
• NOT FOUND — SQLCODE +100
• SQLWARNING — warning (positive other than +100, or SQLWARN0)

Action is CONTINUE or GOTO paragraph. WHENEVER stays in effect until another WHENEVER for that class. GOTO error paragraphs are easy to write and hard to maintain (every statement shares one handler). Many shops prefer explicit IF SQLCODE after each statement, or a PERFORM that inspects SQLCODE and calls DSNTIAR.

1
2
3
4
5
6
7
8
9
10
11
       EXEC SQL WHENEVER NOT FOUND CONTINUE END-EXEC.
       EXEC SQL WHENEVER SQLERROR GOTO SQL-ERR END-EXEC.
 
       EXEC SQL
            SELECT LASTNAME INTO :HV-LNAME
            FROM EMP
            WHERE EMPNO = :HV-EMPNO
       END-EXEC.
       IF SQLCODE = +100
           DISPLAY 'EMPLOYEE NOT FOUND'
       END-IF.

DSNTIAR and printable SQL messages

DSNTIAR is the IBM sample assembler routine that formats the SQLCA into 72-byte (or longer) print lines. COBOL calls it with the SQLCA, a message structure, and a halfword line length. Output resembles DSNT408I for errors and DSNT400I for successful SQL with extra text. Use it in batch SYSOUT and in CICS error screens. GET DIAGNOSTICS MESSAGE_TEXT is the SQL-language equivalent of that short text (without the message number; use DB2_MESSAGE_ID for the id).

1
2
3
4
5
6
7
8
       01  ERROR-MESSAGE.
           05  ERROR-LEN      PIC S9(4) COMP VALUE +720.
           05  ERROR-TEXT     PIC X(72) OCCURS 10 TIMES.
       01  ERROR-TEXT-LEN     PIC S9(9) COMP VALUE +72.
      *
           CALL 'DSNTIAR' USING SQLCA
                                ERROR-MESSAGE
                                ERROR-TEXT-LEN.

GET DIAGNOSTICS: statement, condition, combined

GET DIAGNOSTICS is embeddable SQL only (not dynamic PREPARE). No extra privilege. It reports on the last SQL statement that was not GET DIAGNOSTICS (and not a compound BEGIN). Three shapes:

• Statement information — NUMBER, ROW_COUNT, MORE, cursor attributes after OPEN, DB2_NUMBER_RESULT_SETS after CALL
• CONDITION n — SQLCODE, SQLSTATE, MESSAGE_TEXT, tokens, reason code, row number for that diagnostic
• ALL — combined text dump of everything gathered

1
2
3
4
5
6
7
8
9
10
11
       EXEC SQL
            GET DIAGNOSTICS :HV-ROWS = ROW_COUNT
       END-EXEC.
 
       EXEC SQL
            GET DIAGNOSTICS CONDITION 1
                 :HV-SQLCODE = DB2_RETURNED_SQLCODE,
                 :HV-SQLSTATE = RETURNED_SQLSTATE,
                 :HV-MSG = MESSAGE_TEXT,
                 :HV-REASON = DB2_REASON_CODE
       END-EXEC.

NUMBER is how many errors and warnings were stored. If the previous SQLSTATE was 00000, NUMBER is still 1 (a success condition). MORE = Y means some conditions were discarded because storage exceeded 65535 bytes—you did not see every warning. Loop CONDITION from 1 to NUMBER for multi-row INSERT with several failing rows. CONDITION 1 matches the SQLSTATE that was actually returned on the statement.

CURRENT versus STACKED (multiple diagnostics)

CURRENT (the default) is the diagnostics area for the previous SQL statement. STACKED is a second area that exists only inside a condition handler in a native SQL procedure, compiled SQL function, or trigger. When the handler runs its own INSERT to a log table, CURRENT becomes that INSERT. STACKED still holds the SELECT or UPDATE that fired the handler. If GET DIAGNOSTICS is the first statement in the handler, CURRENT and STACKED are the same.

1
2
3
4
5
6
7
8
9
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
  GET CURRENT DIAGNOSTICS CONDITION 1
    V_HANDLER_MSG = MESSAGE_TEXT;
  GET STACKED DIAGNOSTICS CONDITION 1
    V_ORIG_MSG = MESSAGE_TEXT,
    V_ORIG_CODE = DB2_RETURNED_SQLCODE;
  INSERT INTO ERRLOG (MSG, ORIG) VALUES (V_HANDLER_MSG, V_ORIG_MSG);
END;

Using STACKED outside a handler fails with SQLCODE -20228 (SQLSTATE 0Z002). That is why COBOL programs almost always use CURRENT (or omit the keyword) and SQL PL handlers use STACKED for the original exception.

Exception handling pattern

COBOL: INCLUDE SQLCA; after each statement test SQLCODE; on negatives CALL DSNTIAR and ROLLBACK (or CICS SYNCPOINT ROLLBACK); on +100 take the not-found path; on 0 inspect SQLWARN0. SQL PL: DECLARE handlers for NOT FOUND, SQLWARNING, SQLEXCEPTION; GET STACKED DIAGNOSTICS in the handler; do not assume SQLCODE still holds the original value after the handler’s first statement. Multi-row: GET DIAGNOSTICS ROW_COUNT and CONDITION loop with DB2_ROW_NUMBER.

Explain It Like I'm Five

After you ask the library a question, the librarian stamps a tiny card (SQLCA) with a number (SQLCODE) and a five-letter stamp (SQLSTATE). Sometimes the full story does not fit on the card, so you ask “tell me everything” (GET DIAGNOSTICS) and get a longer note, including which book in a stack of books had the problem (DB2_ROW_NUMBER). If a helper runs in to write in the accident log, their new stamp would cover the original card—unless you look at the carbon copy the helper is not allowed to overwrite (STACKED). DSNTIAR is the photocopier that turns the tiny card into a sentence you can pin on the wall. WHENEVER is a sticky note on your desk that says “if the number is bad, jump to the red paragraph.”

Exercises

1. Write COBOL that INCLUDE SQLCA, SELECT INTO, and branches for 0, +100, and negative SQLCODE, calling DSNTIAR on errors.
2. Write GET DIAGNOSTICS CONDITION 1 that captures SQLCODE, SQLSTATE, MESSAGE_TEXT, and DB2_REASON_CODE into host variables.
3. In a native SQL CONTINUE handler that INSERTs to ERRLOG, explain why STACKED is required to log the original failure.
4. A multi-row INSERT returns SQLCODE -803. How do NUMBER, CONDITION, and DB2_ROW_NUMBER tell you which input row was the duplicate?
5. Look up SQLSTATE 23505 and SQLCODE -803 in IBM Docs and list the tokens you would expect in SQLERRMC.