DB2 10.5 for Linux, UNIX, and Windows

MON_SAMPLE_SERVICE_CLASS_METRICS - Get sample service class metrics

The MON_SAMPLE_SERVICE_CLASS_METRICS table function reads system metrics for one or more service classes across one or more databases at two points in time: at the time the function is called and after a given amount of time has passed.

The function computes various statistics from these metrics.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Syntax


>>-MON_SAMPLE_SERVICE_CLASS_METRICS--(--hostname--,--db_name--,-->

>--service_superclass_name--,--service_subclass_name--,--------->

>--sample_time--,--member--)-----------------------------------><

The schema is SYSPROC.

Table function parameters

hostname: An input argument of type VARCHAR(255) that specifies a fully qualified host name or an IP address of the node from which to generate the report. If the argument is NULL or an empty string, metrics are taken from all active databases in the instance.
db_name: An input argument of type VARCHAR(128) that specifies a valid database name in the same instance as the currently connected database when calling this function. The database must have a directory entry type of either "Indirect" or "Home", as returned by a LIST DATABASE DIRECTORY command. If the argument is NULL or an empty string, metrics are taken from all databases in the instance.
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, performance metrics are retrieved for all the superclasses in the instance whose database name satisfies the constraint imposed by the db_name parameter.
service_subclass_name: An input argument of type VARCHAR(128) that specifies a valid service subclass name in the currently connected database when calling this function. If the argument is NULL or an empty string, performance metrics are retrieved for all the subclasses in the instance whose database name and service superclass name satisfy the constraints imposed by the db_name and service_superclass_name parameters, respectively.
sample_time: The amount of time the function collects data before computing a result on that data. This value is measured in seconds and must be greater than or equal to 1.
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 database members. If the NULL value is specified, -1 is set implicitly.

Information returned

Table 1. Information returned for MON_SAMPLE_SERVICE_CLASS_METRICS
Column name	Data type	Description or corresponding monitor element
HOSTNAME	VARCHAR(255)	hostname - Host name monitor element
DB_NAME	VARCHAR(128)	db_name - Database name monitor element
SERVICE_SUPERCLASS_NAME	VARCHAR(128)	service_superclass_name - Service superclass name
SERVICE_SUBCLASS_NAME	VARCHAR(128)	service_subclass_name - Service subclass name
SERVICE_CLASS_ID	INTEGER	service_class_id - Service class ID
MEMBER	SMALLINT	member- Database member
UOW_THROUGHPUT	DOUBLE	uow_throughput - Unit of work throughput monitor element
UOW_LIFETIME_AVG	DOUBLE	uow_lifetime_avg - Unit of work lifetime average monitor element
UOW_COMPLETED_TOTAL	BIGINT	uow_completed_total - Total completed units of work monitor element
ACT_THROUGHPUT	DOUBLE	act_throughput - Activity throughput monitor element
ACT_COMPLETED_TOTAL	BIGINT	act_completed_total - Total completed activities
TOTAL_CPU_TIME	BIGINT	total_cpu_time - Total CPU time monitor element
TOTAL_DISP_RUN_QUEUE_TIME	BIGINT	total_disp_run_queue_time - Total dispatcher run queue time monitor element
CPU_SHARES	INTEGER	cpu_shares - WLM dispatcher CPU shares monitor element
CPU_SHARE_TYPE	VARCHAR(4)	cpu_share_type - WLM dispatcher CPU share type monitor element
CPU_LIMIT	SMALLINT	cpu_limit - WLM dispatcher CPU limit monitor element
ESTIMATED_CPU_ENTITLEMENT	DOUBLE	estimated_cpu_entitlement - Estimated CPU entitlement monitor element
CPU_UTILIZATION	DOUBLE	cpu_utilization - CPU utilization monitor element
CPU_VELOCITY	DOUBLE	cpu_velocity - CPU velocity monitor element

Usage notes

The MON_SAMPLE_SERVICE_CLASS_METRICS table function returns one row of data per service class and per member for each database. The table function performs no aggregation across service classes (on a member), or across members (for a service class or more). However, aggregation can be achieved through SQL queries. The input parameters have the effect of being "ANDed" together. Therefore, if you specify conflicting input parameters (for example, a superclass named SUPA, and subclass named SUBB which is not a subclass of SUPA), then no rows are returned.

