DB2 10.5 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.

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_GET_SERVICE_SUBCLASS--(--service_superclass_name--,------>

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

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.
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 active database members. If the null value is specified, -1 is set implicitly.

Information returned

Note: In the rows returned by this function that describe the default subclass SYSDEFAULTSUBCLASS under the superclass SYSDEFAULTSYSTEMCLASS, all columns that report metrics have a value of 0.

Table 1. Information returned for MON_GET_SERVICE_SUBCLASS
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
SERVICE_CLASS_ID	INTEGER	service_class_id - Service class ID monitor element
MEMBER	SMALLINT	member - Database member monitor element
ACT_ABORTED_TOTAL	BIGINT	act_aborted_total - Total aborted activities monitor element
ACT_COMPLETED_TOTAL	BIGINT	act_completed_total - Total completed activities monitor element
ACT_REJECTED_TOTAL	BIGINT	act_rejected_total - Total rejected activities monitor element
AGENT_WAIT_TIME	BIGINT	agent_wait_time - Agent wait time monitor element
AGENT_WAITS_TOTAL	BIGINT	agent_waits_total - Total agent waits monitor element
POOL_DATA_L_READS	BIGINT	pool_data_l_reads - Buffer pool data logical reads monitor element
POOL_INDEX_L_READS	BIGINT	pool_index_l_reads - Buffer pool index logical reads monitor element
POOL_TEMP_DATA_L_READS	BIGINT	pool_temp_data_l_reads - Buffer pool temporary data logical reads monitor element
POOL_TEMP_INDEX_L_READS	BIGINT	pool_temp_index_l_reads - Buffer pool temporary index logical reads monitor element
POOL_TEMP_XDA_L_READS	BIGINT	pool_temp_xda_l_reads - Buffer pool temporary XDA data logical reads monitor element
POOL_XDA_L_READS	BIGINT	pool_xda_l_reads - Buffer pool XDA data logical reads monitor element
POOL_DATA_P_READS	BIGINT	pool_data_p_reads - Buffer pool data physical reads monitor element
POOL_INDEX_P_READS	BIGINT	pool_index_p_reads - Buffer pool index physical reads monitor element
POOL_TEMP_DATA_P_READS	BIGINT	pool_temp_data_p_reads - Buffer pool temporary data physical reads monitor element
POOL_TEMP_INDEX_P_READS	BIGINT	pool_temp_index_p_reads - Buffer pool temporary index physical reads monitor element
POOL_TEMP_XDA_P_READS	BIGINT	pool_temp_xda_p_reads - Buffer pool temporary XDA data physical reads monitor element
POOL_XDA_P_READS	BIGINT	pool_xda_p_reads - Buffer pool XDA data physical reads monitor element
POOL_DATA_WRITES	BIGINT	pool_data_writes - Buffer pool data writes monitor element
POOL_INDEX_WRITES	BIGINT	pool_index_writes - Buffer pool index writes monitor element
POOL_XDA_WRITES	BIGINT	pool_xda_writes - Buffer pool XDA data writes monitor element
POOL_READ_TIME	BIGINT	pool_read_time - Total buffer pool physical read time monitor element
POOL_WRITE_TIME	BIGINT	pool_write_time - Total buffer pool physical write time monitor element
CLIENT_IDLE_WAIT_TIME	BIGINT	client_idle_wait_time - Client idle wait time monitor element
DEADLOCKS	BIGINT	deadlocks - Deadlocks detected monitor element
DIRECT_READS	BIGINT	direct_reads - Direct reads from database monitor element
DIRECT_READ_TIME	BIGINT	direct_read_time - Direct read time monitor element
DIRECT_WRITES	BIGINT	direct_writes - Direct writes to database monitor element
DIRECT_WRITE_TIME	BIGINT	direct_write_time - Direct write time monitor element
DIRECT_READ_REQS	BIGINT	direct_read_reqs - Direct read requests monitor element
DIRECT_WRITE_REQS	BIGINT	direct_write_reqs - Direct write requests monitor element
FCM_RECV_VOLUME	BIGINT	fcm_recv_volume - FCM received volume monitor element
FCM_RECVS_TOTAL	BIGINT	fcm_recvs_total - FCM receives total monitor element
FCM_SEND_VOLUME	BIGINT	fcm_send_volume - FCM send volume monitor element
FCM_SENDS_TOTAL	BIGINT	fcm_sends_total - FCM sends total monitor element
FCM_RECV_WAIT_TIME	BIGINT	fcm_recv_wait_time - FCM received wait time monitor element
FCM_SEND_WAIT_TIME	BIGINT	fcm_send_wait_time - FCM send wait time monitor element
IPC_RECV_VOLUME	BIGINT	ipc_recv_volume - Interprocess communication received volume monitor element
IPC_RECV_WAIT_TIME	BIGINT	ipc_recv_wait_time - Interprocess communication received wait time monitor element
IPC_RECVS_TOTAL	BIGINT	ipc_recvs_total - Interprocess communication receives total monitor element
IPC_SEND_VOLUME	BIGINT	ipc_send_volume - Interprocess communication send volume monitor element
IPC_SEND_WAIT_TIME	BIGINT	ipc_send_wait_time - Interprocess communication send wait time monitor element
IPC_SENDS_TOTAL	BIGINT	ipc_sends_total - Interprocess communication send total monitor element
LOCK_ESCALS	BIGINT	lock_escals - Number of lock escalations monitor element
LOCK_TIMEOUTS	BIGINT	lock_timeouts - Number of lock timeouts monitor element
LOCK_WAIT_TIME	BIGINT	lock_wait_time - Time waited on locks monitor element
LOCK_WAITS	BIGINT	lock_waits - Lock waits monitor element
LOG_BUFFER_WAIT_TIME	BIGINT	log_buffer_wait_time - Log buffer wait time monitor element
NUM_LOG_BUFFER_FULL	BIGINT	num_log_buffer_full - Number of times full log buffer caused agents to wait monitor element
LOG_DISK_WAIT_TIME	BIGINT	log_disk_wait_time - Log disk wait time monitor element
LOG_DISK_WAITS_TOTAL	BIGINT	log_disk_waits_total - Total log disk waits monitor element
RQSTS_COMPLETED_TOTAL	BIGINT	rqsts_completed_total - Total requests completed monitor element
ROWS_MODIFIED	BIGINT	rows_modified - Rows modified monitor element
ROWS_READ	BIGINT	rows_read - Rows read monitor element
ROWS_RETURNED	BIGINT	rows_returned - Rows returned monitor element
TCPIP_RECV_VOLUME	BIGINT	tcpip_recv_volume - TCP/IP received volume monitor element
TCPIP_SEND_VOLUME	BIGINT	tcpip_send_volume - TCP/IP send volume monitor element
TCPIP_RECV_WAIT_TIME	BIGINT	tcpip_recv_wait_time - TCP/IP received wait time monitor element
TCPIP_RECVS_TOTAL	BIGINT	tcpip_recvs_total - TCP/IP receives total monitor element
TCPIP_SEND_WAIT_TIME	BIGINT	tcpip_send_wait_time - TCP/IP send wait time monitor element
TCPIP_SENDS_TOTAL	BIGINT	tcpip_sends_total - TCP/IP sends total monitor element
TOTAL_APP_RQST_TIME	BIGINT	total_app_rqst_time - Total application request time monitor element
TOTAL_RQST_TIME	BIGINT	total_rqst_time - Total request time monitor element
WLM_QUEUE_TIME_TOTAL	BIGINT	wlm_queue_time_total - Workload manager total queue time monitor element
WLM_QUEUE_ASSIGNMENTS_TOTAL	BIGINT	wlm_queue_assignments_total - Workload manager total queue assignments monitor element
TOTAL_RQST_MAPPED_IN	BIGINT	total_rqst_mapped_in - Total request mapped-in monitor element
TOTAL_RQST_MAPPED_OUT	BIGINT	total_rqst_mapped_out - Total request mapped-out monitor element
TOTAL_CPU_TIME	BIGINT	total_cpu_time - Total CPU time monitor element
TOTAL_WAIT_TIME	BIGINT	total_wait_time - Total wait time monitor element
APP_RQSTS_COMPLETED_TOTAL	BIGINT	app_rqsts_completed_total - Total application requests completed monitor element
TOTAL_SECTION_SORT_TIME	BIGINT	total_section_sort_time - Total section sort time monitor element
TOTAL_SECTION_SORT_PROC_TIME	BIGINT	total_section_sort_proc_time - Total section sort processing time monitor element
TOTAL_SECTION_SORTS	BIGINT	total_section_sorts - Total section sorts monitor element
TOTAL_SORTS	BIGINT	total_sorts - Total sorts monitor element
POST_THRESHOLD_SORTS	BIGINT	post_threshold_sorts - Post threshold sorts monitor element
POST_SHRTHRESHOLD_SORTS	BIGINT	post_shrthreshold_sorts - Post shared threshold sorts monitor element
SORT_OVERFLOWS	BIGINT	sort_overflows - Sort overflows monitor element
TOTAL_COMPILE_TIME	BIGINT	total_compile_time - Total compile time monitor element
TOTAL_COMPILE_PROC_TIME	BIGINT	total_compile_proc_time - Total compile processing time monitor element
TOTAL_COMPILATIONS	BIGINT	total_compilations - Total compilations monitor element
TOTAL_IMPLICIT_COMPILE_TIME	BIGINT	total_implicit_compile_time - Total implicit compile time monitor element
TOTAL_IMPLICIT_COMPILE_PROC_TIME	BIGINT	total_implicit_compile_proc_time - Total implicit compile processing time monitor element
TOTAL_IMPLICIT_COMPILATIONS	BIGINT	total_implicit_compilations - Total implicit complications monitor element
TOTAL_SECTION_TIME	BIGINT	total_section_time - Total section time monitor element
TOTAL_SECTION_PROC_TIME	BIGINT	total_section_proc_time - Total section processing time monitor element
TOTAL_APP_SECTION_EXECUTIONS	BIGINT	total_app_section_executions - Total application section executions monitor element
TOTAL_ACT_TIME	BIGINT	total_act_time - Total activity time monitor element
TOTAL_ACT_WAIT_TIME	BIGINT	total_act_wait_time - Total activity wait time monitor element
ACT_RQSTS_TOTAL	BIGINT	act_rqsts_total - Total activity requests monitor elements
TOTAL_ROUTINE_TIME	BIGINT	total_routine_time - Total routine time monitor element
TOTAL_ROUTINE_INVOCATIONS	BIGINT	total_routine_invocations - Total routine invocations monitor elements
TOTAL_COMMIT_TIME	BIGINT	total_commit_time - Total commit time monitor element
TOTAL_COMMIT_PROC_TIME	BIGINT	total_commit_proc_time - Total commits processing time monitor element
TOTAL_APP_COMMITS	BIGINT	total_app_commits - Total application commits monitor elements
INT_COMMITS	BIGINT	int_commits - Internal commits monitor element
TOTAL_ROLLBACK_TIME	BIGINT	total_rollback_time - Total rollback time monitor element
TOTAL_ROLLBACK_PROC_TIME	BIGINT	total_rollback_proc_time - Total rollback processing time monitor element
TOTAL_APP_ROLLBACKS	BIGINT	total_app_rollbacks - Total application rollbacks monitor element
INT_ROLLBACKS	BIGINT	int_rollbacks - Internal rollbacks monitor element
TOTAL_RUNSTATS_TIME	BIGINT	total_runstats_time - Total runtime statistics time monitor element
TOTAL_RUNSTATS_PROC_TIME	BIGINT	total_runstats_proc_time - Total runtime statistics processing time monitor element
TOTAL_RUNSTATS	BIGINT	total_runstats - Total runtime statistics monitor element
TOTAL_REORG_TIME	BIGINT	total_reorg_time - Total reorganization time monitor element
TOTAL_REORG_PROC_TIME	BIGINT	total_reorg_proc_time - Total reorganization processing time monitor element
TOTAL_REORGS	BIGINT	total_reorgs - Total reorganizations monitor element
TOTAL_LOAD_TIME	BIGINT	total_load_time - Total load time monitor element
TOTAL_LOAD_PROC_TIME	BIGINT	total_load_proc_time - Total load processing time monitor element
TOTAL_LOADS	BIGINT	total_loads - Total loads monitor element
CAT_CACHE_INSERTS	BIGINT	cat_cache_inserts - Catalog cache inserts monitor element
CAT_CACHE_LOOKUPS	BIGINT	cat_cache_lookups - Catalog cache lookups monitor element
PKG_CACHE_INSERTS	BIGINT	pkg_cache_inserts - Package cache inserts monitor element
PKG_CACHE_LOOKUPS	BIGINT	pkg_cache_lookups - Package cache lookups monitor element
THRESH_VIOLATIONS	BIGINT	thresh_violations - Number of threshold violations monitor element
NUM_LW_THRESH_EXCEEDED	BIGINT	num_lw_thresh_exceeded - Number of lock wait thresholds exceeded monitor element
LOCK_WAITS_GLOBAL	BIGINT	lock_waits_global - Lock waits global monitor element
LOCK_WAIT_TIME_GLOBAL	BIGINT	lock_wait_time_global - Lock wait time global monitor element
LOCK_TIMEOUTS_GLOBAL	BIGINT	lock_timeouts_global - Lock timeouts global monitor element
LOCK_ESCALS_MAXLOCKS	BIGINT	lock_escals_maxlocks - Number of maxlocks lock escalations monitor element
LOCK_ESCALS_LOCKLIST	BIGINT	lock_escals_locklist - Number of locklist lock escalations monitor element
LOCK_ESCALS_GLOBAL	BIGINT	lock_escals_global - Number of global lock escalations monitor element
RECLAIM_WAIT_TIME	BIGINT	reclaim_wait_time - Reclaim wait time monitor element
SPACEMAPPAGE_RECLAIM_WAIT_TIME	BIGINT	spacemappage_reclaim_wait_time - Space map page reclaim wait time monitor element
CF_WAITS	BIGINT	cf_waits - Number of cluster caching facility waits monitor element
CF_WAIT_TIME	BIGINT	cf_wait_time - cluster caching facility wait time monitor element
POOL_DATA_GBP_L_READS	BIGINT	pool_data_gbp_l_reads - Group buffer pool data logical reads monitor element
POOL_DATA_GBP_P_READS	BIGINT	pool_data_gbp_p_reads - Group buffer pool data physical reads monitor element
POOL_DATA_LBP_PAGES_FOUND	BIGINT	pool_data_lbp_pages_found - Local buffer pool found data pages monitor element
POOL_DATA_GBP_INVALID_PAGES	BIGINT	pool_data_gbp_invalid_pages - Group buffer pool invalid data pages monitor element
POOL_INDEX_GBP_L_READS	BIGINT	pool_index_gbp_l_reads - Group buffer pool index logical reads monitor element
POOL_INDEX_GBP_P_READS	BIGINT	pool_index_gbp_p_reads - Group buffer pool index physical reads monitor elements
POOL_INDEX_LBP_PAGES_FOUND	BIGINT	pool_index_lbp_pages_found - Local buffer pool index pages found monitor element
POOL_INDEX_GBP_INVALID_PAGES	BIGINT	pool_index_gbp_invalid_pages - Group buffer pool invalid index pages monitor element
POOL_XDA_GBP_L_READS	BIGINT	pool_xda_gbp_l_reads - Group buffer pool XDA data logical read requests monitor element
POOL_XDA_GBP_P_READS	BIGINT	pool_xda_gbp_p_reads - Group buffer pool XDA data physical read requests monitor element
POOL_XDA_LBP_PAGES_FOUND	BIGINT	pool_xda_lbp_pages_found - Local buffer pool XDA data pages found monitor element
POOL_XDA_GBP_INVALID_PAGES	BIGINT	pool_xda_gbp_invalid_pages - Group buffer pool invalid XDA data pages monitor element
AUDIT_EVENTS_TOTAL	BIGINT	audit_events_total - Total audit events monitor element
AUDIT_FILE_WRITES_TOTAL	BIGINT	audit_file_writes_total - Total audit files written monitor element
AUDIT_FILE_WRITE_WAIT_TIME	BIGINT	audit_file_write_wait_time - Audit file write wait time monitor element
AUDIT_SUBSYSTEM_WAITS_TOTAL	BIGINT	audit_subsystem_waits_total - Total audit subsystem waits monitor element
AUDIT_SUBSYSTEM_WAIT_TIME	BIGINT	audit_subsystem_wait_time - Audit subsystem wait time monitor element
DIAGLOG_WRITES_TOTAL	BIGINT	diaglog_writes_total - Total diagnostic log file writes monitor element
DIAGLOG_WRITE_WAIT_TIME	BIGINT	diaglog_write_wait_time - Diagnostic log file write wait time monitor element
FCM_MESSAGE_RECVS_TOTAL	BIGINT	fcm_message_recvs_total - Total FCM message receives monitor element
FCM_MESSAGE_RECV_VOLUME	BIGINT	fcm_message_recv_volume - FCM message received volume monitor element
FCM_MESSAGE_RECV_WAIT_TIME	BIGINT	fcm_message_recv_wait_time - FCM message received wait time monitor element
FCM_MESSAGE_SENDS_TOTAL	BIGINT	fcm_message_sends_total - Total FCM message sends monitor element
FCM_MESSAGE_SEND_VOLUME	BIGINT	fcm_message_send_volume - FCM message send volume monitor element
FCM_MESSAGE_SEND_WAIT_TIME	BIGINT	fcm_message_send_wait_time - FCM message send wait time monitor element
FCM_TQ_RECVS_TOTAL	BIGINT	fcm_tq_recvs_total - FCM table queue receives total monitor element
FCM_TQ_RECV_VOLUME	BIGINT	fcm_tq_recv_volume - FCM table queue received volume monitor element
FCM_TQ_RECV_WAIT_TIME	BIGINT	fcm_tq_recv_wait_time - FCM table queue received wait time monitor element
FCM_TQ_SENDS_TOTAL	BIGINT	fcm_tq_sends_total - FCM table queue send total monitor element
FCM_TQ_SEND_VOLUME	BIGINT	fcm_tq_send_volume - FCM table queue send volume monitor element
FCM_TQ_SEND_WAIT_TIME	BIGINT	fcm_tq_send_wait_time - FCM table queue send wait time monitor element
TOTAL_ROUTINE_USER_CODE_PROC_TIME	BIGINT	total_routine_user_code_proc_time - Total routine user code processing time monitor element
TOTAL_ROUTINE_USER_CODE_TIME	BIGINT	total_routine_user_code_time - Total routine user code time monitor element
TQ_TOT_SEND_SPILLS	BIGINT	tq_tot_send_spills - Total number of table queue buffers overflowed monitor element
EVMON_WAIT_TIME	BIGINT	evmon_wait_time - Event monitor wait time monitor element
EVMON_WAITS_TOTAL	BIGINT	evmon_waits_total - Event monitor total waits monitor element
TOTAL_EXTENDED_LATCH_WAIT_TIME	BIGINT	total_extended_latch_wait_time - Total extended latch wait time monitor element
TOTAL_EXTENDED_LATCH_WAITS	BIGINT	total_extended_latch_waits - Total extended latch waits monitor element
TOTAL_STATS_FABRICATION_TIME	BIGINT	total_stats_fabrication_time - Total statistics fabrication time monitor element
TOTAL_STATS_FABRICATION_PROC_TIME	BIGINT	total_stats_fabrication_proc_time - Total statistics fabrication processing time monitor element
TOTAL_STATS_FABRICATIONS	BIGINT	total_stats_fabrications - Total statistics fabrications monitor elements
TOTAL_SYNC_RUNSTATS_TIME	BIGINT	total_sync_runstats_time - Total synchronous RUNSTATS time monitor elements
TOTAL_SYNC_RUNSTATS_PROC_TIME	BIGINT	total_sync_runstats_proc_time - Total synchronous RUNSTATS processing time monitor element
TOTAL_SYNC_RUNSTATS	BIGINT	total_sync_runstats - Total synchronous RUNSTATS activities monitor element
TOTAL_DISP_RUN_QUEUE_TIME	BIGINT	total_disp_run_queue_time - Total dispatcher run queue time monitor element
TOTAL_PEDS	BIGINT	total_peds - Total partial early distincts monitor element
DISABLED_PEDS	BIGINT	disabled_peds - Disabled partial early distincts monitor element
POST_THRESHOLD_PEDS	BIGINT	post_threshold_peds - Partial early distincts threshold monitor element
TOTAL_PEAS	BIGINT	total_peas - Total partial early aggregations monitor element
POST_THRESHOLD_PEAS	BIGINT	post_threshold_peas - Partial early aggregation threshold monitor element
TQ_SORT_HEAP_REQUESTS	BIGINT	tq_sort_heap_requests - Table queue sort heap requests monitor element
TQ_SORT_HEAP_REJECTIONS	BIGINT	tq_sort_heap_rejections - Table queue sort heap rejections monitor element
POOL_QUEUED_ASYNC_DATA_REQS	BIGINT	pool_queued_async_data_reqs - Data prefetch requests monitor element
POOL_QUEUED_ASYNC_INDEX_REQS	BIGINT	pool_queued_async_index_reqs - Index prefetch requests monitor element
POOL_QUEUED_ASYNC_XDA_REQS	BIGINT	pool_queued_async_xda_reqs - XDA prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_DATA_REQS	BIGINT	pool_queued_async_temp_data_reqs - Data prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_INDEX_REQS	BIGINT	pool_queued_async_temp_index_reqs - Index prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_XDA_REQS	BIGINT	pool_queued_async_temp_xda_reqs - XDA data prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_OTHER_REQS	BIGINT	pool_queued_async_other_reqs - Non-prefetch requests monitor element
POOL_QUEUED_ASYNC_DATA_PAGES	BIGINT	pool_queued_async_data_pages - Data pages prefetch requests monitor element
POOL_QUEUED_ASYNC_INDEX_PAGES	BIGINT	pool_queued_async_index_pages - Index pages prefetch requests monitor element
POOL_QUEUED_ASYNC_XDA_PAGES	BIGINT	pool_queued_async_xda_pages - XDA pages prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_DATA_PAGES	BIGINT	pool_queued_async_temp_data_pages - Data pages prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES	BIGINT	pool_queued_async_temp_index_pages - Index pages prefetch requests for temporary table spaces monitor element
POOL_QUEUED_ASYNC_TEMP_XDA_PAGES	BIGINT	pool_queued_async_temp_xda_pages - XDA data pages prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_DATA_REQS	BIGINT	pool_failed_async_data_reqs - Failed data prefetch requests monitor element
POOL_FAILED_ASYNC_INDEX_REQS	BIGINT	pool_failed_async_index_reqs - Failed index prefetch requests monitor element
POOL_FAILED_ASYNC_XDA_REQS	BIGINT	pool_failed_async_xda_reqs - Failed XDA prefetch requests monitor element
POOL_FAILED_ASYNC_TEMP_DATA_REQS	BIGINT	pool_failed_async_temp_data_reqs - Failed data prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_TEMP_INDEX_REQS	BIGINT	pool_failed_async_temp_index_reqs - Failed index prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_TEMP_XDA_REQS	BIGINT	pool_failed_async_temp_xda_reqs - Failed XDA prefetch requests for temporary table spaces monitor element
POOL_FAILED_ASYNC_OTHER_REQS	BIGINT	pool_failed_async_other_reqs - Failed non-prefetch requests monitor element
PREFETCH_WAIT_TIME	BIGINT	prefetch_wait_time - Time waited for prefetch monitor element
PREFETCH_WAITS	BIGINT	prefetch_waits - Prefetcher wait count monitor element
APP_ACT_COMPLETED_TOTAL	BIGINT	app_act_completed_total - Total successful external coordinator activities monitor element
APP_ACT_ABORTED_TOTAL	BIGINT	app_act_aborted_total - Total failed external coordinator activities monitor element
APP_ACT_REJECTED_TOTAL	BIGINT	app_act_rejected_total - Total rejected external coordinator activities monitor element
TOTAL_CONNECT_REQUEST_TIME	BIGINT	total_connect_request_time - Total connection or switch user request time monitor element
TOTAL_CONNECT_REQUEST_PROC_TIME	BIGINT	total_connect_request_proc_time - Total connection or switch user request processing time monitor element
TOTAL_CONNECT_REQUESTS	BIGINT	total_connect_requests - Connection or switch user requests monitor element
TOTAL_CONNECT_AUTHENTICATION_TIME	BIGINT	total_connect_authentication_time - Total connection or switch user authentication request time monitor element
TOTAL_CONNECT_AUTHENTICATION_PROC_TIME	BIGINT	total_connect_authentication_proc_time - Total connection authentication processing time monitor element
TOTAL_CONNECT_AUTHENTICATIONS	BIGINT	total_connect_authentications - Connections or switch user authentications performed monitor element
POOL_DATA_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	pool_data_gbp_indep_pages_found_in_lbp - Group buffer pool independent data pages found in local buffer pool monitor element
POOL_INDEX_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	pool_index_gbp_indep_pages_found_in_lbp - Group buffer pool independent index pages found in local buffer pool monitor element
POOL_XDA_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	pool_xda_gbp_indep_pages_found_in_lbp - Group buffer pool XDA independent pages found in local buffer pool monitor element
COMM_EXIT_WAIT_TIME	BIGINT	comm_exit_wait_time - Communication buffer exit wait time monitor element
COMM_EXIT_WAITS	BIGINT	comm_exit_waits - Communication buffer exit number of waits monitor element
IDA_SEND_WAIT_TIME	BIGINT	ida_send_wait_time - Time spent waiting to send data monitor element
IDA_SENDS_TOTAL	BIGINT	ida_sends_total - Number of times data sent monitor element
IDA_SEND_VOLUME	BIGINT	ida_send_volume - Total data volume sent monitor element
IDA_RECV_WAIT_TIME	BIGINT	ida_recv_wait_time - Time spent waiting to receive data monitor element
IDA_RECVS_TOTAL	BIGINT	ida_recvs_total - Number of times data received monitor element
IDA_RECV_VOLUME	BIGINT	ida_recv_volume - Total data volume received monitor element
ROWS_DELETED	BIGINT	rows_deleted - Rows deleted monitor element
ROWS_INSERTED	BIGINT	rows_inserted - Rows inserted monitor element
ROWS_UPDATED	BIGINT	rows_updated - Rows updated monitor element
TOTAL_HASH_JOINS	BIGINT	total_hash_joins - Total Hash Joins monitor element
TOTAL_HASH_LOOPS	BIGINT	total_hash_loops - Total Hash Loops monitor element
HASH_JOIN_OVERFLOWS	BIGINT	hash_join_overflows - Hash Join Overflows monitor element
HASH_JOIN_SMALL_OVERFLOWS	BIGINT	hash_join_small_overflows - Hash Join Small Overflows monitor element
POST_SHRTHRESHOLD_HASH_JOINS	BIGINT	post_shrthreshold_hash_joins - Post threshold hash joins monitor element
TOTAL_OLAP_FUNCS	BIGINT	total_olap_funcs - Total OLAP Functions monitor element
OLAP_FUNC_OVERFLOWS	BIGINT	olap_func_overflows - OLAP Function Overflows monitor element
DYNAMIC_SQL_STMTS	BIGINT	dynamic_sql_stmts - Dynamic SQL Statements Attempted monitor element
STATIC_SQL_STMTS	BIGINT	static_sql_stmts - Static SQL Statements Attempted monitor element
FAILED_SQL_STMTS	BIGINT	failed_sql_stmts - Failed Statement Operations monitor element
SELECT_SQL_STMTS	BIGINT	select_sql_stmts - Select SQL Statements Executed monitor element
UID_SQL_STMTS	BIGINT	uid_sql_stmts - Update/Insert/Delete SQL Statements Executed monitor element
DDL_SQL_STMTS	BIGINT	ddl_sql_stmts - Data Definition Language (DDL) SQL Statements monitor element
MERGE_SQL_STMTS	BIGINT	merge_sql_stmts - Merge SQL statements executed
XQUERY_STMTS	BIGINT	xquery_stmts - XQuery Statements Attempted monitor element
IMPLICIT_REBINDS	BIGINT	implicit_rebinds - number of implicit rebinds
BINDS_PRECOMPILES	BIGINT	binds_precompiles - Binds/Precompiles Attempted monitor element
INT_ROWS_DELETED	BIGINT	int_rows_deleted - Internal Rows Deleted monitor element
INT_ROWS_INSERTED	BIGINT	int_rows_inserted - Internal Rows Inserted monitor element
INT_ROWS_UPDATED	BIGINT	int_rows_updated - Internal Rows Updated monitor element
CALL_SQL_STMTS	BIGINT	call_sql_stmts - CALL SQL statements executed
POOL_COL_L_READS	BIGINT	pool_col_l_reads - Buffer pool column-organized logical reads
POOL_TEMP_COL_L_READS	BIGINT	pool_temp_col_l_reads - Buffer pool column-organized temporary logical reads
POOL_COL_P_READS	BIGINT	pool_col_p_reads - Buffer pool column-organized physical reads
POOL_TEMP_COL_P_READS	BIGINT	pool_temp_col_p_reads - Buffer pool column-organized temporary physical reads
POOL_COL_LBP_PAGES_FOUND	BIGINT	pool_col_lbp_pages_found - Buffer pool column-organized LBP pages found
POOL_COL_WRITES	BIGINT	pool_col_writes - Buffer pool column-organized writes
POOL_COL_GBP_L_READS	BIGINT	pool_col_gbp_l_reads - Buffer pool column-organized GBP logical reads
POOL_COL_GBP_P_READS	BIGINT	pool_col_gbp_p_reads - Buffer pool column-organized GBP physical reads
POOL_COL_GBP_INVALID_PAGES	BIGINT	pool_col_gbp_invalid_pages - Buffer pool column-organized GBP invalid data pages
POOL_COL_GBP_INDEP_PAGES_FOUND_IN_LBP	BIGINT	pool_col_gbp_indep_pages_found_in_lbp - Buffer pool column-organized GBP independent pages found in local buffer pool
POOL_QUEUED_ASYNC_COL_REQS	BIGINT	pool_queued_async_col_reqs - Column-organized prefetch requests
POOL_QUEUED_ASYNC_TEMP_COL_REQS	BIGINT	pool_queued_async_temp_col_reqs - Column-organized temporary prefetch requests
POOL_QUEUED_ASYNC_COL_PAGES	BIGINT	pool_queued_async_col_pages - Column-organized page prefetch requests
POOL_QUEUED_ASYNC_TEMP_COL_PAGES	BIGINT	pool_queued_async_temp_col_pages - Column-organized page temporary prefetch requests
POOL_FAILED_ASYNC_COL_REQS	BIGINT	pool_failed_async_col_reqs - Failed column-organized prefetch requests
POOL_FAILED_ASYNC_TEMP_COL_REQS	BIGINT	pool_failed_async_temp_col_reqs - Failed column-organized temporary prefetch requests
TOTAL_COL_TIME	BIGINT	total_col_time - Total column-organized time
TOTAL_COL_PROC_TIME	BIGINT	total_col_proc_time - Total column-organized processing time
TOTAL_COL_EXECUTIONS	BIGINT	total_col_executions - Total column-organized executions
POST_THRESHOLD_HASH_JOINS	BIGINT	post_threshold_hash_joins - Hash Join Threshold monitor element
POOL_DATA_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
POOL_INDEX_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
POOL_XDA_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
POOL_COL_CACHING_TIER_L_READS	BIGINT	Reserved for future use.
POOL_DATA_CACHING_TIER_PAGE_WRITES	BIGINT	Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGE_WRITES	BIGINT	Reserved for future use.
POOL_XDA_CACHING_TIER_PAGE_WRITES	BIGINT	Reserved for future use.
POOL_COL_CACHING_TIER_PAGE_WRITES	BIGINT	Reserved for future use.
POOL_DATA_CACHING_TIER_PAGE_UPDATES	BIGINT	Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGE_UPDATES	BIGINT	Reserved for future use.
POOL_XDA_CACHING_TIER_PAGE_UPDATES	BIGINT	Reserved for future use.
POOL_COL_CACHING_TIER_PAGE_UPDATES	BIGINT	Reserved for future use.
POOL_CACHING_TIER_PAGE_READ_TIME	BIGINT	Reserved for future use.
POOL_CACHING_TIER_PAGE_WRITE_TIME	BIGINT	Reserved for future use.
POOL_DATA_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_INDEX_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_XDA_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_COL_CACHING_TIER_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_DATA_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
POOL_INDEX_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
POOL_XDA_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
POOL_COL_CACHING_TIER_GBP_INVALID_PAGES	BIGINT	Reserved for future use.
POOL_DATA_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_INDEX_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_XDA_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
POOL_COL_CACHING_TIER_GBP_INDEP_PAGES_FOUND	BIGINT	Reserved for future use.
TOTAL_HASH_GRPBYS	BIGINT	total_hash_grpbys - Total hash group by operations
HASH_GRPBY_OVERFLOWS	BIGINT	hash_grpby_overflows - Hash group by overflows
POST_THRESHOLD_HASH_GRPBYS	BIGINT	post_threshold_hash_grpbys - Hash group by threshold
POST_THRESHOLD_OLAP_FUNCS	BIGINT	post_threshold_olap_funcs - OLAP function threshold monitor element
POST_THRESHOLD_COL_VECTOR_CONSUMERS	BIGINT	post_threshold_col_vector_consumers - Post threshold columnar_vector consumers
TOTAL_COL_VECTOR_CONSUMERS	BIGINT	total_col_vector_consumers - Total columnar_vector consumers
ACTIVE_HASH_GRPBYS	BIGINT	active_hash_grpbys - Active hash GROUP BY operations
ACTIVE_HASH_JOINS	BIGINT	active_hash_joins - Active hash joins
ACTIVE_OLAP_FUNCS	BIGINT	active_olap_funcs - Active OLAP Functions
ACTIVE_PEAS	BIGINT	active_peas - Active partial early aggregations operations
ACTIVE_PEDS	BIGINT	active_peds - Active partial early distinct operations
ACTIVE_SORT_CONSUMERS	BIGINT	active_sort_consumers - Active sort consumers
ACTIVE_SORTS	BIGINT	active_sorts - Active Sorts
ACTIVE_COL_VECTOR_CONSUMERS	BIGINT	active_col_vector_consumers - Active columnar_vector consumers
SORT_HEAP_ALLOCATED	BIGINT	sort_heap_allocated - Total Sort Heap Allocated
SORT_SHRHEAP_ALLOCATED	BIGINT	sort_shrheap_allocated - Sort Share Heap Currently Allocated
TOTAL_BACKUP_TIME	BIGINT	total_backup_time - Total elapsed time for online backups
TOTAL_BACKUP_PROC_TIME	BIGINT	total_backup_proc_time - Total non-wait time for online backups
TOTAL_BACKUPS	BIGINT	total_backups - Total backups
TOTAL_INDEX_BUILD_TIME	BIGINT	total_index_build_time - Total time spent building indexes due to index creation, recreation or rebuilding
TOTAL_INDEX_BUILD_PROC_TIME	BIGINT	total_index_build_proc_time - Total non-wait time spent building indexes due to index creation, recreation or rebuilding
TOTAL_INDEXES_BUILT	BIGINT	total_indexes_built - Total number of indexes built
FCM_TQ_RECV_WAITS_TOTAL	BIGINT	fcm_tq_recv_waits_total - Number of times spent waiting to receive the next buffer monitor element
FCM_MESSAGE_RECV_WAITS_TOTAL	BIGINT	fcm_message_recv_waits_total - Number of times spent waiting for FCM reply message monitor element
FCM_TQ_SEND_WAITS_TOTAL	BIGINT	fcm_tq_send_waits_total - Number of times spent waiting to send the next buffer monitor element
FCM_MESSAGE_SEND_WAITS_TOTAL	BIGINT	fcm_message_send_waits_total - Number of times spent blocking on an FCM message send monitor element
FCM_SEND_WAITS_TOTAL	BIGINT	fcm_send_waits_total - Number of times spent blocking on an FCM send operation monitor element
FCM_RECV_WAITS_TOTAL	BIGINT	fcm_recv_waits_total - Number of times spent waiting to receive data through FCM monitor element

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.

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 is 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).

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