ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

DB2 SQL return code availability

book

Article ID: 225053

calendar_today

Updated On:

Products

SYSVIEW Performance Management

Issue/Introduction

Is there a way to find out the SQL return code after the transaction has completed in sysview history log?

Environment

Release : 15.0

Component : SYSVIEW

Resolution

The SQL return codes can be made available in the Transaction log record provided that:
 
the IMSLOGR Parmlib member specifies DETAIL for TRAN-EVENT-TRACE-LEVEL.
 
And
 
That the IMSLOGR Parmlib member also have MONITOR-ESS-CALLS set to YES.
 
Here is a sample of what is displayed in the tran log record:
 
Sample ESS section of a record displayed from IMSTLOG:
 
Section: ESS Trace                                                            
ESSName  PSBName  Call             RCODE Count  AvgTime  MaxTime TotTime
DT31     DSN8IC0  SIGNON               0     1 0.004930 0.004930 0.004930
DT31     DSN8IC0  CREATE THREAD        0     1 0.004928 0.004928 0.004928
DT31     DSN8IC0  NORMAL CALL          0    25 0.001676 0.023051 0.041922
DT31     DSN8IC0  COMMIT PHASE 1       0     1 0.000913 0.000913 0.000913
DT31     DSN8IC0  TERM THREAD          0     1 0.002494 0.002494 0.002494
 
 

Additional Information

For a DB2 subsystem, a 'NORMAL CALL' will be SQL. We build a 'key' that is the ESSName + PSBName + Call + RCODE and combine those counts, so in this example, there were 25 SQL calls that all completed with RCODE = 0.
If there was an SQL (NORMAL CALL) that got a non-zero RCODE, it would have been listed on a separate row (since the key would have been different).