how to resolve -904 SQL code?

[Raja Babu]

Hi all

Anyone has an idea how to resolve -904 SQL code?

[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.

[Raja Babu]

Thanks but which resource is in error, how do I know that?

[Dicta Mitra]

It's table(resource) unavailability. Maybe the table is on COPY PEND after LOAD or any other operation.

[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

[Raja Babu]

Thanks zum13. My program is not using DSNTIAR, how do we know that it then?

[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.

[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?

[Raja Babu]

Thank you, working with SQLCA has helped me. Thanks for the direction zum13 and Hiprainya .