DB2 SQL Error SQLCODE=-904 (How to Fix)
The DB2 SQL error SQLCODE = -904 indicates the unavailability of a DB2 resource, in most cases, a tablespace. However, other inaccessible resources like packages, internal workspaces, or buffer pools might also be the cause.
You can go into the depths of this failure by finding out the reason from the IBM information center. Generally, you’ll notice a resource type and reason code while viewing the error description in detail, which helps you identify the problem.
What Is DB2 SQL Error SQLCODE=-904?
SQLCODE -904 gets shown when the SQL statement fails to execute due to a missing resource. The general format of the error message looks like:
-904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name
Here, the reason-code value represents what caused the failure. There are different reason-codes for different reasons, the details of which you can find in the official IBM documentation. Likewise, the resource-type is a value that indicates the type of resource that caused the error.
Consider the following example:
SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90081, TYPE OF RESOURCE 00000200, AND RESOURCE NAME DB000011.TS000033
Even if you cannot access the formatted SQLCA, you can decode this message to find the root of the cause. In this particular example, 00C90081 is the reason-code, which means the system stopped a DB2 resource.
To specify the resource type, the message has shown the value 00000200. If you look up the IBM documentation of codes for resource types, you’ll see that 00000200 is the numerical representation of a tablespace.
Lastly, DB000011.TS000033 indicates the name of the particular resource, which in this case is a tablespace name that got stopped.
You may get SQLCODE -904 when running a PowerCenter session with a Power Exchange for DB2 Z/OS connection. However, the reason-code and resource type, in that case, will be 00C90096 and 00000304, respectively.
How to Resolve 904 SQLCODE in DB2?
Here’s a generalized approach to resolving the SQLCODE -904 error in DB2.
1. Try to gather the SYSLOG data for the specific time period when the failure occurred. From SYSLOG data, search for messages like UNAVAILABLE RESOURCE and note the log time.
2. Check the resource ID for the unavailable resource and refer to the reason code to identify the particular cause. If you discover that the resource is a buffer pool, try to define it with that name. Or you can use the tablespace related to that specific buffer pool and create the table in it.
However, if you get the error while running a PowerCenter session with Informatica, the solutions might be:
1. Reducing the number of concurrent executions against affected tables.
2. Altering the table isolation settings to UR or similar.
3. Reading a DB2 unload sequential file rather than reading straight from DB2.
4. Rebinding PowerExchange with BINDCTLB to use dtlamdb2 and avoid using MultiRow.
5. Modifying the isolation level within the SQL override. You can achieve this by applying a SQL override to the source and appending “WITH UR;” after your override statement. Note that this technique applies solely to the Source override but not the target update statement.
Conclusion
If the IBM DB2 Analytics Accelerator for z/OS is causing the SQLCODE -904 error, you may need to contact the support team of IBM DB2 Analytics Accelerator for z/OS and give them the details.
