SQLCODEs and what to do about them
After every SQL statement, DB2 sets SQLCODE. Zero is success, +100 is 'no row found', anything negative is an error. Knowing eight of them covers most of what you will see.
| SQLCODE | Meaning | Usual response |
|---|---|---|
| 0 | Success | |
| +100 | No row found, or end of cursor | Normal — handle it explicitly |
| -180 / -181 | Invalid date or timestamp value | Bad input data; validate before the call |
| -305 | Null returned with no indicator variable | Add a null indicator to the host variable |
| -501 / -502 | Cursor not open, or opened twice | Fix the cursor logic |
| -803 | Duplicate key on insert or update | Row already exists; decide insert-or-update |
| -805 | Package not found | Bind the package in this environment |
| -811 | More than one row returned by a singleton SELECT | Use a cursor, or tighten the WHERE clause |
| -818 | Timestamp mismatch between load module and package | Recompile and rebind together |
| -904 | Resource unavailable | Object is stopped or in a restricted state — check the reason code |
| -911 / -913 | Deadlock or timeout | -911 rolled back; -913 did not. Retry logic belongs here |
| -922 | Authorisation failure | Security: the plan, package, or table |
The three that will actually cost you a night
- -805 — a deployment problem, not a code problem. Check which collection the plan searches in that environment.
- -818 — the load module and the package no longer agree. Compile and bind must be done as a pair from the same source.
- -911 — a deadlock, and DB2 rolled your unit of work back. Well-written batch retries; poorly-written batch abends and someone gets a call.
9000-SQL-ERROR.
DISPLAY 'SQL ERROR IN ' WS-PARAGRAPH
DISPLAY ' SQLCODE : ' SQLCODE
DISPLAY ' SQLSTATE : ' SQLSTATE
DISPLAY ' SQLERRMC : ' SQLERRMC
CALL 'DSNTIAR' USING SQLCA, WS-ERR-MSG, WS-ERR-LEN
PERFORM VARYING WS-I FROM 1 BY 1 UNTIL WS-I > 8
DISPLAY WS-ERR-LINE(WS-I)
END-PERFORM
EXEC SQL ROLLBACK END-EXEC
MOVE 16 TO RETURN-CODE
GOBACK.SQLSTATE alongside SQLCODE
SQLSTATE is a five-character standardised code that means the same thing across database products, where SQLCODE is DB2-specific. Log both: SQLCODE for looking things up in DB2 documentation, SQLSTATE for anything portable.
Common mistakes
Not finding a row is often the expected case. Handle it as a branch in your logic, not an abend.
SQLERRMC and DSNTIAR text name the object involved. Without them, an error is a number and nothing more.
-913 leaves your unit of work intact, so you must decide explicitly whether to roll back before retrying.
What you will see at work
- Every shop has a standard SQL error paragraph in a copybook. Use it rather than writing your own.
- -911s cluster around specific times and specific jobs. If you see them repeatedly, it is a design conversation, not a retry-count conversation.
- Include the program name and paragraph in error output. 'SQLCODE -803' with no location is nearly useless in a large application.
Key terms
Check your understanding.
Take this lesson's quiz and save your progress. Free.