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

SYSVIEW R15.0

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