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

If you identify a statement that is taking a long time to execute, you can define a threshold that causes an activity event monitor to capture information about the execution of that statement when the threshold is exceeded. You can then correlate statement execution information with information collected by the activity event monitor to view activity metrics that can help you understand what might be causing the slowdown.

Before you begin

Before capturing activity information, you must identify the statement in question; for example, a user or application developer might complain that a specific statement runs longer than expected. Or, you might identify a statement that is taking longer to run by using the package cache event monitor.

About this task

In this example, the query that is being investigated runs as part of an application. The query is as follows:
SELECT DISTINCT PARTS_BIN FROM STOCK WHERE PART_NUMBER = ?
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 (?) by the activity associated with the preceding query.

Procedure

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.

Variation: Defining a threshold for a statement by using the executable ID

In the preceding example, the threshold is identified in step 1 explicitly, by using the actual text of the statement. You can also define the threshold indirectly, identifying the executable ID for a statement contained in the package cache. For example, you can define the threshold as follows:
CREATE THRESHOLD TH1
   FOR STATEMENT REFERENCE 
     x'0100000000000000020000000000000000000000020020100304162158584850' ACTIVITIES
   ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTALTIME > 10 SECONDS 
   COLLECT ACTIVITY DATA WITH DETAILS, SECTION AND VALUES
   CONTINUE;
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 .

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.