DB2 Version 10.1 for Linux, UNIX, and Windows


One way that you can monitor and control workloads is on the basis of individual activities. Each time your DB2® data server executes the access plan for an SQL or XQuery statement or executes the load utility a corresponding activity is created.

For workload monitoring, commonly used monitor elements provide information in terms of activity units. For example, you can obtain information about the workload volume and response time from monitor elements such as the activity execution time monitor element (coord_act_exec_time) and the high watermark for the concurrent activities (concurrent_act_top).

For workload control, most workload controls and thresholds apply to each activity. For example, the ACTIVITYTOTALTIME threshold controls the maximum time that your data server can spend processing an activity.

Statements or commands that trigger activities on your data server

The following statements or commands trigger activities on your data server:
  • All DML statements
  • All DDL statements
  • The CALL statement
  • The load utility

The life cycle of activities

The life cycle of an activity for a DML statement does not include processing that occurs before or outside of access plan execution. This implies that activity-based monitoring does not cover operations such as connecting to the database or compiling SQL into an access plan.

During its life cycle, an activity can spend time in various states, which are reported by the activity_state event monitor element. Some of the states an activity can be in are:
  • EXECUTING - This state indicates that the coordinator agent is working on the activity. An activity that encounters a lock wait situation is reported as executing. The total amount of time spent executing activities is returned by the total_act_time monitor element.
  • IDLE - This state indicates that the coordinator agent is waiting for the next request from a client.
  • QUEUED - Some thresholds include a built-in queue. This state indicates that the activity is waiting in the queue for its turn to begin executing.
Monitoring data for the activity is aggregated at the end of the lifetime of an activity.

The following figure shows how the lifetime of a long running query breaks down into queue time and execution time:

Lifetime, queue time, and execution time of a cursor activity

Types of SQL statements and application development

This section describes what activities are created for various SQL statements and identifies the start and end points in the lifetime of these activities. You can use this information to understand how SQL statements are monitored and controlled through activities.

SELECT statements: A SELECT statement is represented by one activity. This includes any cursor requests such as FETCH operations and subselects or subqueries. The activity starts when your DB2 data server starts processing an OPEN cursor statement or request, and ends when your data server has completed processing for a CLOSE cursor statement or request.

SELECT statements using WITH HOLD cursors: When a WITH HOLD cursor is used, an application can open a cursor within one unit of work and close the cursor in a subsequent unit of work. The cursor remains open for multiple units of work. The corresponding activity exists for as long as the cursor is open, because the life cycle of the activity ends only after the cursor is closed.

CALL statement and stored procedures: A CALL statement itself is represented by one activity, but the payload of the stored procedure can spawn nested activities as follows:

Table 1. Contents of stored procedures and activities they create
Contents of stored procedure Additional activities created
A single SQL statement One
No SQL statements in the stored procedure 0
SQL procedures, multiple SQL statements, and looping logic Multiple activities, one corresponding to each invocation of each statement
A call to another stored procedure Activities for that stored procedure

The activity associated with the CALL statement starts when your DB2 data server starts processing the statement or request and ends after the stored procedure processing is complete.

Triggers and UDFs: When a SQL statement calls a trigger or UDF, no additional activity is created. The work done by that trigger or UDF is accrued to the activity for the SQL statement that called it. Cases where the trigger or UDF executes additional SQL statements are handled like any statement execution, that is, an activity is created for each statement.

PREPARE statement: No activity is created, because activities are not created until an access plan is executed.

Nested activities

Nested activities do not significantly affect activity-based monitoring and control of workloads, but some additional information applies.

Activities that can have nested activities within them are:
  • A stored procedure
  • An anonymous block
  • An autonomous routine
  • A DML activity that executes a UDF
  • A load from cursor (a load activity that has the cursor activity nested within it)
  • A DML activity that is subject to a trigger that contains any of the activities listed previously as part of the trigger definition
Nested activities are reported in monitoring information as follows:
  • A nested activity is indicated by a nonzero parent UOW ID and a nonzero parent activity ID.
  • A nested activity is not counted towards histograms or any statistics derived from histograms.
  • Data for a nested activity is not also reported as part of the metrics for the parent activity. For example, if a procedure executed by a CALL statement performs an insert which consumes 10 seconds of processor time, that processor time is counted only towards the processor time metric for the insert activity and does not count towards the processor time metric for the parent CALL activity.
Workload control considers nested activities as follows:
  • An activity nested inside a UDF or trigger does not contribute to the CONCURRENTDBCOORDACTIVITIES threshold.
  • A cursor activity nested within a load activity does not contribute to the CONCURRENTDBCOORDACTIVITIES threshold.

Activities and the load utility

Running the load utility will generate several activities, one of which is a load activity and several others that are of type READ, WRITE, or OTHER. In the case of a load from cursor, an additional activity for the cursor the load activity is loading from is created. This cursor activity is a nested activity of the load activity.

Activities event monitoring

When monitoring activities with an activities event monitor in a multimember database environment, you must be aware of how activity event information is captured. If you are capturing activity events on all members, you might see more than one event for a given activity on members other than the coordinator. Multiple records per activity might be recorded because the activity might come and go on the remote members, depending on the sequencing of events in a section (the executable form of an SQL statement). As a result, multiple records might be captured for the activity on a non-coordinating member. To understand the processing performed by the activity on the remote member, you must consider all the records for the activity. For example, you might want to aggregate metrics from all records for the activity on the remote member.

By contrast, the activity would be recorded only once in the event data collected on the coordinating member.