DB2 Version 9.7 for Linux, UNIX, and Windows

MON_SERVICE_SUBCLASS_SUMMARY - Retrieve metrics for all service subclasses

The MON_SERVICE_SUBCLASS_SUMMARY administrative view returns key metrics for all service subclasses in the currently connected database. It is designed to help monitor the system in a high-level manner, showing work executed per service class.

Note: If your database was created in Version 9.7 before Fix Pack 1, to run this routine you must have already run the db2updv97 command. If your database was created before Version 9.7, it is not necessary to run the db2updv97 command (because the catalog update is automatically taken care of by the database migration). If you downgrade to Version 9.7, this routine will no longer work.

The metrics returned represent the accumulation of all metrics for requests that have executed under the indicated service subclass across all members of the database.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the MON_SERVICE_SUBCLASS_SUMMARY administrative view
CONTROL privilege on the MON_SERVICE_SUBCLASS_SUMMARY administrative view
DATAACCESS authority

Information returned

Table 1. Information returned by the MON_SERVICE_SUBCLASS_SUMMARY administrative view
Column name	Data type	Description or 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
TOTAL_APP_COMMITS	BIGINT	total_app_commits - Total application commits monitor elements
TOTAL_APP_ROLLBACKS	BIGINT	total_app_rollbacks - Total application rollbacks monitor element
ACT_COMPLETED_TOTAL	BIGINT	act_completed_total - Total completed activities monitor element
APP_RQSTS_COMPLETED _TOTAL	BIGINT	Total number of external (application) requests that completed successfully across all members of the database for the specified service subclass
AVG_RQST_CPU_TIME	BIGINT	Average amount of CPU time, in microseconds, used by all external requests that completed successfully. It represents the total of both user and system CPU time.
ROUTINE_TIME_ RQST_PERCENT	DECIMAL(5,2)	The percentage of time the database server spent working on requests that was spent executing user routines.
RQST_WAIT_ TIME_PERCENT	DECIMAL(5,2)	The percentage of the time spent working on requests that was spent waiting within the DB2® database server.
ACT_WAIT_TIME_ PERCENT	DECIMAL(5,2)	The percentage of the time spent executing activities that was spent waiting within the DB2 database server.
IO_WAIT_TIME_PERCENT	DECIMAL(5,2)	The percentage of the time spent waiting within the DB2 database server that was due to I/O operations. This includes time spent performing direct reads or direct writes, and time spent reading data and index pages from the table space to the bufferpool or writing them back to disk.
LOCK_WAIT_TIME_PERCENT	DECIMAL(5,2)	The percentage of time spent waiting within the DB2 database server that was spent waiting on locks.
AGENT_WAIT_TIME_ PERCENT	DECIMAL(5,2)	The percentage of time spent waiting within the DB2 database server that was spent by an application queued to wait for an agent under concentrator configurations.
NETWORK_WAIT_ TIME_PERCENT	DECIMAL(5,2)	The percentage of time spent waiting within the DB2 database server that was spent on client-server communications. This includes time spent sending and receiving data over TCP/IP or using the IPC protocol.
SECTION_PROC_ TIME_PERCENT	DECIMAL(5,2)	The percentage of time the database server spent actively working on requests that was spent executing sections. This includes the time spent performing sorts.
SECTION_SORT_ PROC_TIME_PERCENT	DECIMAL(5,2)	The percentage of time the database server spent actively working on requests that was spent performing sorts while executing sections.
COMPILE_PROC_ TIME_PERCENT	DECIMAL(5,2)	The percentage of time the database server spent actively working on requests that was spent compiling an SQL statement. This includes explicit and implicit compile times.
TRANSACT_END_PROC _TIME_PERCENT	DECIMAL(5,2)	The percentage of time the database server spent actively working on requests that was spent performing commit processing or rolling back transactions.
UTILS_PROC_ TIME_PERCENT	DECIMAL(5,2)	The percentage of time the database server spent actively working on requests that was spent running utilities. This includes performing runstats, reorganization, and load operations.
AVG_LOCK_WAITS _PER_ACT	BIGINT	The average number of times that applications or connections waited for locks per coordinator activities (successful and aborted).
AVG_LOCK_TIMEOUTS _PER_ACT	BIGINT	The average number of times that a request to lock an object timed out per coordinator activities (successful and aborted).
AVG_DEADLOCKS_ PER_ACT	BIGINT	The average number of deadlocks per coordinator activities (successful and aborted).
AVG_LOCK_ESCALS _PER_ACT	BIGINT	The average number of times that locks have been escalated from several row locks to a table lock per coordinator activities (successful and aborted).
ROWS_READ_PER_ ROWS_RETURNED	BIGINT	The average number of rows read from the table per rows returned to the application.
TOTAL_BP_HIT_ RATIO_PERCENT	DECIMAL(5,2)	The percentage of time that the database manager did not need to load a page from disk to service a data or index page request, including requests for XML storage objects (XDAs).