Statistics for Db2 workload management objects
Statistics are maintained for Db2 workload management objects including service classes, work classes, workloads, and threshold queues. These statistics reside in memory and can be viewed in real-time using Db2 workload management statistics table functions, or the statistics can be collected and sent to a statistics event monitor where they can be viewed later for historical analysis.
Note that you can also obtain monitoring metrics through the statistics event monitor. These are not discussed in this topic, which covers only those statistics that are specific to Db2 workload management.
When statistics are sent to the event monitor, the values in memory are reset to prevent duplicate data from being collected on subsequent collection intervals. Because the Db2 workload management statistics table functions report the current in-memory values, following a collection they report the reset values. The Db2 workload management table functions report only a subset of the statistics. To view the full set of statistics, you must collect the statistics and send them to a statistics event monitor.
Aggregate activity data statistics collection
Database object | Statistic | Description |
---|---|---|
Service classes |
|
|
Workloads |
|
|
Work class (through a work action) | Queue assignments total (queue_assignments_total) | Use this statistic to determine whether excessive queuing is occurring, or whether the right number of activities are being queued (that is, whether the concurrency threshold is too restrictive or not restrictive enough). |
Threshold queues |
|
|
Statistic or histogram | Description |
---|---|
Average request execution time (request_exec_time_avg) | Use this statistic to determine the arithmetic mean of the execution times for requests associated with a service class. |
Average coordinator activity lifetime (coord_act_lifetime_avg) | Use this statistic to determine the arithmetic mean of the lifetime for non-nested coordinator activities associated with a service class, workload or a work class. |
Average coordinator activity execution time (coord_act_exec_time_avg) | Use this statistic to determine the arithmetic mean of execution time for non-nested coordinator activities associated with a service class, workload or a work class. |
Average coordinator activity queue time (coord_act_queue_time_avg) | Use this statistic to determine the arithmetic mean of the queue time for non-nested coordinator activities associated with a service class, workload or a work class. |
Cost estimate top (cost_estimate_top) | Use this statistic to tune estimated cost thresholds. |
Actual rows returned top (rows_returned_top) | Use the information to tune the actual rows returned thresholds. |
Aggregate temporary table space top (agg_temp_tablespace_top) | Use this statistic to tune aggregate system temporary table space usage.
This statistic is monitored only if you define a threshold for aggregate temporary table space usage. For any given service class, this statistic is monitored whenever you define a AGGSQLTEMPSPACE threshold on the service class itself, or when you define a similar threshold on any service class within the same superclass. |
Temporary table space top (temp_tablespace_top) | Use this statistic to tune temporary table space usage thresholds. This statistic is monitored only if you define a threshold for temporary table space usage. For any given service class, this threshold is also monitored whenever you define a AGGSQLTEMPSPACE threshold on the service subclass itself, or when you define a similar threshold on any service class within the same superclass. |
Coordinator activity lifetime (CoordActLifetime) histogram | Use this histogram to obtain a view of overall system performance. This histogram collects the time duration (in milliseconds) between the activity arrival and end time for non-nested coordinator activities. If the activity is a routine that leaves a cursor open after it ends, the lifetime histogram does not count the lifetime of the cursor toward the lifetime of the routine that is the parent of the cursor. |
Coordinator activity execution time (CoordActExecTime) histogram | Use this histogram to measure the impact of changes to the system that affect
execution time. This histogram collects the execution time (in milliseconds) for non-nested coordinator activities. The execution time is calculated as follows:
|
Coordinator activity queue time (CoordActQueueTime) histogram | Use this histogram to measure the impact of queuing thresholds on
activities. This histogram collects the amount of time (in milliseconds) that non-nested coordinator activities spend queued. |
Statistic or histogram | Description |
---|---|
Coordinator activity estimated cost average (coord_act_est_cost_avg) | Use this statistic to determine the arithmetic mean of the estimated costs of coordinator DML activities at nesting level 0 that are associated with this service class, workload or work class since the last statistics reset. |
Non-nested coordinator activity inter-arrival time average (coord_act_interarrival_time_avg) | Use this statistic to determine the arithmetic mean of the time between the arrival of one coordinator activity at nesting level 0 that is associated with this service class, workload or work class and the next coordinator activity to arrive. The average is computed since the last statistics reset. |
Coordinator activity estimated cost (CoordActEstCost) histogram. | Use this histogram to obtain an approximate service time distribution. This histogram collects the estimated cost (in timerons) for non-nested coordinator activities. This data is useful for modelling your system or for inputting into performance-modelling applications. |
Coordinator activity inter-arrival time (CoordActInterArrivalTime) histogram. | Use this histogram to obtain the inter-arrival time distribution for
non-nested coordinator activities. This histogram collects the inter-arrival time (in milliseconds) for non-nested coordinator activities. This data is useful for modelling your system or for inputting into performance-modeling applications. |
Object type | Activity statistics always collected by default | Activity statistics collected when you specify COLLECT AGGREGATE ACTIVITY DATA BASE | Activity statistics collected when you specify COLLECT AGGREGATE ACTIVITY DATA EXTENDED |
---|---|---|---|
Service classes |
|
|
|
Workload |
|
|
|
Work class (through a work action) |
|
|
|
Threshold | N/A | N/A | N/A |
Threshold queue | N/A | N/A |
Aggregate request data statistics collection
Statistic or histogram | Description |
---|---|
Request execution time average (request_exec_time_avg) | Use this statistic to quickly understand the average amount of time that is spent processing each request on a member and to help tune the histogram template for the corresponding request execution time histogram. |
Request execution time (ReqExecTime) histogram | Use this histogram to understand where work is being performed and whether the
distribution of work across members is
uniform. This histogram indicates the volume of work executing in a service subclass and the distribution of this work across database members. The execution time (in milliseconds) for requests is collected in a histogram for each member and for all requests. This histogram includes requests on the coordinator member, and any subrequests on both coordinator and non-coordinator members (like RPC requests or SMP subagent requests). Requests included may or may not be associated with an activity: Both PREPARE and OPEN requests are included in this histogram, for example, but while OPEN requests are always associated with a cursor activity, PREPARE requests are not part of any activity. The request execution time approximates the effort spent by agents working in a service subclass. For example, coordinator activity counts might show that most user activities originate on one member, but as part of processing the activities, the coordinator agent might be sending subrequests to another member that performs most of the work. The request execution time histogram can be useful in determining the size of requests sent to a member, that is, whether the work that is sent to the member consists of mostly small requests or mostly large requests or whether there is no specific distribution. Request execution time histograms should not be used for activity response time analysis, because activities may be composed of a number of requests and subrequests, because there is no one-to-one mapping between request and activity execution time, and because not all requests are associated with activities. |
Object type | Request statistics always collected by default | Request statistics collected when you specify COLLECT AGGREGATE REQUEST DATA BASE |
---|---|---|
Service subclass |
|
|
Service superclass | N/A | N/A |
Workload | N/A | N/A |
Work class (through a work action) | N/A | N/A |
Threshold | N/A | N/A |
Threshold queue | N/A | N/A |