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

The following statistics are maintained on the given objects on each member, regardless of the value of the COLLECT AGGREGATE ACTIVITY DATA option specified for those objects when they are created or altered.
Table 1. Statistics collected for database objects regardless of COLLECT AGGREGATE ACTIVITY DATA setting
Database object Statistic Description
Service subclasses
  1. Concurrent activity top (concurrent_act_top)
  2. Coordinator activities completed total (coord_act_completed_total)
  3. Coordinator activities aborted total (coord_act_aborted_total)
  4. Coordinator activities rejected total (coord_act_rejected_total)
  5. The number of active requests (num_requests_active)
  6. The number of activities mapped in and the number of activities mapped out ( act_remapped_in and act_remapped_out)
  1. Use this activity concurrency high watermark to determine the highest concurrency of activities (including nested activities) reached on a member for a service class in the time interval for which the statistic is collected.
  2. Use this statistic to determine how much work is being performed in a service class.
  3. Use this statistic, which measures the unsuccessful completion of activities, to determine how healthy the system is. Activities can be aborted because of cancellation, errors, or reactive thresholds.
  4. Use this rejected non-nested coordinator activity count, which measures the rejection of activities, to obtain an indication of the usefulness of the rejection policy. Activities are counted as rejected when they violate a predictive threshold that has an action of STOP EXECUTION or when they are prevented from executing by a work action.
  5. Use this statistic to determine the number of requests that are currently executing in a service class.
  6. Use these statistics to determine the number of activities that are remapped into or out of a service subclass as part of the priority aging of ongoing activities.
Service superclasses Concurrent connection top (concurrent_connection_top) Use this coordinator connection concurrency high watermark to tune a connection concurrency threshold.
Workloads
  1. Concurrent workload occurrences top (concurrent_wlo_top)
  2. Concurrent workload occurrences activity top (concurrent_wlo_act_top)
  3. Coordinator activities completed total (coord_act_completed_total)
  4. Coordinator activities aborted total (coord_act_aborted_total )
  5. Coordinator activities rejected total (coord_act_rejected_total)
  6. Workload occurrences completed total (wlo_completed_total )
  7. Activities total (act_total)
  1. Use this workload occurrence high watermark to identify the maximum number of concurrent workload occurrences and to help set or tune a workload occurrence concurrency threshold if the number of concurrently executing workload occurrences is too high (that is, too many applications that are associated with the same workload definition are running on the system at the same time).
  2. Use this element to know the highest number of concurrent activities reached on a member for any occurrence of this workload in the time interval collected.
  3. Use this statistic, which measures the rate of successful completion of activities, to obtain an indication of the health of the system.
  4. Use this statistic, which measures the unsuccessful completion of activities, to determine how healthy the system is. Activities can be aborted due to cancellation, errors, or reactive thresholds.
  5. Use this statistic, which measures the rate of rejection of activities, to determine the usefulness of a rejection policy. Activities are counted as rejected when they violate a predictive threshold that has an action of STOP EXECUTION or when they are prevented from executing by a work action.
  6. Use this statistic to determine how many occurrences of a workload complete in a specific period of time.
  7. Use this statistic to determine the effectiveness of the work action set and determine the relative percentages of the types of activities on the system.
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
  1. Queue size top (queue_size_top)
  2. Queue time total (queue_time_total)
  1. Use this statistic to help determine the maximum queue size and to identify whether the queue size is sufficient.
  2. Use this statistic to determine how much time activities are spending in the queue and whether that time is excessive.
When you set the value of the COLLECT AGGREGATE ACTIVITY DATA option to BASE for a service subclass, workload, or a work class (through a work action), some of the following statistics are also collected, or the corresponding histograms are generated for each member. Use the averages to quickly understand where activities are spending most of their time (for example, queued or executing) and the response time (lifetime). You can also use the averages to tune the histogram templates. That is you can compare a true average with the average computed from a histogram, and if the average from the histogram deviates from the true average, consider altering the histogram template for the corresponding histogram, using a set of bin values that are more appropriate for your data.
Table 2. Statistics or histograms collected when COLLECT AGGREGATE ACTIVITY DATA is set to BASE
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 subclass, this statistic is monitored whenever you define a AGGSQLTEMPSPACE threshold on the service subclass itself, or when you define a similar threshold on any service subclass 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 subclass, 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 subclass 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:
  • For cursors, the execution time is the combined time for the open cursor request, any fetches, and the close cursor request. Time when the cursor is idle is not counted towards the execution time.
  • For routines, the execution time is from the start to the end of the routine invocation. If any cursors are left open by the routine after it ends, the lifetimes of these cursors are not counted towards the routine execution time.
  • For all other activities, the execution time is the difference between the activity lifetime and the time that the activity spends queued.
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.
When you set the value of the COLLECT AGGREGATE ACTIVITY DATA option to EXTENDED for a service subclass, workload or a work class, the following system statistics are collected or histograms are generated for each member for the corresponding service class or work class (through a work action). Use the averages to quickly understand the average rate of arrival of activities (arrival rate is the inverse of inter-arrival time) and the expense of activities (estimated cost). You can also use the averages to tune the histogram templates. That is you can compare a true average with the average computed from a histogram, and if the average from the histogram deviates from the true average, consider altering the histogram template for the corresponding histogram, using a set of bin values that are more appropriate for your data. EXTENDED statistics are useful for more detailed performance modelling. Also see Workload management performance modelling.
Table 3. Statistics or histograms collected when COLLECT AGGREGATE ACTIVITY DATA is set to EXTENDED
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 subclass, 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.
The following table provides a reference for which activity statistics are collected for each Db2 workload management object and includes all aggregate statistics available to you from both table functions and event monitors. Some statistics are always collected for some objects. Other statistics are only collected when a particular COLLECT AGGREGATE option is specified. For aggregate activity statistics, if COLLECT AGGREGATE ACTIVITY DATA EXTENDED is specified, all the BASE aggregate activity statistics are also collected.
Table 4. Aggregate activity statistics collection for Db2 workload management objects
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 subclass
Service superclass N/A N/A
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

When you set the value of the COLLECT AGGREGATE REQUEST DATA option for a service subclass to BASE, the following statistics are maintained for the service subclass.
Table 5. Statistics or histograms collected when COLLECT AGGREGATE REQUEST DATA is set to BASE
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 class 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 class. 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.
The following table provides a reference for which request statistics are collected for each Db2 workload management object and includes all aggregate statistics available to you from both table functions and event monitors. Some statistics are always collected for some objects. Other statistics are only collected when the COLLECT AGGREGATE REQUEST DATA option is specified.
Table 6. Aggregate request statistics collection for Db2 workload management objects
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