DB2 SQLCODEs -904, -911, -913: resources and locking

How to read this trio

Class 57 SQLSTATE values are about resources (unavailable, limit exceeded, operator intervention). Class 40 is transaction rollback. Always capture reason-code, resource-type, and resource-name from the message / SQLCA tokens—those three fields are the real diagnosis, not the SQLCODE alone.

-904 unavailable resource

IBM: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

The SQL statement needed something that was not available. Reason codes are documented in the Db2 reason-code volume. Resource-type and resource-name identify what was missing. Special cases IBM calls out:

• Resource-type 402: resource-name carries z/OS Security Server return/reason codes
• Reason 00E7000E with resource-type 00001080: issued by IBM Db2 Analytics Accelerator; resource-name is accelerator message text

System action: statement cannot be processed. If it was a cursor FETCH, Db2 closes the cursor—so a later FETCH may show -501 even though the root problem was -904.

Programmer response: collect SYSLOG across the failure window; search related messages; identify the resource; look up the reason-code. If the name is an optimum buffer pool, define that buffer pool or place the table in a table space that uses an existing pool. For accelerator -904, contact accelerator support with the message text. SQLSTATE 57011.

1
2
3
4
5
6
7
8
Example token pattern (illustrative):
  SQLCODE = -904
  reason-code = 00D70024   (example — look up YOUR code)
  resource-type = ...
  resource-name = DBNAME.TSNAME or data set name
 
Next: DISPLAY DATABASE(...) SPACENAM(...)
      check STOP/RESTRICT status, HSM migrate, BP allocation

Beginner causes of -904

• Database or table space STOPPED or in restricted state
• Page set not open / allocation failure
• Buffer pool not defined
• Object dependent on newer-release function after fallback
• Temporary work file / sort space shortage (reason-specific)

-911 unit of work rolled back (deadlock or timeout)

IBM: THE CURRENT UNIT OF WORK HAS BEEN ROLLED BACK DUE TO DEADLOCK OR TIMEOUT. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

Your UOW was the victim. Attention: changes associated with the unit of work must be entered again. SQLERRD(3) also contains the reason-code. Most common:

• 00C90088 — deadlock
• 00C9008E — timeout

System action: statement cannot be processed; application rolled back to the previous COMMIT. Programmer response: SYSLOG for related messages; long-running apps should COMMIT frequently when possible; see DSNT376I for avoidance tips. On receipt of -911, the application should in general terminate. SQLSTATE 40001.

1
2
3
4
5
6
           EVALUATE SQLCODE
           WHEN -911
                DISPLAY 'DEADLOCK/TIMEOUT - UOW ROLLED BACK'
                DISPLAY 'SEE SQLERRD(3) FOR 00C90088 VS 00C9008E'
      *         Re-drive business unit from last commit; often end task
           END-EVALUATE

-913 unsuccessful execution (deadlock or timeout)

IBM: UNSUCCESSFUL EXECUTION CAUSED BY DEADLOCK OR TIMEOUT. REASON CODE reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name.

You were a victim of deadlock or timeout. Same common reason codes: 00C90088 / 00C9008E. SQLERRD(3) holds the reason-code.

System action: statement cannot be processed. If it was FETCH, Db2 closes the cursor. Programmer response: SYSLOG; application should either COMMIT or ROLLBACK to the previous COMMIT, then generally terminate; see DSNT376I. SQLSTATE 57033.

-911 vs -913 (practical)

Both are lock-contention outcomes. -911 documents that the current unit of work was rolled back (SQLSTATE 40001). -913 documents unsuccessful execution from deadlock/timeout (SQLSTATE 57033) and tells the app to COMMIT or ROLLBACK, then usually terminate. Do not assume you can continue updating as if nothing happened—inspect SQLCA, release/hold strategy, and whether your cursor survived.

Concurrency hygiene

Lock order

Deadlocks (00C90088) often come from two transactions locking the same objects in opposite order. Standardize access paths (touch tables in the same sequence).

Commit frequency

Holding locks across huge batch windows invites timeouts (00C9008E). COMMIT at safe business boundaries. Watch WITH HOLD cursors and what survives COMMIT.

Isolation and lock size

Higher isolation and larger lock sizes increase conflict. Tune with care—correctness first. See lock-size and isolation tutorials for the levers.

After -904 on FETCH

Cursor is closed. Re-OPEN only after the resource is available again; otherwise you chase -501. Log the original -904 tokens before any CLOSE/OPEN cleanup so operators still see the real unavailable resource.

Timeout vs deadlock triage

Use SQLERRD(3) / message reason-code: 00C90088 points you at deadlock graphs and lock order; 00C9008E points you at IRLM wait times, long units of work, and holders that never commit. DSNT376I and related DSNT5xx messages often name the other agents. Do not “fix” timeouts by raising lock wait forever without fixing the holder—you only delay the outage.

Application checklist

• On -911: assume UOW changes are gone; rebuild from last good COMMIT
• On -913: explicitly COMMIT or ROLLBACK, then decide whether to end the task
• On -904: fix or wait for the resource; do not busy-loop SQL against a STOPPED TS
• Always preserve reason-code and resource-name in your application log

Explain It Like I'm Five

-904 is “the toy box is locked in the closet—you cannot play until someone opens the closet.” -911 is “two kids grabbed toys in a circle, a grown-up reset the game, and your turns are undone.” -913 is “you lost the waiting game for a toy; stop and put your pieces back (commit or roll back) before you try again.”

Exercises

1. From a -904 dump, list reason-code, resource-type, and resource-name. Look up the reason in IBM Docs.
2. Explain why a FETCH -904 can be followed by -501.
3. Compare SQLSTATE 40001 (-911) and 57033 (-913) in one paragraph.
4. What do 00C90088 and 00C9008E mean?
5. Sketch a retry policy that never retries without confirming rollback completed.