Example 1

Show the activity throughput and CPU velocity for a 30 second period for each service subclass across all partitions.

SELECT varchar(service_superclass_name,30) AS service_superclass,
       varchar(service_subclass_name,30) AS service_subclass,
       decimal(sum(act_throughput),10,2) AS act_throughput,
       decimal(sum(total_cpu_time) / 
               (sum(total_cpu_time) + 
                sum(total_disp_run_queue_time)),3,2) AS cpu_velocity 
   FROM TABLE(MON_SAMPLE_SERVICE_CLASS_METRICS
      (null, current server, '', '', 30, -2)) AS t 
   WHERE service_subclass_name IS NOT NULL
   GROUP BY service_superclass_name, service_subclass_name 
   ORDER BY service_superclass_name, service_subclass_name

This an example of output from this query.

SERVICE_SUPERCLASS          SERVICE_SUBCLASS        ... 
--------------------------- ----------------------- ... 
SYSDEFAULTUSERCLASS         SYSDEFAULTSUBCLASS      ... 
SYSDEFAULTMAINTENANCECLASS  SYSDEFAULTSUBCLASS      ...  
SYSDEFAULTSYSTEMCLASS       SYSDEFAULTSUBCLASS      ... 

  3 record(s) selected.

Output for query (continued).

... ACT_THROUGHPUT  CPU_VELOCITY 
... --------------- -----------------
...          214.76              0.72
...               0                 0
...               0                 0

Example 2

Show the configured shares, the estimated CPU entitlement, and the actual CPU utilization for a 5 minute period, for each service class across all databases and partitions on the system.

SELECT varchar(db_name,18) AS db_name,
       varchar(service_superclass_name,30) AS service_superclass,
       varchar(service_subclass_name,30) AS service_subclass,
       cpu_shares,
       cpu_limit,
       decimal(estimated_cpu_entitlement, 9, 2) AS estimated_cpu_entitlement,
       decimal( cpu_utilization, 3, 2) AS cpu_utilization       
    FROM TABLE(MON_SAMPLE_SERVICE_CLASS_METRICS
       (null, null, '', '', 300, -2)) AS t 
    ORDER BY db_name, service_superclass_name, service_subclass_name, member

This an example of output from this query.

DB_NAME            SERVICE_SUPERCLASS          ...
------------------ --------------------------- ...
SAMPLE             SYSDEFAULTUSERCLASS         ...
SAMPLE             SYSDEFAULTUSERCLASS         ...
SAMPLE             SYSDEFAULTMAINTENANCECLASS  ...
SAMPLE             SYSDEFAULTMAINTENANCECLASS  ...
SAMPLE             SYSDEFAULTSYSTEMCLASS       ...
SAMPLE             SYSDEFAULTSYSTEMCLASS       ...
OTHER              SYSDEFAULTUSERCLASS         ...
OTHER              SYSDEFAULTUSERCLASS         ...
OTHER              SYSDEFAULTMAINTENANCECLASS  ...
OTHER              SYSDEFAULTMAINTENANCECLASS  ...
OTHER              SYSDEFAULTSYSTEMCLASS       ...
OTHER              SYSDEFAULTSYSTEMCLASS       ...

  12 record(s) selected.

Output for query (continued).

SERVICE_SUBCLASS        CPU_SHARES CPU_LIMIT ...
----------------------- ---------- --------- ...
SYSDEFAULTSUBCLASS            1000         - ...
-                             2000        70 ...
SYSDEFAULTSUBCLASS            1000         - ...
-                             1000         - ...
SYSDEFAULTSUBCLASS               -         - ...
-                                -         - ...
SYSDEFAULTSUBCLASS            1000         - ...
-                             5000        70 ...
SYSDEFAULTSUBCLASS            1000         - ...
-                             2000         - ...
SYSDEFAULTSUBCLASS               -         - ...
-                                -         - ...

Output for query (continued).

ESTIMATED_CPU_ENTITLEMENT CPU_UTILIZATION
------------------------- ---------------
                     0.20            0.16
                     0.20            0.16
                     0.10            0.09
                     0.10            0.09
                        -            0.10
                        -            0.10
                     0.50            0.45
                     0.50            0.45
                     0.20            0.11
                     0.20            0.11
                        -            0.09
                        -            0.09