MON_GET_ROUTINE table function - get aggregated routine execution metrics

The MON_GET_ROUTINE table function returns aggregated execution metrics for procedures, external procedures, compiled functions, external functions, compiled triggers, and anonymous blocks invoked since the database was activated.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the routine
DATAACCESS authority
SQLADM authority
DBADM authority

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Routine parameters

routine_type

An input parameter of type CHAR(2) that specifies the type of routine or compiled trigger for which data is returned:

"P" for a procedure
"SP" for the specific name of a procedure
"F" for a compiled function
"SF" for the specific name of a compiled function
"T" for a compiled trigger
"C" for a dynamically prepared compound SQL statement or an anonymous block in PL/SQL

If the "P","F","T" or "C" routine type is specified and no input name is specified, all routines of the given type will be returned. If the routine type is "SP" or "SF", the specific name of the routine must be provided.

If the routine type is an empty string, NULL, or blanks, all routines of all types are returned.

routine_schema

An input parameter of type VARCHAR(128) that specifies the schema for the routine or trigger. For dynamically prepared compound SQL statements or anonymous blocks, the schema can be determined using the MON_GET_SECTION_ROUTINES table function. Use NULL or an empty string to return the routines and triggers in all schemas. This parameter is case sensitive.

routine_module_name

An input parameter of type VARCHAR(128) that specifies the name of the module for the input routine, if applicable. Use NULL or an empty string to return the routines in all modules. This parameter is case sensitive.

routine_name

An input parameter of type VARCHAR(128) that specifies the name of the routine. If the input parameter is "SP" or "SF", the specific name of the routine must be provided. For dynamically prepared compound SQL statements or anonymous blocks, the name can be determined using the MON_GET_SECTION_ROUTINES table function. Use NULL or an empty string to return all routines that match the other input parameters. This parameter is case sensitive.

member

An input parameter 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 implicitly set.

Information returned

