DB2 SQL return code availability

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