DB2 Version 9.7 for Linux, UNIX, and Windows

MON_GET_SERVICE_SUBCLASS table function - Get service subclass metrics

The MON_GET_SERVICE_SUBCLASS table function returns metrics for one or more service subclasses.

Syntax


>>-MON_GET_SERVICE_SUBCLASS--(--service_superclass_name--,------>

>--service_subclass_name--,--member--)-------------------------><

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 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.
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, metrics are retrieved for all the subclasses 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 database members. If the null value is specified, -1 is set implicitly.

Authorization

EXECUTE privilege on the MON_GET_SERVICE_SUBCLASS function.

Example

Display the total CPU time used and total number of requests processed for each service class, ordered by CPU usage.

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

The following example is a sample output from this query.

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

  3 record(s) selected.

Output for query (continued).

... TOTAL_CPU            TOTAL_RQSTS  
... -------------------- -------------------- 
...               967673                  100  
.. .                   0                    0  
...                    0                    0

Usage notes

The metrics returned by the MON_GET_SERVICE_SUBCLASS table function represent the accumulation of all metrics for requests that have executed under the indicated service subclass. 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_SUBCLASS 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_SUBCLASS table function returns one row of data per service subclass 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 as shown in the example. The input parameters have the effect of being ANDed together. Therefore, if you specify conflicting input parameters (for example, a superclass name SUPA and subclass name SUBB that is not a subclass of SUPA), no rows are returned.

Tip: A request might execute in more than one service subclass. For example, this situation might occur if a request is mapped from one service subclass to another by using a Workload Manager (WLM) threshold with a REMAP ACTIVITY action. Although the time spent metrics are updated for each service subclass under which the request executes, the request counters are incremented for the service subclass where the request completed. Therefore, you should not analyze the averages of request times for a single subclass. All subclasses to which an activity can be mapped must be analyzed in conjunction with one another. For example, if a threshold exists that can map activities from service subclass A to service subclass B, then when you compute averages of requests, you should aggregate the counters and metrics for service subclasses A and B, and compute the averages using the aggregates.

Information returned