Table 1. Information returned for MON_GET_ROUTINE
Column name	Data type	Description or corresponding monitor element
ROUTINE_TYPE	CHAR(1)	routine_type - Routine type monitor element
ROUTINE_SCHEMA	VARCHAR(128)	routine_schema - Routine schema monitor element
ROUTINE_MODULE_NAME	VARCHAR(128)	routine_module_name - Routine module name monitor element
ROUTINE_NAME	VARCHAR(128)	routine_name - Routine name monitor element
SPECIFIC_NAME	VARCHAR(128)	specific_name - Specific name monitor element
DYN_COMPOUND_EXEC_ID	VARCHAR(32) FOR BIT DATA	dyn_compound_exec_id - Dynamic compound statement executable identifier monitor element
MEMBER	SMALLINT	member - Database member monitor element
ROUTINE_ID	INTEGER	routine_id - Routine ID monitor element
SUBROUTINE_ID	INTEGER	subroutine_id - Subroutine identifier monitor element
LIB_ID	BIGINT	lib_id - Library identifier monitor element
EXEC_LIST_MEM_EXCEEDED	CHAR(1)	exec_list_mem_exceeded - Execution list memory exceeded monitor element
EXEC_LIST_CLEANUP_TIME	TIMESTAMP	exec_list_cleanup_time - Execution list cleanup time monitor element
TOTAL_TIMES_ROUTINE_INVOKED	BIGINT	total_times_routine_invoked - Total routine invoked occurrences monitor element
TOTAL_NESTED_INVOCATIONS	BIGINT	total_nested_invocations - Total nested invocations monitor element
TOTAL_ROUTINE_COORD_TIME	BIGINT	total_routine_coord_time - Total routine coordinator time monitor element
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
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_xda_l_reads - Buffer Pool 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
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 received volume
FCM_RECVS_TOTAL	BIGINT	fcm_recvs_total - FCM receives 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 received wait time
FCM_SEND_WAIT_TIME	BIGINT	fcm_send_wait_time - FCM send wait time
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 times full log buffer caused agents to wait
LOG_DISK_WAIT_TIME	BIGINT	log_disk_wait_time - Log disk wait time
LOG_DISK_WAITS_TOTAL	BIGINT	log_disk_waits_total - Total log disk waits
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
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_CPU_TIME	BIGINT	total_cpu_time - Total CPU time .
TOTAL_WAIT_TIME	BIGINT	total_wait_time - Total wait time
APP_RQSTS_COMPLETED_TOTAL	BIGINT	tapp_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 application 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 . The total number of times a routine was invoked by this routine.
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 lock wait thresholds exceeded
LOCK_WAITS_GLOBAL	BIGINT	lock_waits_global - Lock waits global
LOCK_WAIT_TIME_GLOBAL	BIGINT	lock_wait_time_global - Lock wait time global
LOCK_TIMEOUTS_GLOBAL	BIGINT	lock_timeouts_global - Lock timeouts global
LOCK_ESCALS_MAXLOCKS	BIGINT	lock_escals_maxlocks - Number of maxlocks lock escalations
LOCK_ESCALS_LOCKLIST	BIGINT	llock_escals_locklist - Number of locklist lock escalations
LOCK_ESCALS_GLOBAL	BIGINT	lock_escals_global - Number of global lock escalations
RECLAIM_WAIT_TIME	BIGINT	reclaim_wait_time - Reclaim wait time
SPACEMAPPAGE_RECLAIM_WAIT_TIME	BIGINT	spacemappage_reclaim_wait_time - Space map page reclaim wait time
CF_WAITS	BIGINT	cf_waits - Number of cluster caching facility waits
CF_WAIT_TIME	BIGINT	cf_wait_time - cluster caching facility wait time
POOL_DATA_GBP_L_READS	BIGINT	pool_data_gbp_l_reads - Group buffer pool data logical reads
POOL_DATA_GBP_P_READS	BIGINT	pool_data_gbp_p_reads - Group buffer pool data physical reads
POOL_DATA_LBP_PAGES_FOUND	BIGINT	pool_data_lbp_pages_found - Local buffer pool found data pages
POOL_DATA_GBP_INVALID_PAGES	BIGINT	pool_data_gbp_invalid_pages - Group buffer pool invalid data pages
POOL_INDEX_GBP_L_READS	BIGINT	pool_index_gbp_l_reads - Group buffer pool index logical reads
POOL_INDEX_GBP_P_READS	BIGINT	pool_index_gbp_p_reads - Group buffer pool index physical reads
POOL_INDEX_LBP_PAGES_FOUND	BIGINT	pool_index_lbp_pages_found - Local buffer pool index pages found
POOL_INDEX_GBP_INVALID_PAGES	BIGINT	pool_index_gbp_invalid_pages - Group buffer pool invalid index pages
POOL_XDA_GBP_L_READS	BIGINT	pool_xda_gbp_l_reads - Group buffer pool XDA data logical read requests
POOL_XDA_GBP_P_READS	BIGINT	pool_xda_gbp_p_reads - Group buffer pool XDA data physical read requests
POOL_XDA_LBP_PAGES_FOUND	BIGINT	pool_xda_lbp_pages_found - Local buffer pool XDA data pages found
POOL_XDA_GBP_INVALID_PAGES	BIGINT	pool_xda_gbp_invalid_pages - Group buffer pool invalid XDA data pages
AUDIT_EVENTS_TOTAL	BIGINT	audit_events_total - Total audit events
AUDIT_FILE_WRITES_TOTAL	BIGINT	audit_file_writes_total - Total audit files written
AUDIT_FILE_WRITE_WAIT_TIME	BIGINT	audit_file_write_wait_time - Audit file write wait time
AUDIT_SUBSYSTEM_WAITS_TOTAL	BIGINT	audit_subsystem_waits_total - Total audit subsystem waits
AUDIT_SUBSYSTEM_WAIT_TIME	BIGINT	audit_subsystem_wait_time - Audit subsystem wait time
DIAGLOG_WRITES_TOTAL	BIGINT	diaglog_writes_total - Total diagnostic log file writes
DIAGLOG_WRITE_WAIT_TIME	BIGINT	diaglog_write_wait_time - Diagnostic log file write wait time
FCM_MESSAGE_RECVS_TOTAL	BIGINT	fcm_message_recvs_total - Total FCM message receives
FCM_MESSAGE_RECV_VOLUME	BIGINT	fcm_message_recv_volume - FCM message received volume
FCM_MESSAGE_RECV_WAIT_TIME	BIGINT	fcm_message_recv_wait_time - FCM message received wait time
FCM_MESSAGE_SENDS_TOTAL	BIGINT	fcm_message_sends_total - Total FCM message sends
FCM_MESSAGE_SEND_VOLUME	BIGINT	fcm_message_send_volume - FCM message send volume
FCM_MESSAGE_SEND_WAIT_TIME	BIGINT	fcm_message_send_wait_time - FCM message send wait time
FCM_TQ_RECVS_TOTAL	BIGINT	fcm_tq_recvs_total - FCM table queue receives total
FCM_TQ_RECV_VOLUME	BIGINT	fcm_tq_recv_volume - FCM table queue received volume
FCM_TQ_RECV_WAIT_TIME	BIGINT	fcm_tq_recv_wait_time - FCM table queue received wait time
FCM_TQ_SENDS_TOTAL	BIGINT	fcm_tq_sends_total - FCM table queue send totaltime
FCM_TQ_SEND_VOLUME	BIGINT	fcm_tq_send_volume - FCM table queue send volume
FCM_TQ_SEND_WAIT_TIME	BIGINT	fcm_tq_send_wait_time - FCM table queue send wait time
TOTAL_ROUTINE_USER_CODE_PROC_TIME	BIGINT	total_routine_user_code_proc_time - Total routine user code processing time
TOTAL_ROUTINE_USER_CODE_TIME	BIGINT	total_routine_user_code_time - Total routine user code time
TQ_TOT_SEND_SPILLS	BIGINT	tq_tot_send_spills - Total number of table queue buffers overflowed
EVMON_WAIT_TIME	BIGINT	evmon_wait_time - Event monitor wait time
EVMON_WAITS_TOTAL	BIGINT	evmon_waits_total - Event monitor total waits
TOTAL_EXTENDED_LATCH_WAIT_TIME	BIGINT	total_extended_latch_wait_time - Total extended latch wait time
TOTAL_EXTENDED_LATCH_WAITS	BIGINT	ttotal_extended_latch_waits - Total extended latch waits
TOTAL_STATS_FABRICATION_TIME	BIGINT	total_stats_fabrication_time - Total statistics fabrication time
TOTAL_STATS_FABRICATION_PROC_TIME	BIGINT	total_stats_fabrication_proc_time - Total statistics fabrication processing time
TOTAL_STATS_FABRICATIONS	BIGINT	total_stats_fabrications - Total statistics fabrications
TOTAL_SYNC_RUNSTATS_TIME	BIGINT	total_sync_runstats_time - Total synchronous RUNSTATS time
TOTAL_SYNC_RUNSTATS_PROC_TIME	BIGINT	total_sync_runstats_proc_time - Total synchronous RUNSTATS processing time
TOTAL_SYNC_RUNSTATS	BIGINT	total_sync_runstats - Total synchronous RUNSTATS activities
TOTAL_DISP_RUN_QUEUE_TIME	BIGINT	total_disp_run_queue_time - Total dispatcher run queue time
TOTAL_PEDS	BIGINT	total_peds - Total partial early distincts
DISABLED_PEDS	BIGINT	disabled_peds - Disabled partial early distincts
POST_THRESHOLD_PEDS	BIGINT	post_threshold_peds - Partial early distincts threshold
TOTAL_PEAS	BIGINT	total_peas - Total partial early aggregations
POST_THRESHOLD_PEAS	BIGINT	post_threshold_peas - Partial early aggregation threshold
TQ_SORT_HEAP_REQUESTS	BIGINT	tq_sort_heap_requests - Table queue sort heap requests
TQ_SORT_HEAP_REJECTIONS	BIGINT	tq_sort_heap_rejections - Table queue sort heap rejections
POOL_QUEUED_ASYNC_DATA_REQS	BIGINT	pool_queued_async_data_reqs - Data prefetch requests
POOL_QUEUED_ASYNC_INDEX_REQS	BIGINT	pool_queued_async_index_reqs - Index prefetch requests
POOL_QUEUED_ASYNC_XDA_REQS	BIGINT	pool_queued_async_xda_reqs - XDA prefetch requests
POOL_QUEUED_ASYNC_TEMP_DATA_REQS	BIGINT	pool_queued_async_temp_data_reqs - Data prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_INDEX_REQS	BIGINT	pool_queued_async_temp_index_reqs - Index prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_XDA_REQS	BIGINT	pool_queued_async_temp_xda_reqs - XDA data prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_OTHER_REQS	BIGINT	pool_queued_async_other_reqs - Other requests handled by prefetchers
POOL_QUEUED_ASYNC_DATA_PAGES	BIGINT	pool_queued_async_data_pages - Data pages prefetch requests
POOL_QUEUED_ASYNC_INDEX_PAGES	BIGINT	pool_queued_async_index_pages - Index pages prefetch requests
POOL_QUEUED_ASYNC_XDA_PAGES	BIGINT	pool_queued_async_xda_pages - XDA pages prefetch requests
POOL_QUEUED_ASYNC_TEMP_DATA_PAGES	BIGINT	pool_queued_async_temp_data_pages - Data pages prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_INDEX_PAGES	BIGINT	pool_queued_async_temp_index_pages - Index pages prefetch requests for temporary table spaces
POOL_QUEUED_ASYNC_TEMP_XDA_PAGES	BIGINT	pool_queued_async_temp_xda_pages - XDA data pages prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_DATA_REQS	BIGINT	pool_failed_async_data_reqs - Failed data prefetch requests
POOL_FAILED_ASYNC_INDEX_REQS	BIGINT	pool_failed_async_index_reqs - Failed index prefetch requests
POOL_FAILED_ASYNC_XDA_REQS	BIGINT	pool_failed_async_xda_reqs - Failed XDA prefetch requests
POOL_FAILED_ASYNC_TEMP_DATA_REQS	BIGINT	pool_failed_async_temp_data_reqs - Failed data prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_TEMP_INDEX_REQS	BIGINT	pool_failed_async_temp_index_reqs - Failed index prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_TEMP_XDA_REQS	BIGINT	pool_failed_async_temp_xda_reqs - Failed XDA prefetch requests for temporary table spaces
POOL_FAILED_ASYNC_OTHER_REQS	BIGINT	pool_failed_async_other_reqs - Failed non-prefetch requestss
PREFETCH_WAIT_TIME	BIGINT	prefetch_wait_time - Time waited for prefetch
PREFETCH_WAITS	BIGINT	prefetch_waits - Prefetcher wait count
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
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
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
COMM_EXIT_WAIT_TIME	BIGINT	comm_exit_wait_time - Communication buffer exit wait time
COMM_EXIT_WAITS	BIGINT	comm_exit_waits - Communication buffer exit number of waits
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
POOL_COL_L_READS	BIGINT	pool_col_l_reads - Buffer pool column-organized logical reads monitor element
POOL_TEMP_COL_L_READS	BIGINT	pool_temp_col_l_reads - Buffer pool column-organized temporary logical reads monitor element
POOL_COL_P_READS	BIGINT	pool_col_p_reads - Buffer pool column-organized physical reads monitor element
POOL_TEMP_COL_P_READS	BIGINT	pool_temp_col_p_reads - Buffer pool column-organized temporary physical reads monitor element
POOL_COL_LBP_PAGES_FOUND	BIGINT	pool_col_lbp_pages_found - Buffer pool column-organized LBP pages found monitor element
POOL_COL_WRITES	BIGINT	pool_col_writes - Buffer pool column-organized writes monitor element
POOL_COL_GBP_L_READS	BIGINT	pool_col_gbp_l_reads - Buffer pool column-organized GBP logical reads monitor element
POOL_COL_GBP_P_READS	BIGINT	pool_col_gbp_p_reads - Buffer pool column-organized GBP physical reads monitor element
POOL_COL_GBP_INVALID_PAGES	BIGINT	pool_col_gbp_invalid_pages - Buffer pool column-organized GBP invalid data pages monitor element
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 monitor element
POOL_QUEUED_ASYNC_COL_REQS	BIGINT	pool_queued_async_col_reqs - Column-organized prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_COL_REQS	BIGINT	pool_queued_async_temp_col_reqs - Column-organized temporary prefetch requests monitor element
POOL_QUEUED_ASYNC_COL_PAGES	BIGINT	pool_queued_async_col_pages - Column-organized page prefetch requests monitor element
POOL_QUEUED_ASYNC_TEMP_COL_PAGES	BIGINT	pool_queued_async_temp_col_pages - Column-organized page temporary prefetch requests monitor element
POOL_FAILED_ASYNC_COL_REQS	BIGINT	pool_queued_async_temp_col_pages - Column-organized page temporary prefetch requests monitor element
POOL_FAILED_ASYNC_TEMP_COL_REQS	BIGINT	pool_failed_async_col_reqs - Failed column-organized prefetch requests monitor element
TOTAL_COL_TIME	BIGINT	total_col_time - Total column-organized time monitor element
TOTAL_COL_PROC_TIME	BIGINT	total_col_proc_time - Total column-organized processing time monitor element
TOTAL_COL_EXECUTIONS	BIGINT	total_col_executions - Total column-organized executions 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 monitor element
XQUERY_STMTS	BIGINT	xquery_stmts - XQuery Statements Attempted monitor element
IMPLICIT_REBINDS	BIGINT	implicit_rebinds - number of implicit rebinds monitor element
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 monitor element
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
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
TOTAL_INDEX_BUILD_PROC_TIME	BIGINT	total_index_build_proc_time - Total non-wait time spent building indexes due to index creation
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
EXT_TABLE_RECV_WAIT_TIME	BIGINT	ext_table_recv_wait_time - Total agent wait time for external table readers monitor element
EXT_TABLE_RECVS_TOTAL	BIGINT	ext_table_recvs_total - Total row batches received from external table readers monitor element
EXT_TABLE_RECV_VOLUME	BIGINT	ext_table_recv_volume - Total data received from external table readers monitor element
EXT_TABLE_READ_VOLUME	BIGINT	ext_table_read_volume - Total data read by external table readers monitor element
EXT_TABLE_SEND_WAIT_TIME	BIGINT	ext_table_send_wait_time - Total agent wait time for external table writers monitor element
EXT_TABLE_SENDS_TOTAL	BIGINT	ext_table_sends_total - Total row batches sent to external table writers monitor element
EXT_TABLE_SEND_VOLUME	BIGINT	ext_table_send_volume - Total data sent to external table writers monitor element
EXT_TABLE_WRITE_VOLUME	BIGINT	ext_table_write_volume - Total data written by external table writers monitor element
COL_VECTOR_CONSUMER_OVERFLOWS	BIGINT	col_vector_consumer_overflows - Columnar vector consumer overflows monitor element.
TOTAL_COL_SYNOPSIS_TIME	BIGINT	total_col_synopsis_time - Total column-organized synopsis table time monitor element
TOTAL_COL_SYNOPSIS_PROC_TIME	BIGINT	total_col_synopsis_proc_time - Total column-organized synopsis table processing time monitor element
TOTAL_COL_SYNOPSIS_EXECUTIONS	BIGINT	total_col_synopsis_executions - Total column-organized synopsis table executions monitor element
COL_SYNOPSIS_ROWS_INSERTED	BIGINT	col_synopsis_rows_inserted - Column-organized synopsis table rows inserted monitor element
LOB_PREFETCH_WAIT_TIME	BIGINT	lob_prefetch_wait_time - LOB prefetch wait time monitor element
LOB_PREFETCH_REQS	BIGINT	lob_prefetch_reqs - LOB prefetcher request count monitor element
FED_ROWS_DELETED	BIGINT	fed_rows_deleted - Rows deleted by a federation system monitor element
FED_ROWS_INSERTED	BIGINT	fed_rows_inserted - Rows inserted by a federation system monitor element
FED_ROWS_UPDATED	BIGINT	fed_rows_updated - Rows updated by a federation system monitor element
FED_ROWS_READ	BIGINT	fed_rows_read - Rows read by a federation system monitor element
FED_WAIT_TIME	BIGINT	fed_wait_time - Time spent by a federation server monitor element
FED_WAITS_TOTAL	BIGINT	fed_waits_total - Total number of execution times for a federation server monitor element
APPL_SECTION_INSERTS	BIGINT	appl_section_inserts - Insert count for SQL workspace monitor element
APPL_SECTION_LOOKUPS	BIGINT	appl_section lookups - SQL workspace access count monitor element
ADM_OVERFLOWS	BIGINT	Reserved for future use.
ADM_BYPASS_ACT_TOTAL	BIGINT	Reserved for future use.

