DB2 Version 9.7 for Linux, UNIX, and Windows

Collecting data for individual activities

You can use an ACTIVITIES event monitor to collect data for individual activities that run in your system. The data collected includes items such as statement text and compilation environment, and can be used to investigate and diagnose problems, and as input to other tools (for example, the Design Advisor).

About this task

You can collect information about individual activities for service subclasses, workloads, work classes (through work actions), and threshold violations. You enable activity collection using the COLLECT ACTIVITY DATA keyword of the CREATE and ALTER statements for these DB2® workload manager objects. When an activity completes, information about the activity is sent to the active ACTIVITIES event monitor if:

The activity was submitted by an application that is mapped to a workload for which COLLECT ACTIVITY DATA is specified, or
The activity runs in a service subclass for which COLLECT ACTIVITY DATA is specified, or
The activity has a COLLECT ACTIVITY DATA work action applied to it, or
The activity violates a threshold that was defined with the COLLECT ACTIVITY DATA action

You can also use the WLM_SET_CONN_ENV procedure to turn on activity collection for your own application's connection before executing the user's query, then execute the user's query, then use WLM_SET_CONN_ENV to turn off activity collection for your application's own connection. Assuming that you have created and activated an activity event monitor, the application could look something like the following:

call WLM_SET_CONN_ENV(cast (NULL as bigint), '<collectactdata>WITHOUT DETAILS</collectactdata>')

... execute user's query ...

call WLM_SET_CONN_ENV(cast(NULL as bigint), '<collectactdata>NONE</collectactdata>')

The COLLECT ACTIVITY DATA keyword also controls the amount of information that is sent to the ACTIVITIES event monitor. If the keyword specifies WITH DETAILS, statement information (such as statement text) is collected. If the keyword specifies WITH DETAILS AND VALUES, data values are collected as well.

An activity might have multiple COLLECT ACTIVITY DATA keywords applied to it. For example, the activity might run in a service subclass for which COLLECT ACTIVITY DATA is specified, and while executing it might violate a threshold that has the COLLECT ACTIVITY DATA action. In this situation, the activity is only collected once. The COLLECT keyword that specifies the largest amount of information to be collected is applied to the activity. For example, if both COLLECT ACTIVITY DATA WITHOUT DETAILS and COLLECT ACTIVITY DATA WITH DETAILS are applied to an activity, the activity is collected with detailed information.

If the ON ALL DATABASE PARTITIONS keywords are used with the COLLECT ACTIVITY DATA clause, an activity record will be captured on each partition where the activity executes in a partitioned database environment. Activity event monitor records are written when the last agent working on the activity at that partition completes execution. Depending on the sequencing of events in a section, it is possible for agents to start and stop working on an activity at a partition several times, causing multiple activity records to be captured at that partition for the same query. The total work done by the activity on that partition is the aggregate of the metrics for each record that is captured for the activity on the partition.

To enable collection of activities for a given DB2 workload manager object:

Procedure

Use the CREATE EVENT MONITOR statement to create an ACTIVITIES event monitor.
Use the COMMIT statement to commit your changes.
Use the SET EVENT MONITOR STATE statement to activate the event monitor. Instead of using the SET EVENT MONITOR STATE statement, you can use the AUTOSTART default for the ACTIVITIES event monitor to have it activated the next time that the database is activated. If you want to define multiple ACTIVITIES event monitors, you should not use the AUTOSTART option.
Use the COMMIT statement to commit your changes.
Identify the objects for which you want to collect activities by using the ALTER SERVICE CLASS, ALTER WORK ACTION SET, ALTER THRESHOLD, or ALTER WORKLOAD statement and specify the COLLECT ACTIVITY DATA keywords.
Use the COMMIT statement to commit your changes.

Results

Note: Individual activity collection is more expensive than workload management statistics collection. You should try to set up activity collection to collect as few activities as possible. For example, if you need to investigate activities submitted by a specific application, you could isolate that application by creating a workload or service class specifically for that application, and only enable activity collection for that workload or service class.

You might not always know in advance that you will want to capture an activity. For example, you might have a query that is taking a long time to run and you want to collect information about it for later analysis. In this situation, it is too late to specify the COLLECT ACTIVITY DATA keyword on the DB2 workload manager objects, because the activity has already entered the system. In this situation, you can use the WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure. The WLM_CAPTURE_ACTIVITY_IN_PROGRESS stored procedure sends information about an executing activity to the active ACTIVITIES event monitor. You identify the activity to be collected using the application handle, unit of work identifier, and activity identifier. Information about the activity is immediately be sent to the ACTIVITIES event monitor when the procedure is invoked: you do not need to wait for the activity to complete.