DB2 Version 9.7 for Linux, UNIX, and Windows

WLM_GET_SERVICE_SUBCLASS_STATS table function - return statistics of service subclasses

Note: This table function has been deprecated and replaced by the WLM_GET_SERVICE_SUBCLASS_STATS_V97 table function - Return statistics of service subclasses.

This function returns basic statistics of one or more service subclasses.

Syntax


>>-WLM_GET_SERVICE_SUBCLASS_STATS--(--service_superclass_name--,-->

>--service_subclass_name--,--dbpartitionnum--)-----------------><

The schema is SYSPROC.

Table function parameters

service_superclass_name: An input argument of type VARCHAR(128) that specifies a valid service superclass name in the same database as the one currently connected to when calling this function. If the argument is null or an empty string, the data is retrieved for all the superclasses in the database.
service_subclass_name: An input argument of type VARCHAR(128) that specifies a valid service subclass name in the same database as the one currently connected to when calling this function. If the argument is null or an empty string, the data is retrieved for all the subclasses in the database.
dbpartitionnum: An input argument of type INTEGER that specifies a valid partition number in the same instance as the currently connected database when calling this function. Specify -1 for the current database partition, or -2 for all database partitions. If the null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the WLM_GET_SERVICE_SUBCLASS_STATS function.

Examples

Example 1: As every activity has to be mapped to a DB2® Service Class before being executed, the global state of the system can be regularly monitored using the service class statistics table functions query all the service classes on all the partitions. Note that passing a null value for an argument indicates that the result should not be restricted by that argument, except for the final argument, dbpartitionnum, where a value of -2 indicates that data from all database partitions is returned. This example returns service class statistics, such as average activity lifetime and standard deviation in seconds:

 SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
       SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
       SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
       CAST(COORD_ACT_LIFETIME_AVG / 1000 AS DECIMAL(9,3))
         AS AVGLIFETIME,
       CAST(COORD_ACT_LIFETIME_STDDEV / 1000 AS DECIMAL(9,3))
         AS STDDEVLIFETIME,
       SUBSTR(CAST(LAST_RESET AS VARCHAR(30)),1,16) AS LAST_RESET
  FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS(CAST(NULL AS VARCHAR(128)),
       CAST(NULL AS VARCHAR(128)), -2)) AS SCSTATS
  ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, PART

This is an example of output from this query.

SUPERCLASS_NAME     SUBCLASS_NAME      PART ...
------------------- ------------------ ---- ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0    ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1    ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2    ...
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3    ...

Output from this query (continued).

... AVGLIFETIME STDDEVLIFETIME LAST_RESET
... ----------- -------------- ----------------
...     691.242         34.322 2006-07-24-11.44
...     644.740         22.124 2006-07-24-11.44
...     612.431         43.347 2006-07-24-11.44
...     593.451         28.329 2006-07-24-11.44

Example 2: The same table function also gives the highest value for average concurrency of coordinator activities running in the service class for each partition.

  SELECT SUBSTR(SERVICE_SUPERCLASS_NAME,1,19) AS SUPERCLASS_NAME,
        SUBSTR(SERVICE_SUBCLASS_NAME,1,18) AS SUBCLASS_NAME,
        SUBSTR(CHAR(DBPARTITIONNUM),1,4) AS PART,
        CONCURRENT_ACT_TOP AS ACTTOP
  FROM TABLE(WLM_GET_SERVICE_SUBCLASS_STATS(CAST(NULL AS VARCHAR(128)),
        CAST(NULL AS VARCHAR(128)), -2)) AS SCSTATS
  ORDER BY SUPERCLASS_NAME, SUBCLASS_NAME, PART

This is an example of output from this query.

SUPERCLASS_NAME    SUBCLASS_NAME      PART  ACTTOP   
------------------- ------------------ ---- --------- 
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 0          10
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 1           0
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 2           0
SYSDEFAULTUSERCLASS SYSDEFAULTSUBCLASS 3           0

