Activity event monitoring

The activity event monitor captures data that is related to activities that run on the system. You can use this event monitor to gather data to help you better understand the performance and behavior of statements and of the load on your system in general. The activity event monitor records information after the completion of each activity in the system. By contrast, the unit of work event monitor records data at the completion of each transaction. Using the activity event monitor, you can examine monitor elements related to the execution of individual statements.
The data that the activity event monitor returns complements the data that the following table functions return:
  • MON_GET_ACTIVITY_DETAILS
  • MON_GET_PKG_CACHE_STMT
  • MON_GET_PKG_CACHE_STMT_DETAILS
Whereas the event monitor returns historical information about activities that ran on the system, the table functions provide information about activities that have run or have recently run on the system.

Uses of the activity event monitor

Use with other event monitors
The activity event monitor is particularly useful in conjunction with other event monitors. For example, you might want to capture information about the execution of a statement that violates a threshold that you define. In this case, you perform the following steps:
  1. Define the threshold by using the CREATE THRESHOLD statement. As part of defining the threshold, specify the COLLECT ACTIVITY DATA clause to have activity data recorded by any active activity event monitors.
  2. Create a threshold violations event monitor to capture details about when the threshold was violated, along with other data about what was happening in the system at that time.
  3. Create an activity event monitor that captures the activity information that the threshold violation generates.
  4. Run your application or workload.
  5. Query the event monitor output to view information about what was happening when the threshold was violated. You might perform a join of data from the threshold violations event monitor with the data from the activity event monitor to pinpoint the statement that was running when the violation occurred.
This process is described in more detail in Example: Capturing activity information related to the execution of a statement.
Other applications of the activity event monitor include the following ones:
  • Capturing information about long-running queries. In this case, you run the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure to force the collection of information about an activity before the activity is finished. Running this procedure is useful if you want to terminate a long-running statement but also want to capture information about it.
  • Seeing what statements the applications in a specific workload are running.
Input to commands, procedures, or tools
The data produced by the activity event monitor can be used as input for various tools and stored procedures, including:
db2advis - Design Advisor command
The db2advis command can use the output of the activity event monitor to produce recommendations about the following items and activities:
  • Materialized query tables (MQTs)
  • Indexes
  • Repartitioning of tables
  • Conversion to multidimensional clustering (MDC) tables
  • Deletion of unused objects
db2expln - SQL and XQuery Explain command
The db2expln command can use section information from the activity event monitor to describe the access plan for statements related to a section.
EXPLAIN_FROM_ACTIVITY stored procedure
The EXPLAIN_FROM_ACTIVITY stored procedure explains a specific execution of a statement by using the contents of the section that the procedure obtains from an activity event monitor. The Explain output is placed in the Explain tables for processing using Explain tools (for example, the db2exfmt command). The Explain output contains, if available, both the access plan and section actuals (runtime statistics for operators in the access plan).
Workload management historical analysis tools
The wlmhist.pl and wlmhistrep.pl Perl scripts perform historical analysis by using information that the activities event monitor captures.
Section actuals
Another use of the activity event monitor is to capture section actuals. You can use this data to compare the actual values that an activity event monitor captures with the estimated costs in an access plan. Doing this comparison can let you see whether the access plan is still valid.