Usage notes

Use the MON_GET_ROUTINE table function to identify the most expensive routines on the database server.

This table function returns one row of metrics for each routine or trigger and each member that matches the input arguments. Input argument values are complementary. Metrics returned are aggregates of all executions of the routine on that member. No aggregation across members is performed. However, an aggregation across members is possible through SQL queries (as shown in the Examples section). If a routine executes subroutines, the work done in the subroutines is included in the metrics of the parent routine.

Routine monitoring data collection must be explicitly enabled using the mon_rtn_data database configuration parameter. If this configuration parameter is set to NONE, no information is returned.

The counters and time-spent monitor elements returned by this table function are controlled with the COLLECT REQUEST METRICS clause on service classes and the mon_req_metrics database configuration parameter at the database level. If neither control is enabled, the counters and time-spent monitor elements reported are 0.

When the package for a dynamically prepared compound SQL statements is removed from the package cache, information for this routine is no longer reported by MON_GET_ROUTINE function. Similarly, when a routine or trigger is dropped, information about the routine or trigger is no longer reported.

Any routines that were not executed during the previous 24 hour period are pruned from memory and not returned.

Examples

List the highest CPU consuming routines of any type in module MOD1.

SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME, SPECIFIC_NAME, TOTAL_CPU_TIME 
   FROM TABLE(MON_GET_ROUTINE(NULL, NULL, 'MOD1', NULL, NULL)) 
   AS T ORDER BY TOTAL_CPU_TIME DESC

