IDB2 exception does not write the SQL to the capture file on the first pass

Products

• SYSVIEW Performance Management Option for DB2 for z/OS

Issue/Introduction

The Exception IQL that captures the SQL to then write to the Capture dataset does not write the SQL on the first pass. 

Environment

Release : 20.0

Component : SYSVIEW Performance Management Option for DB2 for z/OS

Cause

By design, the SQL text is captured on the 2nd pass through the exception system that identifies a unique SQL execution.   

 

Resolution

The following recommendations to the exception definition will facilitate the SQL being written to the file via the 2nd pass:

1) The number of seconds between exception checks should be lower than the lowest threshold value. If not, exceptions will be missed (and this affects text collection).

2) The capture option can be left at "W", but it will require that the exception system "see" this SQL twice before capturing it. (Notice that when we captured the SQL the logging message said that the exception had "PEAKED" rather than "BEGAN". That is a confirmation that it saw the SQL a second time.) Your current definition almost guarantees that the first recognition of the exception will occur at the warning level because you scan every 30 seconds. 

3) lower your scan interval (recommending 5)
Lowering the scan interval to 5, the Informational alert 
should trip (no SQL captured) and then  the SQL will be 
captured at the warn level (if it's still running).

The info/warn/crit (if specified) should be 5 seconds apart.

Also, the EXCEPTION-INTERVAL sysparm should be lowered to 5 
(Default is 10). 

(Ideally, the scan interval should be LESS than the 
difference between thresholds, but with current design 5 
is as low as you can go.)

 

Additional Information

Recommendation was made to the customer to open an 'Idea' in communities for a design change in the exception processor to capture and write the sql on the first pass the exception occurs.