DB2 10.5 for Linux, UNIX, and Windows

Guidelines for capturing information about and investigating a rogue activity

This topic provides guidelines for capturing information about, and investigating, a rogue activity.

First establish a set of criteria for what you would consider a rogue activity. For example:

An activity in that runs in a service class for activities with a low estimated cost, and runs for more that 1 hour
An activity that returns an unusually large number of rows
An activity that consumes an unusually high amount of temporary table space

Then create thresholds that describe these criteria and contain a COLLECT ACTIVITY DATA WITH DETAILS action. When the threshold is violated, information about the activity that violated the threshold is sent to the active ACTIVITIES event monitor when the activity completes.

For example, to collect information about any database activity that runs for more than 3 hours, create a threshold such as the following threshold:

CREATE THRESHOLD LONGRUNNINGACTIVITIES
   FOR DATABASE ACTIVITIES ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTALTIME > 3 HOURS COLLECT ACTIVITY DATA WITH DETAILS
   CONTINUE

Monitoring with DB2® workload manager is lightweight, if you are careful to apply it only to a small subset of your queries, as shown in the example, where only queries running for at least three hours are monitored. You can refine this example further by creating a threshold not at the global database level, but at the level of a user-defined superclass. If this more narrowly scoped monitoring suits your purpose, it can further reduce the cost of monitoring and it will provide information only at the level you need:

CREATE SERVICE CLASS LONGQUERIES
   PREFETCH PRIORITY LOW

CREATE THRESHOLD LONGRUNNINGACTIVITIES2
   FOR SERVICE CLASS LONGQUERIES ACTIVITIES ENFORCEMENT DATABASE
   WHEN ACTIVITYTOTALTIME > 3 HOURS COLLECT ACTIVITY DATA WITH DETAILS
   CONTINUE

After your data server has performed some work, you can analyze the information that is written to the threshold violations and activities event monitors. DML activities also have their statement text and compilation environment information written to the activities event monitor, so you can run DB2 explain on them to further investigate the performance of the activity.