search cancel

DB2 SQL return code availability


Article ID: 225053


Updated On:


SYSVIEW Performance Management


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


Release : 15.0

Component : SYSVIEW


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