How do I resolve the "STATEMENT ID" in the CA Datacom SQL messages and dumps?
Article ID: 30361
Updated On:
Products
Issue/Introduction
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
Environment
Component:
Feedback
