SQLCODE -904 — resource unavailable

Db2 could not use an object or resource, often a table space in a restricted state. The reason code and resource name tell you which and why.

What happened

Jobs or transactions that touch one table start failing immediately, while others run fine. The message names a reason code, resource type and resource name.

Program output via DSNTIAR (illustrative)

DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN
         UNAVAILABLE RESOURCE. REASON 00C900nn, TYPE OF RESOURCE
         00000200, AND RESOURCE NAME DBPAY01.TSPAY01
DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE

What it means

Db2 needed a resource and could not get it, so the statement failed. The SQLSTATE is 57011. Usually the resource is a database object — a table space, index space or partition — that is stopped, held by a utility, or in a pending state.

Read the three SQLERRMC tokens. The reason code (format 00Cxxxxx) says why; look it up in the Db2 codes documentation for your version. The resource type says what kind of object. The resource name usually has the form database.space, which you use in the next command.

Typical causes

A table space or index stopped by an operator or DBA.
A utility (REORG, LOAD, COPY, RECOVER) running or left in a stopped state.
A pending state after a utility or failure, such as recovery pending, check pending or copy pending, depending on the object and settings.
Pages in the logical page list after an I/O or failure problem.
Other resource limits defined at the site, shown by a different reason code.

Symptoms

Every access to that object fails at once, regardless of data. Look-alike -911 is intermittent and clears; -904 persists until someone changes the object's state.

Where to look

The program's DSNTIAR or GET DIAGNOSTICS output with all three tokens.
The Db2 MSTR log in SDSF for messages about the object or a utility.
Output of -DISPLAY DATABASE for the named space.
Output of -DISPLAY UTILITY(*) for running or stopped utilities.
The batch schedule for maintenance jobs on that database.

How to diagnose

Record reason code, resource type and resource name.
Look up the reason code meaning for your Db2 version.
Issue -DISPLAY DATABASE(dbname) SPACENAM(spacename) RESTRICT (or ask the DBA) to see the object's status, such as STOP, UT, RECP, CHKP, COPY or LPL.
Issue -DISPLAY UTILITY(*) to see whether a utility holds the object.
Check the schedule and change records for maintenance at that time.

How to fix

Checking object status (illustrative)

-DISPLAY DATABASE(DBPAY01) SPACENAM(TSPAY01) RESTRICT
NAME     TYPE PART  STATUS
TSPAY01  TS         RW,UTRO

The fix depends on the state and is normally a DBA action: let a running utility finish, terminate and clean up a failed one, run the recovery or check the state requires, or restart the object with -START DATABASE. Once the object is available, rerun or restart the failed work. To turn the raw SQLCA into readable text, call DSNTIAR (DSNTIAC in CICS) with the SQLCA and a message buffer, or use GET DIAGNOSTICS to fetch MESSAGE_TEXT, RETURNED_SQLSTATE and DB2_RETURNED_SQLCODE. Either way, write the full message to the job log before the program ends.