Example: Capturing activity information related to the execution of a statement
Before you begin
About this task
One possible reason for a slowdown might be unfavorable data distribution. For example, if the STOCK table has only a few rows for most part numbers, but has several thousand for one particular part number, it takes longer to run this SELECT statement. The example that follows shows how you can retrieve the actual values that are processed for the parameter marker (
SELECT DISTINCT PARTS_BIN FROM STOCK WHERE PART_NUMBER = ?
?) by the activity associated with the preceding query.
To test the hypothesis that unfavorable data distribution is the cause of the slow-running query, you can create a threshold for the statement in question. Then you can use the threshold and activity event monitors to capture information about the execution of that particular statement. From this information, you can determine the actual value that was processed by the query that ran for longer than expected.
- Create a threshold
for the statement in question, specifying that a threshold violation
event occurs when the statement runs for longer than 10 seconds:
CREATE THRESHOLD TH1 FOR STATEMENT TEXT 'SELECT DISTINCT PARTS_BIN FROM STOCK WHERE PART_NUMBER = ?' ACTIVITIES ENFORCEMENT DATABASE WHEN ACTIVITYTOTALTIME > 10 SECONDS COLLECT ACTIVITY DATA WITH DETAILS, SECTION AND VALUES CONTINUE
- Create a threshold event monitor to record threshold violations:
CREATE EVENT MONITOR STMT_THRESH_VIOLATIONS FOR THRESHOLD VIOLATIONS WRITE TO TABLE AUTOSTART
- Create an activity event monitor to record detailed activity
CREATE EVENT MONITOR ACTIVITIES FOR ACTIVITIES WRITE TO TABLE
- Enable the new event monitors:
SET EVENT MONITOR ACTIVITIES STATE 1 SET EVENT MONITOR STMT_THRESH_VIOLATIONS STATE 1
- Run the application that executes the statement. If a threshold violation occurs, the threshold violations event monitor STMT_THRESH_VIOLATIONS records information about the threshold violation; information about the activity associated with the threshold violation is recorded by the activity event monitor ACTIVITIES.
- To determine whether a threshold violation occurred, query
the number of violations recorded by the threshold event monitor for
the threshold TH1 defined in step 1. To perform
this query, join the view SYSCAT.THRESHOLDS with the table that was
produced by the thresholds event monitor that contains the threshold
violation information. This join is necessary because the threshold
name TH1 is maintained in SYSCAT.THRESHOLDS:
SELECT COUNT(1) NUM_VIOLATIONS FROM THRESHOLDVIOLATIONS_DB2THRESHOLDVIOLATIONS T JOIN SYSCAT.THRESHOLDS S ON T.THRESHOLDID = S.THRESHOLDID WHERE S.THRESHOLDNAME = 'TH1';
In this case, there was one threshold violation; one execution of the statement identified in step 1 ran for longer than 10 seconds.
NUM_VIOLATIONS -------------- 1 1 record(s) selected.
- Examine the data (the part number) that is represented
by the parameter marker (
?) in the statement that you identified in 1.In the following example, the SELECT statement retrieves the value for the parameter marker (represented by STMT_VALUE_DATA in the SQL that follows) from one of the ACTIVITYVALS tables that the activity event monitor produces:
In the preceding example, the select statement retrieves the value for the parameter marker (STMT_VALUE_DATA) from the one of the tables produced by the activity event monitor.
SELECT SUBSTR(V.STMT_VALUE_DATA, 1, 80) PARAM_MARKER_VALUE FROM ACTIVITYVALS_ACTIVITIES V JOIN THRESHOLDVIOLATIONS_STMT_THRESH_VIOLATIONS T ON T.APPL_ID = V.APPL_ID AND T.UOW_ID = V.UOW_ID AND T.ACTIVITY_ID = V.ACTIVITY_ID JOIN SYSCAT.THRESHOLDS S ON T.THRESHOLDID = S.THRESHOLDID WHERE S.THRESHOLDNAME = 'TH1';
PARAM_MARKER_VALUE -------------------------------------------------------------------------------- 475299
- Now that you know the value for the PART_NUMBER associated with the long-running statement, you can examine the STOCK table to see if there is anything about the occurrences of that part number in the table that might lead to longer query times. For example, many rows that contain 475299 as the value for the PART_NUBMER (as compared to the number of rows for other part numbers) might be a reason that the query runs longer when this value is encountered.
Variation: Defining a threshold for a statement by using the executable ID
In this example, the executable ID that follows the keywords STATEMENT REFERENCE is used to look up the corresponding statement text in the package cache. The executable ID for a statement can be determined by examining the package cache. For more information about how to view information contained in the package cache, including the executable ID for a statement, see Using package cache information to identify statements that are candidates for performance tuning .
CREATE THRESHOLD TH1 FOR STATEMENT REFERENCE x'0100000000000000020000000000000000000000020020100304162158584850' ACTIVITIES ENFORCEMENT DATABASE WHEN ACTIVITYTOTALTIME > 10 SECONDS COLLECT ACTIVITY DATA WITH DETAILS, SECTION AND VALUES CONTINUE;
If the executable ID is found in the package cache, the associated statement text is retrieved from the package cache and is used for defining the statement threshold. For statements in static SQL sections, if the executable ID is not in the package cache, the statement text is retrieved from the system catalogs. For statements in dynamic SQL sections, consider using the PREPARE statement to create a prepared statement from the statement string. If the executable ID cannot be found in the package cache or the system catalogs, an error (SQL4721N) is returned.