DB2 Other SQLCODE Topics: How to Read Every SQLCODE Page

What every SQLCODE page should give you

Whether you read IBM Docs or a MainframeMaster group page, a useful SQLCODE write-up always covers the same checklist. If a blog posts only “-803 means duplicate,” it is incomplete—you still need SQLSTATE, tokens, and system action.

Sign and magnitude: reading the integer

• SQLCODE = 0 — successful execution (check SQLWARN0 for warnings)
• SQLCODE = +100 — no data found (not a crash)
• SQLCODE > 0 and not 100 — successful with warning
• SQLCODE < 0 — unsuccessful execution

People write +000 next to +100 in tables; the host variable is still integer 0. Never invent a plus version of a famous minus code (+911 is not a thing on z/OS for deadlock rollback—that is -911).

Tokens: the part beginners skip

IBM message text includes placeholders such as constraint-name, indexspace-name, rid-number, reason-code, auth-id, and object-type. At runtime those placeholders become concrete values in SQLERRM / DSNTIAR output. Two -803s with different index spaces are different problems.

1
2
3
4
5
6
7
8
9
10
11
       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
           PERFORM VARYING I FROM 1 BY 1 UNTIL I > 10
               IF ERROR-TEXT (I) NOT = SPACES
                   DISPLAY ERROR-TEXT (I)
               END-IF
           END-PERFORM.

1
2
3
4
GET DIAGNOSTICS CONDITION 1
  :msg = MESSAGE_TEXT,
  :stat = RETURNED_SQLSTATE,
  :code = DB2_RETURNED_SQLCODE;

SQLCA fields worth knowing beyond SQLCODE

• SQLSTATE — five characters; class 23 often integrity; 57 often resource; 42 often syntax/access
• SQLERRD(3) — row count on many DML successes; reason codes on some failures (for example locking)
• SQLWARN0–A — warning flags even when SQLCODE is 0
• SQLERRM — message tokens (prefer DSNTIAR for formatting)

A practical diagnosis loop

Use the same loop for every unfamiliar code:

• Capture SQLCODE + SQLSTATE + formatted message
• Open IBM Codes for your Db2 version and read system action first
• Match tokens to catalog objects (SYSINDEXES, SYSRELS, SYSCHECKS, SYSPACKAGE)
• Decide: data fix, SQL rewrite, grant, bind, environment, or retry
• Add a regression test so the same bad key or empty SELECT INTO cannot silently return

1
2
3
4
5
6
7
8
9
Ticket template
---------------
SQLCODE: -530
SQLSTATE: 23503
Message: ... FOREIGN KEY FK_EMP_DEPT ...
Program / Package / Collection: ...
Statement: INSERT INTO EMP ...
Parent table checked: DEPT (no row for DEPTNO='Z99')
Next action: insert parent or correct FK value

Grouping strategy (why this site splits pages)

One giant page for every SQLCODE is unreadable. Group by failure mode:

• Success / no data — 0 and +100
• Warnings — +802, +445, cursor holes, …
• Syntax / semantics — -007, -084, -117, -119, …
• Data / datetime — -180, -181, -305, …
• Cursors — -501 to -504
• Packages / plans — -805, -818
• Resources / locking — -904, -911, -913
• Connection / auth — -922…, -551…
• RI / constraints — -530…, -803, -811
• DDL / accelerator leftovers — -601…, -4742

When you meet a code outside those groups, still apply this page’s checklist—do not guess from LUW forums.

Related message and abend world

SQLCODEs are not the only numbers on a dump. Console DSN* messages, utility DSNU messages, and completion codes X'04E' /X'04F' often surround a bad night. If SQLCODE is 0 but the job abended, you are no longer in SQLCODE land—move to the messages and abends references.

Explain It Like I'm Five

Every SQLCODE page is like a recipe card for a kitchen mistake. The title is the mistake name (-803). The message is the loud buzzer text. Explanation is “you burned the toast.” System action is “we threw the toast away.” Programmer response is “use a lower heat.” SQLSTATE is the shared sticker color so every kitchen in the chain recognizes “burned food.” Tokens are which toaster and which slice. Read the whole card—not just the name.

Exercises

1. Pick any negative SQLCODE from IBM Docs. Copy message text, SQLSTATE, and programmer response into your own words without looking.
2. Call DSNTIAR (or GET DIAGNOSTICS) after a deliberate -803 and highlight the tokens.
3. Build a logging standard for your team: which SQLCA fields are mandatory in the application log.
4. Find one SQLCODE that exists on LUW with a different meaning than z/OS (or is absent). Write a warning sticky note for your squad.
5. Map five recent production SQLCODEs into the grouping list above. Which group is your shop’s biggest?