Example of exception processing
This example demonstrates how you can use exception processing to discover a possible class 2 CPU (processing) time problem, and identify the cause by viewing past data and analyzing the access paths chosen by Db2® to process an SQL statement. The data collector needs to be installed at your site to view past data.
DGOPMENU Data Set Maintenance Menu
Select one of the following.
1 1. Maintain exception thresholds
2. Maintain correlation translations
3. Maintain time zone information
4. Maintain MAINPACK definitions
Exception data set
'SAMPLE.EXCEPT.DATASET'
DPMPARMS data set
______________________________________________
Command ===> __________________________________________________________________
F1=Help F2=Split F3=Exit F6=History F9=Swap F12=Cancel
F17=Collect
Ensure that you have specified an Exception Threshold data set name in the 1
field. Select option 1 (Maintain exception thresholds
) to display the
Exception Threshold Category Selection panel.
DGOPXDS1 Exception Threshold Category Selection
Select one or more categories, then press Enter. Overtype with space to
deselect any category. Request EXIT when complete.
Category
/ Elapsed, CPU and Waiting Times per Plan Execution
_ Elapsed, CPU and Waiting Times per Program Execution
_ CPU Times per Address Space
_ SQL Statements per Plan Execution
_ SQL Statements per Program Execution
_ SQL Statements per System
_ Subsystem Events per Plan Execution
_ Subsystem Events per System
_ Locking Activity per Plan Execution
_ Locking Activity per System
_ RID List Processing per Plan Execution
_ RID List Processing per System
_ Query Parallelism per Plan Execution
_ Query Parallelism per System
_ Buffer Pools Activity per Plan Execution
_ Buffer Pools Activity per System
_ Distributed Activity per Location per Plan Execution
Command ===> __________________________________________________________________
F1=Help F2=Split F3=Exit F7=Up F8=Down F9=Swap F12=Cancel
Select the category that contains the exception field you want. For this example,
select the Elapsed, CPU and Waiting Times per Plan Execution
field.
DGOPXDS2 Exception Threshold Field Selection
Select only one field, then press Enter. Overtype with space to
deselect any field. Request EXIT when complete.
Field category . . : Elapsed, CPU, and Waiting Times per Plan Execution
Field Description
_ ADRECETT Elapsed time in application (Class 1)
_ ADCPUT CPU time in application (Class 1)
_ ADDB2ETT Elapsed time in DB2 (Class 2)
/ ADDBCPUT CPU time in DB2 (Class 2)
_ ADTWTDB Total wait time in DB2 (Class 2)
_ ADTWTAP Total wait time in application (Class 1)
_ ADTSUST Total Class 3 suspensions time
_ ADTSUSC Total Class 3 suspensions
_ QWACAWTL Lock/latch suspensions time (Class 3)
_ ADLLSUSC Lock/latch suspensions (Class 3)
_ QWACAWTI Synchronous I/O susp. time (Class 3)
_ ADIOSUSC Synchronous I/O suspensions (Class 3)
_ QWACAWTR Other read I/O susp. time (Class 3)
Command ===> __________________________________________________________________
F1=Help F2=Split F3=Exit F7=Up F8=Down F9=Swap F12=Cancel
This panel shows all available fields within the specified category. Select the
ADDBCPUT
field and press Enter.
DGOPXDSN Exception Threshold Field Details
ENTRY 1 OF 1
Category . . . . . : Elapsed, CPU, and Waiting Times per Plan Execution
Field ID . . . . . : ADDBCPUT
Description . . . . : CPU time in DB2 (Class 2)
Active . . . . . . . 1 1=Yes 2=No
By . . . . . . . . . 1 1=Total 2=Minute 3=Second
4=Commit 5=Thread
Compare operator . . > <=Less than >=Greater than
Warning threshold . . 0.09
Problem threshold . . 0.1
Local location . . . *
Group name . . . . . *
Member name . . . . . *
Subsystem ID . . . . *
Requester location *
Connect . . . . . . . *
Planname . . . . . . *
Corrname . . . . . . *
Corrnmbr . . . . . . *
Primauth . . . . . . *
Command ===> __________________________________________________________________
F1=Help F2=Split F3=Exit F5=Add F6=Delete F7=Up
F8=Down F9=Swap F10=Previous F11=Next F12=Cancel
Use the Exception Threshold Field Details panel to specify the threshold criteria for the
exception field selected from the Exception Threshold Field Selection panel. For this example,
enter a 1
in the Active
field, set the By
field to Total
(1), the Compare operator
field to a
greater than symbol (>
), and the Problem
threshold
field to a value of 0.1
seconds.
Control Exception
Processing
) from the Online Monitor Main Menu to display the Exception Processor
panel.
DGOMEP02 Exception Processor PMO1DLOC DSN1 V10
For any field enter any character to activate
Activate/Deactivate Exception Processing
/ Periodic _ User Exit
_ Exception event notification
Options
Periodic units . . . . . . . . . . . . . . 1 1=Seconds
2=Minutes
Periodic interval . . . . . . . . . . . . 10 1-7200 Seconds
1-120 Minutes
> Disable auto-display for problem exceptions
> Sound alarm for exception warnings
_ Log file data set output needed
_ DPMOUT data set output needed
Exception threshold data set
Name . . . . . . . . . SAMPLE.EXCEPT.DATASET
Command ===> __________________________________________________________________
F1=Help F2=Split F3=Exit F7=Up F8=Down F9=Swap
F12=Cancel F16=Look F17=Collect
Use the Exception Processor panel to activate periodic exception processing. Enter the
Exception Threshold data set name you specified on the Data Set Maintenance Menu and type a
forward slash (/
) in the Periodic
field to
activate periodic exception processing. In this example, the exception processor has been set
to check for periodic exceptions every 10 seconds.
For more information about activating exception processing, see How to start exception processing.
LOOK 1
on any command line and press Enter to display the Periodic Exceptions List window. DGOMLAXP Periodic Exceptions List Row 499 to 500 of 500
Periodic Interval started . . . . . . . : 05/03/08 11:43:26:10
Last Interval . . . . . . . . . . . . . : 05/03/08 14:27:41:22
Time Location Group Subsystem Member Corrname
Reqloc Primauth Planname Connect Corrnmbr
Field Value Compare Threshold Type By
Descr
-------- ------------------ -------- ------------ -------- ----------
_ 14:27:41 PMO1DLOC GROUP001 DSN1 MEMBER01 TESTDC6
'BLANK' TFA6 CICSEMP APPCICP3 'BLANK'
ADDBCPUT 0.108004 > 0.1 Problem Total
CPU TIME IN DB2 (CLASS 2)
***************************** Bottom of data ******************************
Command ===> ______________________________________________________________
F1=Help F2=Split F3=Exit F7=Up F8=Down F9=Swap
F12=Cancel
From this window you can examine a list that contains the last 500 periodic exceptions that have occurred. The most recent exceptions are displayed at the bottom of the list.