returns

ROUTINE_TYPE ROUTINE_SCHEMA  ROUTINE_NAME                SPECIFIC_NAME       TOTAL_CPU_TIME
------------ --------------- --------------------------- ------------------- --------------
F            DRICARD         FUNCTION3                   FUNCTION3                    19425
F            DRICARD         FUNCTION4                   FUNCTION4                     5780
P            DRICARD         P1                          SQL120801142627900            4685
C            SYSIBMINTERNAL  COMPILED_ANON_BLOCK_INVOKE  SQL120801153841490            3471
P            SYSPROC         SYSINSTALLOBJECTS           SYSINSTALLOBJECTS             1158
F            DRICARD         FUNCTION1                   FUNCTION1                     2632
F            DRICARD         FUNCTION2                   FUNCTION2                     2029

  7 record(s) selected.

List aggregate monitor data for all stored procedures.

SELECT * FROM TABLE(MON_GET_ROUTINE('P', NULL, NULL, NULL, -2)) AS T

List aggregate monitor data for routines of all types.

SELECT * FROM TABLE(MON_GET_ROUTINE(NULL, NULL, NULL, NULL, -2)) AS T

List aggregate monitor data for all procedures named PROC1 in schema TEST.
```
SELECT * FROM TABLE(MON_GET_ROUTINE('P', 'TEST', NULL, 'PROC1', -2)) AS T
```
List aggregate monitor data for overloaded procedure PROC1 with specific name PROC1_OVERLOAD in schema TEST.
```
SELECT * FROM TABLE(MON_GET_ROUTINE('SP', 'TEST', NULL, 'PROC1_OVERLOAD', -2)) 
   AS T
```

List aggregate monitor data for the anonymous block with executable id x'0100000000000000520100000000000001000000010020120822205618607103'.

SELECT * FROM TABLE(MON_GET_ROUTINE
   ('A', 'MYSCHEMA', NULL, 'SQL181500027522310', -1)) AS T 
   WHERE 
   DYN_COMPOUND_EXEC_ID = x'0100000000000000520100000000000001000000010020120822205618607103'