Example: Capturing activity information related to the execution of a statement

SELECT DISTINCT PARTS_BIN FROM STOCK WHERE PART_NUMBER = ?

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.

1. 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

2. Create a threshold event monitor to record threshold violations:

   CREATE EVENT MONITOR STMT_THRESH_VIOLATIONS 
     FOR THRESHOLD VIOLATIONS 
     WRITE TO TABLE
     AUTOSTART
   

3. Create an activity event monitor to record detailed activity information:

   CREATE EVENT MONITOR ACTIVITIES 
      FOR ACTIVITIES 
      WRITE TO TABLE
   

4. Enable the new event monitors:

   SET EVENT MONITOR ACTIVITIES STATE 1
   SET EVENT MONITOR STMT_THRESH_VIOLATIONS STATE 1

5. 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.
6. 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';
   
   

   NUM_VIOLATIONS          
   --------------
             1
   
     1 record(s) selected.

   In this case, there was one threshold violation; one execution of the statement identified in step 1 ran for longer than 10 seconds.
7. 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:

   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';
   

   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.

   
   PARAM_MARKER_VALUE
   --------------------------------------------------------------------------------
   475299

8. 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.

CREATE THRESHOLD TH1
   FOR STATEMENT REFERENCE 
     x'0100000000000000020000000000000000000000020020100304162158584850' ACTIVITIES
   ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTALTIME > 10 SECONDS 
   COLLECT ACTIVITY DATA WITH DETAILS, SECTION AND VALUES
   CONTINUE;