MON_GET_SERVICE_SUPERCLASS_DETAILS table function - Get service superclass metrics as an XML document

Authorization

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine parameters

service_superclass_name

An input argument of type VARCHAR(128) that specifies a valid service superclass name in the currently connected database when calling this function. If the argument is null or an empty string, metrics are retrieved for all the superclasses in the database.

member

An input argument of type INTEGER that specifies a valid member in the same instance as the currently connected database when calling this function. Specify -1 for the current database member, or -2 for all active database members. If the null value is specified, -1 is set implicitly.

Information returned

<db2_service_superclass xmlns="http://www.ibm.com/xmlns/prod/db2/mon" release="90700000">
   <service_superclass_name>SYSDEFAULTSYSTEMCLASS</service_superclass_name>
   <service_subclass_name>SYSDEFAULTSUBCLASS</service_subclass_name>
   <service_subclass_id>11</service_subclass_id>
   <member>0</member>
   <system_metrics release="90700000">
   <act_aborted_total>5</act_aborted_total>
    ...
   <wlm_queue_assignments_total>3</wlm_queue_assignments_total>
   </system_metrics>
</db2_service_superclass>

Usage notes

Metrics are rolled up to a service class on unit of work boundaries, and periodically during the execution of requests. Therefore, the values reported by this table function reflect the current state of the system at the time of the most recent rollup. Metrics are strictly increasing in value. To determine the value of a given metric for an interval of time, use the MON_GET_SERVICE_SUPERCLASS_DETAILS table function to query the metric at the start and end of the interval, and compute the difference.

Request metrics are controlled through the COLLECT REQUEST METRICS clause on service superclasses, and the mon_req_metrics database configuration parameter at the database level. Metrics are only collected for a request if the request is processed by an agent in a service subclass whose parent service superclass has request metrics enabled, or if request metrics collection is enabled for the entire database. By default request metrics are enabled at the database level. If request metrics are disabled at the database level and for a service superclass, the metrics reported for each connection mapped to that service superclass stop increasing (or remain at 0 if request metrics were disabled at database activation time).

The MON_GET_SERVICE_SUPERCLASS_DETAILS table function returns one row of data per service superclass and per member. No aggregation across service classes (on a member), or across members (for a service class or more), is performed. However, aggregation can be achieved through SQL queries (see the example). The input parameters have the effect of being ANDed together.

Note that the metrics from background EDUs such as prefetchers and page cleaners are not rolled up to service classes.

The schema for the XML document that is returned in the DETAILS column is available in the file sqllib/misc/DB2MonRoutines.xsd. Further details can be found in the file sqllib/misc/DB2MonCommon.xsd.

Example

Display the total CPU time used and total number of requests processed for each service superclass, ordered by CPU usage in relational format (using XMLTABLE).

SELECT varchar(service_superclass_name,30) as service_superclass,                  
                 sum(total_cpu_time) as total_cpu,                   
                 sum(app_rqsts_completed_total) as total_rqsts  
FROM TABLE(MON_GET_SERVICE_SUPERCLASS('',-2)) 
AS t 
GROUP BY service_superclass_name
ORDER BY total_cpu desc

The following is an example of output from this query.

SERVICE_SUPERCLASS             TOTAL_CPU            TOTAL_RQSTS         
------------------------------ -------------------- --------------------
SYSDEFAULTUSERCLASS                         2428188                   26
SYSDEFAULTMAINTENANCECLASS                        0                    0
SYSDEFAULTSYSTEMCLASS                             0                    0

  3 record(s) selected.