Table 1. Information returned for MON_GET_SERVICE_SUBCLASS
Column Name	Data Type	Description or corresponding 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
ACT_ABORTED_TOTAL	BIGINT	act_aborted_total - Total aborted activities
ACT_COMPLETED_TOTAL	BIGINT	act_completed_total - Total completed activities
ACT_REJECTED_TOTAL	BIGINT	act_rejected_total - Total rejected activities
AGENT_WAIT_TIME	BIGINT	agent_wait_time - Agent wait time
AGENT_WAITS_TOTAL	BIGINT	agent_waits_total - Total agent waits
POOL_DATA_L_READS	BIGINT	pool_data_l_reads - Buffer pool data logical reads
POOL_INDEX_L_READS	BIGINT	pool_index_l_reads - Buffer pool index logical reads
POOL_TEMP_DATA_L_READS	BIGINT	pool_temp_data_l_reads - Buffer pool temporary data logical reads
POOL_TEMP_INDEX_L_READS	BIGINT	pool_temp_index_l_reads - Buffer pool temporary index logical reads
POOL_TEMP_XDA_L_READS	BIGINT	pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads
POOL_XDA_L_READS	BIGINT	pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads
POOL_DATA_P_READS	BIGINT	pool_data_p_reads - Buffer pool data physical reads
POOL_INDEX_P_READS	BIGINT	pool_index_p_reads - Buffer pool index physical reads
POOL_TEMP_DATA_P_READS	BIGINT	pool_temp_data_p_reads - Buffer pool temporary data physical reads
POOL_TEMP_INDEX_P_READS	BIGINT	pool_temp_index_p_reads - Buffer pool temporary index physical reads
POOL_TEMP_XDA_P_READS	BIGINT	pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads
POOL_XDA_P_READS	BIGINT	pool_xda_p_reads - Buffer pool XDA data physical reads
POOL_DATA_WRITES	BIGINT	pool_data_writes - Buffer pool data writes
POOL_INDEX_WRITES	BIGINT	pool_index_writes - Buffer pool index writes
POOL_XDA_WRITES	BIGINT	pool_xda_writes - Buffer pool XDA data writes
POOL_READ_TIME	BIGINT	pool_read_time - Total buffer pool physical read time
POOL_WRITE_TIME	BIGINT	pool_write_time - Total buffer pool physical write time
CLIENT_IDLE_WAIT_TIME	BIGINT	client_idle_wait_time - Client idle wait time
DEADLOCKS	BIGINT	deadlocks - Deadlocks detected
DIRECT_READS	BIGINT	direct_reads - Direct reads from database
DIRECT_READ_TIME	BIGINT	direct_read_time - Direct read time
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database
DIRECT_WRITE_TIME	BIGINT	direct_write_time - Direct write time
DIRECT_READ_REQS	BIGINT	direct_read_reqs - Direct read requests
DIRECT_WRITE_REQS	BIGINT	direct_write_reqs - Direct write requests
FCM_RECV_VOLUME	BIGINT	fcm_recv_volume - FCM recv volume
FCM_RECVS_TOTAL	BIGINT	fcm_recvs_total - FCM recvs total
FCM_SEND_VOLUME	BIGINT	fcm_send_volume - FCM send volume
FCM_SENDS_TOTAL	BIGINT	fcm_sends_total - FCM sends total
FCM_RECV_WAIT_TIME	BIGINT	fcm_recv_wait_time - FCM recv wait time
FCM_SEND_WAIT_TIME	BIGINT	fcm_send_wait_time - FCM send wait time
IPC_RECV_VOLUME	BIGINT	ipc_recv_volume - Interprocess communication recv volume
IPC_RECV_WAIT_TIME	BIGINT	ipc_recv_wait_time - Interprocess communication recv wait time
IPC_RECVS_TOTAL	BIGINT	ipc_recvs_total - Interprocess communication recvs total
IPC_SEND_VOLUME	BIGINT	ipc_send_volume - Interprocess communication send volume
IPC_SEND_WAIT_TIME	BIGINT	ipc_send_wait_time - Interprocess communication send wait time
IPC_SENDS_TOTAL	BIGINT	ipc_sends_total - Interprocess communication send total
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks
LOCK_WAITS	BIGINT	lock_waits - Lock waits
LOG_BUFFER_WAIT_TIME	BIGINT	log_buffer_wait_time - Log buffer wait time
NUM_LOG_BUFFER_FULL	BIGINT	num_log_buffer_full - Number of full log buffers
LOG_DISK_WAIT_TIME	BIGINT	log_disk_wait_time - Log disk wait time
LOG_DISK_WAITS_TOTAL	BIGINT	log_disk_waits_total - Log disk waits total
RQSTS_COMPLETED_TOTAL	BIGINT	rqsts_completed_total - Total requests completed
ROWS_MODIFIED	BIGINT	rows_modified - Rows modified
ROWS_READ	BIGINT	rows_read - Rows read
ROWS_RETURNED	BIGINT	rows_returned - Rows returned
TCPIP_RECV_VOLUME	BIGINT	tcpip_recv_volume - TCP/IP received volume
TCPIP_SEND_VOLUME	BIGINT	tcpip_send_volume - TCP/IP send volume
TCPIP_RECV_WAIT_TIME	BIGINT	tcpip_recv_wait_time - TCP/IP recv wait time
TCPIP_RECVS_TOTAL	BIGINT	tcpip_recvs_total - TCP/IP recvs total
TCPIP_SEND_WAIT_TIME	BIGINT	tcpip_send_wait_time - TCP/IP send wait time
TCPIP_SENDS_TOTAL	BIGINT	tcpip_sends_total - TCP/IP sends total
TOTAL_APP_RQST_TIME	BIGINT	total_app_rqst_time - Total application request time
TOTAL_RQST_TIME	BIGINT	total_rqst_time - Total request time
WLM_QUEUE_TIME_TOTAL	BIGINT	wlm_queue_time_total - Workload manager total queue time
WLM_QUEUE_ASSIGNMENTS _TOTAL	BIGINT	wlm_queue_assignments_total - Workload manager total queue assignments
TOTAL_RQST_MAPPED_IN	BIGINT	total_rqst_mapped_in - Total request mapped-in
TOTAL_RQST_MAPPED_OUT	BIGINT	total_rqst_mapped_out - Total request mapped-out
TOTAL_CPU_TIME	BIGINT	total_cpu_time - Total CPU time
TOTAL_WAIT_TIME	BIGINT	total_wait_time - Total wait time
APP_RQSTS_COMPLETED_TOTAL	BIGINT	app_rqsts_completed_total - Total application requests completed
TOTAL_SECTION_SORT_TIME	BIGINT	total_section_sort_time - Total section sort time
TOTAL_SECTION_SORT_PROC_TIME	BIGINT	total_section_sort_proc_time - Total section sort processing time
TOTAL_SECTION_SORTS	BIGINT	total_section_sorts - Total section sorts
TOTAL_SORTS	BIGINT	total_sorts - Total Sorts
POST_THRESHOLD_SORTS	BIGINT	post_threshold_sorts - Post threshold sorts
POST_SHRTHRESHOLD_SORTS	BIGINT	post_shrthreshold_sorts - Post shared threshold sorts
SORT_OVERFLOWS	BIGINT	sort_overflows - Sort overflows
TOTAL_COMPILE_TIME	BIGINT	total_compile_time - Total compile time
TOTAL_COMPILE_PROC_TIME	BIGINT	total_compile_proc_time - Total compile processing time
TOTAL_COMPILATIONS	BIGINT	total_compilations - Total compilations
TOTAL_IMPLICIT_COMPILE_TIME	BIGINT	total_implicit_compile_time - Total implicit compile time
TOTAL_IMPLICIT_COMPILE_PROC_TIME	BIGINT	total_implicit_compile_proc_time - Total implicit compile processing time
TOTAL_IMPLICIT_COMPILATIONS	BIGINT	total_implicit_compilations - Total implicit complications
TOTAL_SECTION_TIME	BIGINT	total_section_time - Total section time
TOTAL_SECTION_PROC_TIME	BIGINT	total_section_proc_time - Total section processing time
TOTAL_APP_SECTION_EXECUTIONS	BIGINT	total_app_section_executions - Total section executions
TOTAL_ACT_TIME	BIGINT	total_act_time - Total activity time
TOTAL_ACT_WAIT_TIME	BIGINT	total_act_wait_time - Total activity wait time
ACT_RQSTS_TOTAL	BIGINT	act_rqsts_total - Total activity requests
TOTAL_ROUTINE_TIME	BIGINT	total_routine_time - Total routine time
TOTAL_ROUTINE_INVOCATIONS	BIGINT	total_routine_invocations - Total routine invocations
TOTAL_COMMIT_TIME	BIGINT	total_commit_time - Total commit time
TOTAL_COMMIT_PROC_TIME	BIGINT	total_commit_proc_time - Total commits processing time
TOTAL_APP_COMMITS	BIGINT	total_app_commits - Total application commits
INT_COMMITS	BIGINT	int_commits - Internal commits
TOTAL_ROLLBACK_TIME	BIGINT	total_rollback_time - Total rollback time
TOTAL_ROLLBACK_PROC_TIME	BIGINT	total_rollback_proc_time - Total rollback processing time
TOTAL_APP_ROLLBACKS	BIGINT	total_app_rollbacks - Total application rollbacks
INT_ROLLBACKS	BIGINT	int_rollbacks - Internal rollbacks
TOTAL_RUNSTATS_TIME	BIGINT	total_runstats_time - Total runtime statistics
TOTAL_RUNSTATS_PROC_TIME	BIGINT	total_runstats_proc_time - Total runtime statistics processing time
TOTAL_RUNSTATS	BIGINT	total_runstats - Total runtime statistics
TOTAL_REORG_TIME	BIGINT	total_reorg_time - Total reorganization time
TOTAL_REORG_PROC_TIME	BIGINT	total_reorg_proc_time - Total reorganization processing time
TOTAL_REORGS	BIGINT	total_reorgs - Total reorganizations
TOTAL_LOAD_TIME	BIGINT	total_load_time - Total load time
TOTAL_LOAD_PROC_TIME	BIGINT	total_load_proc_time - Total load processing time
TOTAL_LOADS	BIGINT	total_loads - Total loads
CAT_CACHE_INSERTS	BIGINT	cat_cache_inserts - Catalog cache inserts
CAT_CACHE_LOOKUPS	BIGINT	cat_cache_lookups - Catalog cache lookups
PKG_CACHE_INSERTS	BIGINT	pkg_cache_inserts - Package cache inserts
PKG_CACHE_LOOKUPS	BIGINT	pkg_cache_lookups - Package cache lookups
THRESH_VIOLATIONS	BIGINT	thresh_violations - Number of threshold violations
NUM_LW_THRESH_EXCEEDED	BIGINT	num_lw_thresh_exceeded - Number of thresholds exceeded
IDA_SEND_WAIT_TIME	BIGINT	ida_send_wait_time - Time spent waiting to send data
IDA_SENDS_TOTAL	BIGINT	ida_sends_total - Number of times data sent
IDA_SEND_VOLUME	BIGINT	ida_send_volume - Total data volume sent
IDA_RECV_WAIT_TIME	BIGINT	ida_recv_wait_time - Time spent waiting to receive data
IDA_RECVS_TOTAL	BIGINT	ida_recvs_total - Number of times data received
IDA_RECV_VOLUME	BIGINT	ida_recv_volume - Total data volume received
ADDITIONAL_DETAILS	BLOB(100K)	Reserved for future use