Exercise 9: Capturing detailed information about an executing activity

Activity information you capture is sent to the active event monitor for activities. Previous tasks showed how the COLLECT ACTIVITY DATA clause is used for workloads, service classes, work actions and thresholds to capture detailed activity information. This clause needs to be specified in advance before an activity begins executing and information is sent to the activities event monitor when the activity completes. The WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure permits you to capture information reactively when you notice a problem with an activity already in progress. When this procedure is used, information about an activity is sent to the activities event monitor immediately. Both basic and statement activity information are collected, but not input data.

Step 1: Enable activities event monitor

Enable the existing event monitor for activities you created in Exercise 1.

CONNECT TO SAMPLE

SET EVENT MONITOR DB2ACTIVITIES STATE 1

Step 2: Issue a long running query

From the CLP, run the following script that issues a long running query with a problematic cursor:

db2 -tvf longquery.db2

Step 3: Get the application handle

From a second CLP window, call WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES to obtain the application handle, unit of work ID and activity ID for the activity that is run from within longquery.db2.

CONNECT TO SAMPLE

SELECT T.APPLICATION_HANDLE, T.UOW_ID, T.ACTIVITY_ID, T.ACTIVITY_TYPE
FROM  TABLE(WLM_GET_WORKLOAD_OCCURRENCE_ACTIVITIES
              (CAST(NULL AS BIGINT), -2)) T
      WHERE T.CLIENT_APPLNAME = 'CLP longquery.db2';

The output looks such as the following:

APPLICATION_HANDLE   UOW_ID      ACTIVITY_ID ACTIVITY_TYPE
-------------------- ----------- ----------- ----------------------------
----
                 267           1           1 READ_DML

1 record(s) selected.

Step 4: Capture information about the activity

From the same CLP window, call the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure using the application handle, unit of work ID, and activity ID obtained from the previous step:

CONNECT TO SAMPLE

CALL WLM_CAPTURE_ACTIVITY_IN_PROGRESS (267, 1, 1)

CONNECT RESET

This step sends information about the activity to the active event monitor for activities. Note that in your case, the application handle, unit of work ID, and activity ID you specify may be different.

Step 5: View the activity information

Look at the information that was collected for the activity using a statement such as the following:

SELECT VARCHAR(A.APPL_NAME, 15) as APPL_NAME,
     VARCHAR(A.TPMON_CLIENT_APP, 20) AS CLIENT_APP_NAME,
     VARCHAR(A.APPL_ID, 30) as APPL_ID,
     A.ACTIVITY_ID,
     A.UOW_ID,
     A.PARTIAL_RECORD,
     A.TIME_STARTED,
     A.TIME_COMPLETED,
     VARCHAR(S.STMT_TEXT, 300) AS STMT_TEXT
FROM ACTIVITY_DB2ACTIVITIES AS A,
     ACTIVITYSTMT_DB2ACTIVITIES AS S
WHERE A.APPL_ID = S.APPL_ID AND
     A.ACTIVITY_ID = S.ACTIVITY_ID AND
     A.UOW_ID = S.UOW_ID

The output looks something such as this:

APPL_NAME       CLIENT_APP_NAME      APPL_ID
ACTIVITY_ID          UOW_ID      PARTIAL_RECORD TIME_STARTED
TIME_COMPLETED             STMT_TEXT
--------------- -------------------- ------------------------------ -----
--------------- ----------- -------------- -------------------------- ---
----------------------- -------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
-------------------------------------------------------------------------
--------------------------------
db2bp           CLP longquery.db2    *LOCAL.swalkty.070928151408
1           1              1 2007-09-28-11.14.09.334636 0000-00-00-
00.00.00.000000 SELECT COUNT(*) FROM SYSCAT.TABLES, SYSCAT.TABLES,
SYSCAT.TABLES, SYSCAT.TABLES, SYSCAT.TABLES

Note: Activities that are captured using the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure contain somewhat less information than if the COLLECT ACTIVITY DATA clause were used, because the activity is captured before it has completed execution. In particular, fields such as the completion timestamp (which shows only zeros) and the sqlcode do not apply. You can determine if an activity was collected using the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure by looking at the PARTIAL_RECORD column in the ACTIVITY table. If the PARTIAL_RECORD column has a value of 1 (as shown in the previous output), activity information was collected using WLM_CAPTURE_ACTIVITY_IN_PROGRESS. If the PARTIAL_RECORD column has a value of 0, activity information was collected after completion with the COLLECT ACTIVITY DATA clause.