SQL -904 Top Reasons to Get This Error

This is mainly occurred due to work space is not available in the DB2. In the real scenario, If we need to deal with multiple rows, there is enough work space is needed , else we get SQL Code-904. Resolution: (Source IBM Red book)

A new online updatable DSNZPARM, MAXTEMPS, has been added to DSN6SYSP. This DSNZPARM specifies the maximum amount of workfile storage that an agent can use and is specified in units of MBs or GBs. If the DSNZPARM value is specified as 0, then no limit is enforced, which was the default in previous releases.

When the total storage used by an agent exceeds the MAXTEMPS value that you specified, a Resource Unavailable message (DSNT501I) and a Resource Unavailable SQLCODE (-904) are issued.

Tthe activity that caused this condition terminates. The -904 message has a new reason code and resource name as shown in the below example.

New resource unavailable information

SQLCODE = -904
UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE
REASON = 00C90305
TYPE OF RESOURCE = '100'x (for database)
RESOURCE NAME = 'WORKFILE DATABASE'
SQLSTATE = 57011

The MAXTEMPS DSNZPARM can protect your workfile from becoming exhausted by runaway queries and declared global temporary tables, however make sure you have enough 32 KB WORKFILE space allocated to avoid -904 resource unavailable failures for large declared global temporary tables (DGTT) since they cannot span workfiles.

Pertinent APARs on space reuse and performance for DGTT for INSERT/DELETE are PK62009, PK67301, and PK70060.

The current recommendation is to allocate workfiles with secondary extents for use by DGTT.

In-memory workfile support is provided when the final sort output data that would have been stored in a work file is less than the 4 KB or 32 KB pagesize of the selected work file. This means that small sorts are not written out to the work file, but the results are presented directly from the WORKFILE 4 KB or 32 KB buffer.

This usage of in-memory workfile support provides a performance enhancement. In one test measurement, we achieved a 10 to 30% CPU reduction for small sorts. This enhancement is not available for declared global temporary tables (both user-defined and used by DB2) as they are always written to the WORKFILE.

In-memory workfile support is expected to be of most benefit for online transactions with relatively short-running SQL calls in which the number of rows that are sorted can be small.

Also read