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.
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.
The following figure shows how the lifetime of a long running query breaks down into queue time and execution time:
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:
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 do not significantly affect activity-based monitoring and control of workloads, but some additional information applies.
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.
By contrast, the activity would be recorded only once in the event data collected on the coordinating member.