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.

First, set the exception threshold for class 2 CPU time to a value applicable to your environment. To do this, select option 4 from the OMPE menu. This displays the Data Set Maintenance Menu.
Figure 1. Data Set Maintenance Menu
 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.

The Exception Threshold Category Selection panel is displayed.
Figure 2. 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.

The Exception Threshold Field Selection panel is displayed.
Figure 3. Exception Threshold Field Selection panel
 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.

The Exception Threshold Field Details panel is displayed.
Figure 4. Exception Threshold Field Details panel
 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.

Note: Choose a threshold value that is applicable to your environment. For information about defining exception threshold values, see How to define exception threshold values.
Now that the exception threshold has been defined, select option 2 (Control Exception Processing) from the Online Monitor Main Menu to display the Exception Processor panel.
Figure 5. 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.

The Exception Notification window indicates that a periodic exception has occurred. You can examine the exception in greater detail using the LOOK command. Type LOOK 1 on any command line and press Enter to display the Periodic Exceptions List window.
Figure 6. 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.