DB2 SQLCODE overview

How to read SQLCODE and SQLSTATE

Db2 sets both after the statement, whether you INCLUDE SQLCA or declare stand-alone SQLCODE and SQLSTATE with STDSQL(YES). COBOL PIC S9(9) COMP for SQLCODE is a signed binary fullword—do not store it in an unsigned display field that drops the minus.

Read a dump in this order: SQLCODE (what happened), SQLSTATE (which standard class), SQLERRMC tokens (which object), SQLERRD(3) or GET DIAGNOSTICS DB2_REASON_CODE (why, for lock and resource failures), then the IBM message text.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
       EVALUATE SQLCODE
           WHEN 0
                IF SQLWARN0 = 'W'
                    PERFORM LOG-WARNING
                END-IF
           WHEN +100
                PERFORM NOT-FOUND-PATH
           WHEN OTHER
                IF SQLCODE > 0
                    PERFORM LOG-WARNING
                ELSE
                    PERFORM SQL-ERROR-PATH
                END-IF
       END-EVALUATE.

Positive versus negative codes

A positive SQLCODE means Db2 finished the statement in a way that still counts as successful execution, except that +100 means “success, but no row.” Host variables from a FETCH that got +100 are undefined for that fetch—do not use them. Host variables from a FETCH that got +802 may contain good columns and null indicators of -2 on the broken expression.

A negative SQLCODE means unsuccessful execution. Inserts did not land. A singleton SELECT did not fill hosts (unless you have a documented partial case). For -911 the whole unit of recovery was rolled back. For -913 the statement failed but the UR was not rolled back—you decide COMMIT or ROLLBACK. That difference is why the sign and the exact code both matter.

Shop talk often says “we got an 805” or “911.” Those are minus codes on z/OS. Writing +805 in a comment is wrong; +805 is not the DBRM-not-found error. The next pages call out real plus codes versus the famous minus codes that checklists sometimes label with a plus by habit.

Using GET DIAGNOSTICS with SQLCODE

After a statement, SQLCA.SQLCODE and GET DIAGNOSTICS CONDITION 1 DB2_RETURNED_SQLCODE tell the same story for the primary condition. Use GET DIAGNOSTICS when:

• NUMBER is greater than 1 (several diagnostics stored)
• You need MESSAGE_TEXT or DB2_ORDINAL_TOKEN_n
• Multi-row INSERT/FETCH: DB2_ROW_NUMBER plus ROW_COUNT
• A native SQL handler already ran SQL: GET STACKED DIAGNOSTICS for the original SQLCODE

1
2
3
4
GET DIAGNOSTICS CONDITION 1
  V_CODE = DB2_RETURNED_SQLCODE,
  V_STATE = RETURNED_SQLSTATE,
  V_TEXT = MESSAGE_TEXT;

Do not GET DIAGNOSTICS as a substitute for checking SQLCODE on the original statement—check first, then diagnose. GET DIAGNOSTICS itself can return a warning if a VARCHAR host truncates; that warning is in DB2_GET_DIAGNOSTICS_DIAGNOSTICS, not a wipe of the previous area.

Where to look up IBM documentation

• SQL codes — IBM Docs, Db2 for z/OS, Codes. Topics “Successful (+) SQL codes” and “Error (−) SQL codes.” Each code has Explanation, System action, Programmer response, SQLSTATE.
• Reason codes — same Codes PDF, later part. Hex values such as 00C90088. Also printed in DSNT376I / DSNT500I.
• Messages — Messages book: DSNT408I formats an SQLCODE for SYSOUT; DSNU is utilities; DSNJ is log/BSDS.
• SQL Reference — GET DIAGNOSTICS, WHENEVER, SQLCA layout.
• Application Programming and SQL Guide — checking SQLCODE in COBOL, DSNTIAR.

Match the version (Db2 12 versus 13). Do not use LUW SQL0204N text for z/OS -204 without checking; numbers overlap, wording and system action can differ. Bookmark: https://www.ibm.com/docs/en/db2-for-zos/12.0.0?topic=codes-sql

What each IBM SQLCODE topic contains

When you open a code, read more than the title. Explanation is what happened. System action tells you whether the cursor closed, whether a rollback already ran, whether hosts were left unchanged. Programmer response is the first fix to try. SQLSTATE lets you write class-based handlers. Related information links reason codes and messages. Later tutorial pages follow that same shape for each code.

Explain It Like I'm Five

SQLCODE is the librarian’s score after you ask for a book. Zero means “here it is.” Plus one hundred means “that shelf is empty.” Other plus numbers mean “here it is, but the cover is a bit torn—still usable.” Minus numbers mean “I did not give you a book; stop pretending I did.” SQLSTATE is a color sticker so every IBM library uses the same colors. GET DIAGNOSTICS is asking the librarian to read the whole incident report, not just the score. The IBM Codes book is the dictionary of scores.

Exercises

1. Write EVALUATE SQLCODE that handles 0, +100, other positives, and negatives separately.
2. Look up SQLCODE -803 and SQLSTATE 23505 in IBM Docs. Copy the programmer response in your own words.
3. Why must a FETCH loop test +100 before using host variables, even if SQLSTATE is also 02000?
4. List three IBM manuals you would open for -911, and what each one uniquely provides.
5. In a native SQL handler, write GET STACKED DIAGNOSTICS that saves the original SQLCODE before an INSERT to a log table.