The output of this table function gives you a good high-level view of the "load" on each partition for a specific database. The output comes from checking the average execution times and numbers of activities. Any significant variations of the high level values returned by these table functions may indicate a change in the load on the system.

Usage notes

Some statistics are returned only if the COLLECT AGGREGATE ACTIVITY DATA and COLLECT AGGREGATE REQUEST DATA settings for the corresponding service subclass are set to a value other than "NONE".

The WLM_GET_SERVICE_SUBCLASS_STATS table function returns one row of data per service subclass and per partition. There is no aggregation across service classes (on a partition) or across partitions (for a service class or more). However, aggregation can be achieved through SQL queries, as shown in the preceding examples.

The parameters have the effect of being logically united as "AND" clauses. That is, if you specify conflicting input parameters such as a superclass named SUPA and a subclass named SUBB, such that SUBB is not a subclass of SUPA, no rows would be returned.

Information returned

Table 1. Information returned for WLM_GET_SERVICE_SUBCLASS_STATS
Column Name	Data Type	Description
SERVICE_SUPERCLASS_NAME	VARCHAR(128)	service_superclass_name - Service superclass name monitor element
SERVICE_SUBCLASS_NAME	VARCHAR(128)	service_subclass_name - Service subclass name monitor element
DBPARTITIONNUM	SMALLINT	Partition number from which this record was collected.
LAST_RESET	TIMESTAMP	last_reset - Last Reset Timestamp monitor element
COORD_ACT_COMPLETED_TOTAL	BIGINT	The total number of coordinator activities that users have submitted since the last reset and completed successfully. This count is updated as each activity completes. If you remap an activity to a different service subclass, then that activity counts only toward the total of the subclass in which it completes.
COORD_ACT_ABORTED_TOTAL	BIGINT	The total number of coordinator activities that users have submitted since the last reset and completed with errors. This count is updated as each activity aborts. If you remap an activity to a different service subclass, then that activity counts only toward the total of the subclass in which it aborts.
COORD_ACT_REJECTED_TOTAL	BIGINT	coord_act_rejected_total - Coordinator activities rejected total monitor element
CONCURRENT_ACT_TOP	INTEGER	concurrent_act_top - Concurrent activity top monitor element
COORD_ACT_LIFETIME_TOP	BIGINT	High watermark for coordinator activity lifetime, counted at all nesting levels. Null when COLLECT AGGREGATE ACTIVITY DATA of service class is NONE. Units are milliseconds. To use this statistic effectively when the service class includes subclasses that are remapped, you must aggregate the COORD_ACT_LIFETIME_TOP high watermark of the service subclass with that of other subclasses affected by the same remapping threshold or thresholds. You must aggregate these values because an activity can complete after it has been remapped to a different service subclass, but the time the activity spends in other service subclasses before being remapped is counted only toward the service class in which it completes.
COORD_ACT_LIFETIME_AVG	DOUBLE	Arithmetic mean of lifetime for coordinator activities at nesting level 0 associated with this service subclass since the last reset. If the internally tracked average has overflowed, the value -2 is returned. Null when COLLECT AGGREGATE ACTIVITY DATA of service class is NONE. Units are milliseconds. The COORD_ACT_LIFETIME_AVG of a service subclass is unaffected by activities that pass through the subclass but are remapped to a different subclass before they complete.
COORD_ACT_LIFETIME_STDDEV	DOUBLE	Standard deviation of lifetime for coordinator activities at nesting level 0 associated with this service subclass since the last reset. Null when COLLECT AGGREGATE ACTIVITY DATA of service class is NONE. Units are milliseconds. This standard deviation is computed from the coordinator activity lifetime histogram and may be inaccurate if the histogram has not been properly sized to fit the data. The value of -1 will be returned if any values fall into the last histogram bin. The COORD_ACT_LIFETIME_STDDEV of a service subclass is unaffected by activities that pass through the service subclass but are remapped to a different subclass before they complete.
COORD_ACT_EXEC_TIME_AVG	DOUBLE	Arithmetic mean of the execution times for coordinator activities at nesting level 0 associated with this service subclass since the last reset. If the internally tracked average has overflowed, the value -2 is returned. Null when COLLECT AGGREGATE ACTIVITY DATA of service class is NONE. Units are milliseconds. The execution time average of a service subclass is unaffected by activities that pass through the subclass but are remapped to a different subclass before they complete.
COORD_ACT_EXEC_TIME_STDDEV	DOUBLE	Standard deviation of the execution times for coordinator activities at nesting level 0 associated with this service subclass since the last reset. Units are milliseconds. This standard deviation is computed from the coordinator activity executetime histogram and might be inaccurate if the histogram has not been properly sized to fit the data. The value of -1 will be returned if any values fall into the last histogram bin. The execution time standard deviation of a service subclass is unaffected by activities that pass through the subclass but are remapped to a different subclass before they complete.
COORD_ACT_QUEUE_TIME_AVG	DOUBLE	Arithmetic mean of the queue time for coordinator activities at nesting level 0 associated with this service subclass since the last reset. If the internally tracked average has overflowed, the value -2 is returned. Null when COLLECT AGGREGATE ACTIVITY DATA of service class is NONE. Units are milliseconds. The queue time average is counted only toward the service subclass in which the activity was queued.
COORD_ACT_QUEUE_TIME_STDDEV	DOUBLE	Standard deviation of the queue time for coordinator activities at nesting level 0 associated with this service subclass since the last reset. Null when COLLECT AGGREGATE ACTIVITY DATA of service class is NONE. Units are milliseconds. This standard deviation is computed from the coordinator activity queuetime histogram and may be inaccurate if the histogram has not been properly sized to fit the data. The value of -1 will be returned if any values fall into the last histogram bin. The queue time standard deviation is counted only toward the service subclass in which the activity was queued.
NUM_REQUESTS_ACTIVE	BIGINT	The number of requests that are executing in the service subclass at the time this table function is executed.
NUM_REQUESTS_TOTAL	BIGINT	The number of requests to finish executing in this service subclass since the last reset. This applies to any request regardless of its membership in an activity. If COLLECT AGGREGATE REQUEST DATA on this service subclass is set to NONE, the value of this column is NULL. The NUM_REQUESTS_TOTAL of a service subclass is unaffected by requests that pass through the service subclass, but do not complete in it.
REQUEST_EXEC_TIME_AVG	DOUBLE	Arithmetic mean of the execution times for requests associated with this service subclass since the last reset. Units are milliseconds. If the internally tracked average has overflowed, the value -2 is returned. If COLLECT AGGREGATE REQUEST DATA on this service class is set to NONE, the value of this column is NULL. The execution time average of a service subclass is unaffected by requests that pass through the subclass, but do not complete in it.
REQUEST_EXEC_TIME_STDDEV	DOUBLE	Standard deviation of the execution times for requests associated with this service subclass since the last reset. Units are milliseconds. If COLLECT AGGREGATE REQUEST DATA on this service class is set to NONE, the value of this column is NULL. This standard deviation is computed from the request executetime histogram and may be inaccurate if the histogram has not been properly sized to fit the data. The value of -1 will be returned if any values fall into the last histogram bin. The execution time standard deviation of a service subclass is unaffected by requests that pass through the subclass, but do not complete in it.
REQUEST_EXEC_TIME_TOTAL	BIGINT	Sum of the execution times for requests associated with this service subclass since the last reset. Units are milliseconds. If COLLECT AGGREGATE REQUEST DATA on this service class is set to NONE, the value of this column is NULL. This total is computed from the request execution time histogram and may be inaccurate if the histogram has not been properly sized to fit the data. The value of -1 will be returned if any values fall into the last histogram bin. The execution time total of a service subclass is unaffected by requests that pass through the subclass, but do not complete in it.