Diagnose DB2 SQLCODE -904

Diagnose SQLCODE -904

Unlike a syntax error, -904 usually means the SQL itself might be fine while the environment is not. SQLSTATE 57011 places the problem in the resource-unavailable family. The productive habit is to stop asking "what is wrong with my SELECT?" for a moment and ask "which named resource is unavailable and why?"

IBM's message pattern is consistent: reason code, resource type, and resource name. Those three fields are the diagnosis. A stopped payroll table space, a missing buffer pool BP8K0, and an accelerator message can all present as -904 with completely different next steps. If you skip the reason lookup, you will apply the wrong fix with great confidence.

Be careful with secondary codes. When FETCH returns -904, DB2 closes the cursor. The next FETCH may return -501. Operators who "fix the -501" without restoring the underlying resource create a loop of confusion. Always treat the first unavailable-resource failure as the root event.

Prerequisites

• Full -904 tokens: reason code, resource type, resource name, plus SQL text or package section if available
• Authority to issue DISPLAY DATABASE, DISPLAY BUFFERPOOL, and DISPLAY UTILITY as your site allows
• Access to SYSLOG / OPERLOG around the failure time
• IBM reason-code documentation or an approved internal lookup for your DB2 version
• Change authority or a DBA partner if the fix requires START, RECOVER, DEFINE BP, or utility work

Steps

Step 1: Capture and decode the reason code

Copy the reason code exactly, including leading zeros and the 00C / 00D / 00E style prefix. Look it up for your DB2 release. The documentation tells you whether you are dealing with an object state, allocation failure, drain timeout, security resource, or special component such as an accelerator. Also record resource-type and resource-name because many reason codes are generic until those fields name the object.

1
2
3
4
5
6
7
8
Illustrative -904 capture:
  SQLCODE        = -904
  SQLSTATE       = 57011
  reason-code    = (lookup YOUR value)
  resource-type  = ...
  resource-name  = DBNAME.SPACENAME   or other name form
 
Next: do not START or RECOVER yet — decode reason-code first.

Step 2: Display the named database and spaces

If the resource name looks like a database, table space, index space, or partition, display it. Check for STOP, STOPP, RECP, LPL, GRECP, RO, UT, and other restricted or advisory states. Remember that a space can look RW while its database is restricted, so display both levels when needed.

1
2
3
4
5
-DISPLAY DATABASE(APPDB)
-DISPLAY DATABASE(APPDB) SPACENAM(*) RESTRICT
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS)
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) CLAIMERS
-DISPLAY DATABASE(APPDB) SPACENAM(ORDERTS) LOCKS

CLAIMERS and LOCKS help when the reason points at drain or lock-related unavailability. Utility displays help when UT status or utility-only access is involved. Do not skip related index spaces: an unavailable index can block SQL that seems to touch only the table.

Step 3: Correlate SYSLOG and component messages

Search the failure window for DSNT500I, DSNT501I, allocation failures, HSM recall messages, buffer pool messages, and utility messages. The SQLCODE tells you the statement failed; SYSLOG often tells you the operator-visible event one second earlier. For accelerator-related reasons, preserve the accelerator message text from the resource name field and engage the component owners with that exact text.

Step 4: Inspect non-object resources when the name is not a space

Not every -904 names a table space. Buffer pool problems need DISPLAY BUFFERPOOL and a comparison against the object's defined pool. Work-file or sort shortages need workload and storage analysis. Security-resource variants need RACF or equivalent return codes from the message tokens. Your reason-code article is the map; displays are the fieldwork.

1
2
3
4
5
6
7
-DISPLAY BUFFERPOOL(BP0)
-DISPLAY BUFFERPOOL(*)
-DISPLAY UTILITY(*)
 
Also compare:
  object buffer-pool attributes in the catalog
  site standards for 4K / 8K / 16K / 32K pools

Step 5: Apply the corrective action that matches the reason

Only after the reason is understood should you START an object, RECOVER a pending state, recall a data set, define or enlarge a buffer pool, finish or terminate a utility, or wait for claimers to drain. Starting a space that is in RECP without recovery is not a fix. Likewise, retrying the application in a tight loop against a stopped space just produces more -904 noise.

Coordinate with the team that stopped or restricted the object. Production stops are often intentional during releases, recoveries, or REORG windows. Your job is to restore availability safely, not to win a race against an in-flight change.

Step 6: Retest the original SQL path

Rerun the same statement, package section, or transaction. Confirm SQLCODE success and confirm the object remains in the expected RW or planned state afterward. If FETCH had failed earlier, reopen the cursor deliberately after the resource is healthy.

Verify results

1. Reason code is documented in the ticket with a one-sentence plain-language meaning.
2. DISPLAY output from before and after the fix shows the unavailable condition cleared.
3. The original application path no longer returns -904.
4. Any required follow-on utilities (RECOVER, START, RUNSTATS, COPY) are complete or intentionally scheduled.
5. If the root cause was procedural (an unexpected STOP), the runbook is updated so the next change window communicates the outage.

Common errors

Another frequent mistake is assuming the resource name in the message is the only object involved. Dependent indexes, partitions, and the database status can all matter. Display widely enough to see the full availability picture, then narrow your change to the minimum safe action.

Explain it like I'm 5

SQLCODE -904 is like trying to play with a toy box that is locked, empty, or carried away. Your game instructions might be perfect, but you still cannot play until the toy box is available again. The reason code is the note on the box that says why it is closed—maybe someone locked it for cleaning, maybe the shelf is missing, maybe the toys were moved. Read the note, fix that problem, then try the game again.

Exercises

1. Write a first-response checklist for -904 that starts with tokens and ends with a verified rerun.
2. Explain why FETCH -904 followed by -501 should not be treated as two unrelated bugs.
3. Draft DISPLAY commands for database HRDB when resource-name is HRDB.EMPTS.
4. List three non-locking causes of -904 and the evidence you would expect for each.
5. Create a decision tree: STOPPED vs RECP vs buffer-pool vs unknown reason code.