CICS VSAM Transparency for z/OS, Version 1.2


Identifying SQL errors

CICS® VT translates SQL codes into appropriate VSAM return codes and reason codes, which are returned to your application program.

For example, the SQL code -803 indicates that an inserted or updated value violates a DB2 uniqueness constraint. In other words, an attempt has been made to insert a duplicate key. CICS VT translates this condition into a matching condition in VSAM and returns the appropriate information to the application program.

It is almost inevitable that CICS VT will encounter certain conditions in DB2 that do not translate exactly into an equivalent VSAM return code and reason code. For example, if a DB2 tablespace is only available for utility processing, DB2 returns a -904 SQL code. The way that CICS VT reports these conditions varies between batch and CICS programs.

For batch programs, CICS VT writes a formatted SQL communication area (SQLCA) to the system console log and the batch job log. Here is an example:

Figure 1. CICS VT SQL error reporting in batch
 
 VIDCAF    SUB SYSTEM = DB2B   PLAN NAME = VIDKSDS  
 VIDCAF                 PRECOMPILER SEQUENCE NUMBER =  756
 VIDCAF    DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY 
 VIDCAF                                     AN UNAVAILABLE RESOURCE. REASON CODE 
 VIDCAF                                     00C90086, TYPE OF RESOURCE 00000200, 
 VIDCAF                                     AND RESOURCE NAME VIDTSTDB.VIDKSDS
 VIDCAF    DSNT418I SQLSTATE = 57011 SQLSTATE RETURN CODE
 VIDCAF    DSNT415I SQLERRP  = DSNXRRC SQL PROCEDURE DETECTING ERROR
 VIDCAF    DSNT416I SQLERRD  = 111  13172746  0  13223106  -974970876  12714050 
 VIDCAF                        SQL DIAGNOSTIC INFORMATION     
 VIDCAF    DSNT416I SQLERRD  = X'0000006F'  X'00C9000A'  X'00000000'  X'00C9C4C2' 
 VIDCAF                        X'C5E32004'  X'00C20042'    
 VIDCAF    SQL DIAGNOSTIC INFORMATION    

In CICS, the error information is written to the DD statement VIDDMP. This is an optional CICS VT DD statement that you should add to every CICS system or region that runs CICS VT. See Modifying the CICS startup JCL for more details. The content of a VIDDMP DD statement is shown in Figure 2:

Figure 2. CICS VT SQL error reporting in CICS
 
 ********************************************************************************
 *                                                                              *
 *              C I C S  V T  F O R M A T T E D   D U M P                       *
 *                                                                              *
 *        DATE: 14.11.2009                            TIME: 15.56.3322          *
 *                                                                              *
 *        TRANSACTION: VTMV     TERMINAL: 0010                                  *
 *                                                                              *
 *        CALLTYPE:    GET      DIMNAME : VIDKSDS                               *
 *                                                                              *
 ********************************************************************************

 DSNT408I SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN           
          UNAVAILABLE RESOURCE. REASON 00C90081, TYPE OF RESOURCE 00000200, AND 
          RESOURCE NAME VIDTSTDB.VIDTSA                                         
 DSNT418I SQLSTATE   = 57011 SQLSTATE RETURN CODE                               
 DSNT415I SQLERRP    = DSNXRRC SQL PROCEDURE DETECTING ERROR                    
 DSNT416I SQLERRD    = 111  13172746  0  13223106  -974970876  12714050 SQL     
          DIAGNOSTIC INFORMATION                                                
 DSNT416I SQLERRD    = X'0000006F'  X'00C9000A'  X'00000000'  X'00C9C4C2'       
          X'C5E32004'  X'00C20042' SQL DIAGNOSTIC INFORMATION  
                 

For certain DB2 errors, diagnostic data is written to the DB2® master address space (ssidMSTR) by DB2.





Last updated: February 8, 2013 20:43:3