How do I resolve the "STATEMENT ID" in the CA Datacom SQL messages and dumps?

Document ID : KB000030361
Last Modified Date : 14/02/2018
Show Technical Document Details

Introduction:

SQL statements in a plan are identified by a unique statement-id that is printed in error messages and in SQL dumps but sometimes, especially for very complex programs, it's difficult to understand which is the statement that caused the error.

For example, how can I identify the SQL statement that filled up the CA Datacom TTM, causing the following error?

 

-- DATA BASE SQL ERROR SUMMARY  10/4/2015 12:50:05  --                                                         

SQLSTATE 57S05 OCCURRED WITH SQL ERROR CODE -560        
               IN PROG temp_wri AT LINE 606 (TTM)       
ERROR MESSAGE TEXT:    TEMPORARY TABLE AREA (TTM) FULL  
                                                        
AUTH-ID.PLAN NAME:     SYSUSR.MYPLAN                     
                                                         
COMMAND(STATEMENT ID): QEXEC(STATEMENT 66)              
                                                        
JOB NAME:              MYTESTJB, RUN UNIT: 12345        
                                                         
-------------- END OF ERROR DIAGNOSTICS --------------  

 

Instructions:  

First, the statements are numbered sequentially in physical sequence. All statements related to a cursor have the same statement number, based on the DECLARE.

You must divide the statement number by 16 to get the statement number.

In the above case:                                                              

 COMMAND(STATEMENT ID): QEXEC(STATEMENT 66) 

 66 / 16 = 4 2 (remainder)                                                  

This means that the involved statement is the fourth one.  

The last 4 bits (i.e. the remainder) are used to indicate if the statement is dynamic or not, and the various cursor-related statements like OPEN, FETCH, CLOSE, UPDATE and DELETE.

Here are the meanings for these last four bits for cursor-related statements:

CURSOR_OPEN           1             
CURSOR_FETCH          2             
CURSOR_UPDATE         3             
CURSOR_DELETE         4             
CURSOR_CLOSE          5              
CURSOR_OPEN_SPAN_LUWS 6  

Bit x'08'  indicates Dynamic SQL used.                                                                   

Again, in the above case:                                                              

COMMAND(STATEMENT ID): QEXEC(STATEMENT 66)

66 / 16 = 4 2 (remainder)                                                  

as the remainder is 2, the statement that caused the error is the fourth one and it's a FETCH for the cursor defined in statement 4.  

As you now have the statement id, you have to find the source of the statement. The quickest way is exporting the plan on a flat file via DDTRSLM and looking for the statement id. 

Using our sample case:

//SYSIN    DD *                              
SET USER DATACOM-INSTALL NEWUSER;                    
EXPORT PLAN SYSUSR.MYPLAN EXCLUDE ALL TABLE;
/*  

You have to find the fourth statement (F 'STMTID = 00004') in the exported plan, which is a DECLARE CURSOR:

STMTID = 00004.00000                                                   
DECLARE CUSTLIST CURSOR FOR SELECT * FROM CUST WHERE STATE = :WS-STATE ;  

while the related FETCH statement can be found few lines below (note the same statement id):

STMTID = 00004.00000                                                     
FETCH CUSTLIST INTO :WC-IND-CD , :WC-CUSTNO , :WC-NAME , :WC-ADDR-1 ,    
:WC-ADDR-2 , :WC-CITY , :WC-STATE , :WC-ZIP , :WC-CRED-IND , :WC-AREA-CD ,
:WC-PH-EXCH , :WC-PH-NO , :WC-OPEN-DOL , :WC-YTD-SALES , :WC-ACT-YR ,    
:WC-ACT-MO , :WC-ACT-DAY , :WC-SLMN-ID ; 

 

Another example of the internal statement ids for a cursor defined as statement 1 would be: 

 OPEN   = 17            where 17 / 16 = 1 1                            
 FETCH  = 18                     / 16 = 1 2                            
 CLOSE  = 21                     / 16 = 1 5