Page 1 of 1
how to resolve -904 SQL code?
Posted: Tue Aug 29, 2023 8:46 pm
by Raja Babu
Hi all
Anyone has an idea how to resolve -904 SQL code?
Re: how to resolve -904 SQL code?
Posted: Wed Aug 30, 2023 12:57 am
by Robert Sample
From the manual:
-904 UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON reason-code, TYPE OF RESOURCE resource-type, AND RESOURCE NAME resource-name
So to resolve this, look up the reason-code to figure out what the precise issue is, and use the resource-type and resource-name to zoom in on what needs to be changed / added to resolve the issue.
Short answer: make the resource available.
Re: how to resolve -904 SQL code?
Posted: Sat Sep 02, 2023 12:04 pm
by Raja Babu
Thanks but which resource is in error, how do I know that?
Re: how to resolve -904 SQL code?
Posted: Sat Sep 02, 2023 12:17 pm
by Dicta Mitra
It's table(resource) unavailability. Maybe the table is on COPY PEND after LOAD or any other operation.
Re: how to resolve -904 SQL code?
Posted: Sun Sep 03, 2023 12:06 am
by zum13
The information you require will be in the SQL Communication Area (SQLCA).
If you are running a utility like SPUFI, it should dump that information for you in the output.
If this is from an application program, then you'll need to find the SQLCA in the storage dump. Assuming it's one of IBM's compilers you're using, having the "CEEDUMP" ddname allocated is an advantage here as the Language Environment will usually give you a nicely formatted listing of the storage in your program rather than the hexdump that the system abend dump provides. If your program is not actually abending, then it should be. The general good practice around SQLCODEs is that any code you aren't handling should give you an abend so you have the required diagnostic information to fix the fault.
There is a module supplied with DB2 called "DSNTIAR" that can help when coding error handling into programs. It takes an SQLCA and formats an error message for you. Details are here:
https://www.ibm.com/docs/en/db2-for-zos ... c=dsntiar-
and here's an example (in C) which shows it in use (see function "Sql_err"):
https://www.ibm.com/docs/en/db2-for-zos ... s-dsn8becl
Re: how to resolve -904 SQL code?
Posted: Wed Sep 06, 2023 6:21 pm
by Raja Babu
Thanks zum13. My program is not using DSNTIAR, how do we know that it then?
Re: how to resolve -904 SQL code?
Posted: Wed Sep 06, 2023 8:54 pm
by zum13
Somewhere in your program you should have an "EXEC SQL INCLUDE SQLCA" statement to include the field definition for the SQL Communications Area. For COBOL, it looks like this:
Code: Select all
01 SQLCA.
05 SQLCAID PIC X(8).
05 SQLCABC PIC S9(9) COMP-5.
05 SQLCODE PIC S9(9) COMP-5.
05 SQLERRM.
49 SQLERRML PIC S9(4) COMP-5.
49 SQLERRMC PIC X(70).
05 SQLERRP PIC X(8).
05 SQLERRD OCCURS 6 TIMES
PIC S9(9) COMP-5.
05 SQLWARN.
10 SQLWARN0 PIC X.
10 SQLWARN1 PIC X.
10 SQLWARN2 PIC X.
10 SQLWARN3 PIC X.
10 SQLWARN4 PIC X.
10 SQLWARN5 PIC X.
10 SQLWARN6 PIC X.
10 SQLWARN7 PIC X.
05 SQLEXT.
10 SQLWARN8 PIC X.
10 SQLWARN9 PIC X.
10 SQLWARNA PIC X.
10 SQLSTATE PIC X(5).
The SQLCA is used to return error information to the program which includes the SQLCODE and an error message (SQLERRM) as well as a load of other information (see
https://www.ibm.com/docs/en/db2-for-zos ... ion-fields for a description of the fields). It is here that you will find the reason code and the resource for which the problem is occurring, so either locate the SQLCA within your abend dump (the "SQLCAID" is an eyecatcher field, so search for "SQLCA") or print its contents before ending the program (which is what "DSNTIAR" is designed to do).
When using SPUFI, you can see a dump of the SQLCA whenever an error is generated, for example:
Code: Select all
SELECT *
FROM SYSIBM.SYSDUMMY2;
---------+---------+---------+---------+---------+---------+---------+--
DSNT408I SQLCODE = -204, ERROR: SYSIBM.SYSDUMMY2 IS AN UNDEFINED NAME
DSNT418I SQLSTATE = 42704 SQLSTATE RETURN CODE
DSNT415I SQLERRP = DSNXOTL SQL PROCEDURE DETECTING ERROR
DSNT416I SQLERRD = -500 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
DSNT416I SQLERRD = X'FFFFFE0C' X'00000000' X'00000000' X'FFFFFFFF'
X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION
---------+---------+---------+---------+---------+---------+---------+--
DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0
It's also possible to use the "GET DIAGNOSTICS" statement to retrieve error information (see
https://www.ibm.com/docs/en/db2-for-zos ... iagnostics), although this requires a little more work.
Re: how to resolve -904 SQL code?
Posted: Sun Dec 31, 2023 2:46 am
by Hiprainya
Do you have the "EXEC SQL INCLUDE SQLCA" statement in your program, and have you checked the SQLCA to identify the reason code and resource causing the -904 SQL code?
Re: how to resolve -904 SQL code?
Posted: Wed Jan 03, 2024 5:27 pm
by Raja Babu
Thank you, working with SQLCA has helped me. Thanks for the direction zum13 and Hiprainya .