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
_ Display thread summary
_ Display thread detail
_ Display statistics detail
/ 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.
When a periodic exception occurs, the Online Monitor notifies you
by displaying the Exception Notification window. This window is overlaid
on the current panel. Figure 6. Exception Notification
window (overlaid on Thread Detail panel)
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 7. 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.
In this example the thread causing the exception has ended. However,
you can still select the exception to be examined from the list displayed in the Periodic Exceptions List window. If history
and data collector are available, the Online Monitor retrieves past
data and displays the Thread Detail panel where you can examine the
thread causing the exception.
Some fields can have values that are longer than the space available
to show them in this panel. When this happens, the last character
in the field is replaced with an asterisk (*) to indicate that the
reported value is truncated.
The word HISTORY is displayed under the heading
line of the Thread Detail panel to indicate that you are viewing past
data. The date and time displayed next to HISTORY represent
the time just after the exception occurred.
This is where you can use past data to examine the data on a thread
that has ended. In this way, you can examine the thread before and
after the exception occurred by moving backward and forward through
time. See Viewing past data for more information
about viewing past data.
Because class 2 time is the time spent within Db2, the excessive class 2 time causing the exception
could be caused by a possible SQL statement problem.
From the Thread Detail panel, select SQL Statement and
Package to display the SQL Statement and Package window. Figure 9. SQL Statement and Package window
This window displays the SQL statement executing at the time the
exception occurred. To obtain details about the
access paths chosen by Db2 to
process the SQL statement, press F18 (Explain) to explain the SQL
statement and display the Db2 Explain
Output panel.
Figure 10. Db2 Explain
Output panel (packages)
DGOMYPKM DB2 Explain Output More - +
Local location . . . . . . : PMO1DLOC DSN1 V10
Current server . . . . . . : PMO1DLOC DSN1 V10
_ Package . . . . . . . . . . . . : EMPLOYEE.EMPSRCH
Version . . . . . . . . . . . . : EMPSRCH_PACKAGE_VERSION1
Explain executed at . . . . : 13:10:52 04/17/08
------------------------------ SQL Text -----------------------------------
_ SELECT EMPNO, LASTNAME, WORKDEPT, BIRTHDATE FROM DSNB710.EMP WHERE
EMPNO = :EMPLOYEE_NUMBER
Status : Compiled-REPORT specified. Rebound at exec.time using values
Isolation: Cursor stability
_ Host variable definitions
----- Access path summary for query block 1 step 1 -----
Table space scan - no index will be used
Standard sequential prefetch will be performed
Lock mode is share lock for the page
Page range scan will not be used
/ Table DSNB710 EMP
_ PLAN_TABLE details for step
----- Access path summary for query block 1 step 2 -----
Command ===> _________________________________________________________________
F1=Help F2=Split F3=Exit F7=Up F8=Down F9=Swap
F12=Cancel F16=Look F17=Collect
The access path summary in the Db2 Explain
Output panel shows that a table space scan is being used to access
the table. This could indicate the reason for the excessive class
2 time.
Select the table from the Db2 Explain
Output panel to display the Table Information window, where you can
see if any indexes have been defined for the table. Figure 11. Table Information
window
From this window you can see that an index has been defined for
the table. Select Indexes to display the Index Selection
window where you can select the index to be examined. In this example,
the index selected is DSNB710.XEMP1. This displays the Index Information
window. Figure 12. Index Information window
Use this window to examine the characteristics of the key column
on which the index has been defined.
One of the possible reasons for an index not being used is that
the host variable defined in the program does not match the characteristics
of the column as defined in the table. To determine whether this is
the reason for the index not being used, you can return to the Db2 Explain Output panel where the
Host Variable Definition window can be selected.
Figure 14. Host Variable Definition window
─ Host Variable Definition ─
DGOMYWHV
Local location . . . . . . . : PMO1DLOC DSN1 V10
Current server . . . . . . . : PMO1DLOC DSN1 V10
Name Type Length
EMPLOYEE_NUMBER FIXED CHARACTER 8
Command ===> __________________________________________________________
F1=Help F2=Split F3=Exit F7=Up F8=Down F9=Swap
F12=Cancel F16=Look F17=Collect
In this particular example, the Host Variable Definition window
shows that the definitions do not match, thereby disqualifying use
of the index.
The host variable defined in the source program should be changed
to match the table definition so that Db2 can
use the index and thereby reduce the class 2 time.