DB2 Version 9.7 for Linux, UNIX, and Windows

Information written to relational tables for a package cache event monitor

Information written for a package cache event monitor from the EVMON_FORMAT_UE_TO_TABLES table function. This is also documented in the DB2EvmonPkgCache.xsd file.
Table 1. Information returned for a package cache event monitor: Table name: PKGCACHE_EVENT
Column Name Data Type Description
XMLID VARCHAR(1000) NOT NULL  
EVENT_ID BIGINT NOT NULL  
EVENT_TYPE VARCHAR(128) NOT NULL  
EVENT_TIMESTAMP TIMESTAMP NOT NULL  
MEMBER SMALLINT NOT NULL member - Database member monitor element
SECTION_TYPE CHAR(1) section_type - Section type indicator monitor element
INSERT_TIMESTAMP TIMESTAMP insert_timestamp - Insert timestamp monitor element
EXECUTABLE_ID VARCHAR(32) FOR BIT DATA executable_id - Executable ID monitor element
PACKAGE_SCHEMA VARCHAR(128) package_schema - Package schema monitor element
PACKAGE_NAME VARCHAR(128) package_name - Package name monitor element
PACKAGE_VERSION_ID VARCHAR(64) package_version_id - Package version monitor element
SECTION_NUMBER BIGINT section_number - Section number monitor element
EFFECTIVE_ISOLATION CHAR(2) effective_isolation - Effective isolation monitor element
NUM_EXECUTIONS BIGINT num_executions - Statement executions monitor element
NUM_EXEC_WITH_METRICS BIGINT num_exec_with_metrics - Number of executions with metrics collected monitor element
PREP_TIME BIGINT prep_time - Preparation time monitor element
LAST_METRICS_UPDATE TIMESTAMP last_metrics_update - Metrics last update timestamp monitor element
NUM_COORD_EXEC BIGINT num_coord_exec - Number of executions by coordinator agent monitor element
NUM_COORD_EXEC_WITH_METRICS BIGINT num_coord_exec_with_metrics - Number of executions by coordinator agent with metrics monitor element
STMT_TYPE_ID VARCHAR(32) stmt_type_id - Statement type identifier monitor element
QUERY_COST_ESTIMATE BIGINT query_cost_estimate - Query cost estimate monitor element
STMT_PKG_CACHE_ID BIGINT  
STMT_TEXT CLOB(2M) stmt_text - SQL statement text monitor element
COMP_ENV_DESC BLOB(10K) comp_env_desc - Compilation environment monitor element
METRICS BLOB(1M) XML document containing metrics-related monitor elements. The metrics in this document are the same as those described in the PKGCACHE_METRICS table that appears later in this topic. See Interfaces that return monitor data in XML documents for more information.
SECTION_ENV BLOB(150M) section_env - Section environment monitor element
Table 2. Information returned for a package cache event monitor: Table name: PKGCACHE_METRICS. The metrics in this table are the same as those returned in the METRICS monitor element in the PKGCACHE_EVENT table
Column Name Data Type Description
XMLID VARCHAR(1000) NOT NULL  
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
TOTAL_CPU_TIME BIGINT total_cpu_time - Total CPU time 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
DIRECT_READ_TIME BIGINT direct_read_time - Direct read time monitor element
DIRECT_WRITE_TIME BIGINT direct_write_time - Direct write time monitor element
LOCK_WAIT_TIME BIGINT lock_wait_time - Time waited on locks 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
LOCK_ESCALS BIGINT lock_escals - Number of lock escalations monitor element
LOCK_WAITS BIGINT lock_waits - Lock waits 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
DIRECT_READS BIGINT direct_reads - Direct reads from database monitor element
DIRECT_READ_REQS BIGINT direct_read_reqs - Direct read requests monitor element
DIRECT_WRITES BIGINT direct_writes - Direct writes to database monitor element
DIRECT_WRITE_REQS BIGINT direct_write_reqs - Direct write requests monitor element
POOL_DATA_L_READS BIGINT pool_data_l_reads - Buffer pool data logical reads monitor element
POOL_TEMP_DATA_L_READS BIGINT pool_temp_data_l_reads - Buffer pool temporary data logical reads monitor element
POOL_XDA_L_READS BIGINT pool_xda_l_reads - Buffer pool XDA data 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_INDEX_L_READS BIGINT pool_index_l_reads - Buffer pool index logical reads monitor element
POOL_TEMP_INDEX_L_READS BIGINT pool_temp_index_l_reads - Buffer pool temporary index logical reads monitor element
POOL_DATA_P_READS BIGINT pool_data_p_reads - Buffer pool data physical reads monitor element
POOL_TEMP_DATA_P_READS BIGINT pool_temp_data_p_reads - Buffer pool temporary data physical reads monitor element
POOL_XDA_P_READS BIGINT pool_xda_p_reads - Buffer pool XDA data 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_INDEX_P_READS BIGINT pool_index_p_reads - Buffer pool index physical reads monitor element
POOL_TEMP_INDEX_P_READS BIGINT pool_temp_index_p_reads - Buffer pool temporary index physical reads monitor element
POOL_DATA_WRITES BIGINT pool_data_writes - Buffer pool data writes monitor element
POOL_XDA_WRITES BIGINT pool_xda_writes - Buffer pool XDA data writes monitor element
POOL_INDEX_WRITES BIGINT pool_index_writes - Buffer pool index writes 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
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
DEADLOCKS BIGINT deadlocks - Deadlocks detected 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
LOCK_TIMEOUTS BIGINT lock_timeouts - Number of lock timeouts 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
TOTAL_ROUTINE_TIME BIGINT total_routine_time - Total routine time monitor element
TOTAL_ROUTINE_INVOCATIONS BIGINT total_routine_invocations - Total routine invocations monitor elements
COORD_STMT_EXEC_TIME BIGINT coord_stmt_exec_time - Execution time for statement by coordinator agent monitor element
STMT_EXEC_TIME BIGINT stmt_exec_time - Statement execution time 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_ROUTINE_NON_SECT_TIME BIGINT total_routine_non_sect_time - Non-section routine execution time monitor elements
TOTAL_ROUTINE_NON_SECT_PROC_TIME BIGINT total_routine_non_sect_proc_time - Non-section processing time monitor element
FCM_TQ_RECV_WAIT_TIME BIGINT fcm_tq_recv_wait_time - FCM table queue received wait time monitor element
FCM_MESSAGE_RECV_WAIT_TIME BIGINT fcm_message_recv_wait_time - FCM message received wait time monitor element
FCM_TQ_SEND_WAIT_TIME BIGINT fcm_tq_send_wait_time - FCM table queue send wait time monitor element
FCM_MESSAGE_SEND_WAIT_TIME BIGINT fcm_message_send_wait_time - FCM message send wait time monitor element
AUDIT_FILE_WRITE_WAIT_TIME BIGINT audit_file_write_wait_time - Audit file write wait time monitor element
AUDIT_FILE_WRITES_TOTAL BIGINT audit_file_writes_total - Total audit files written monitor element
AUDIT_SUBSYSTEM_WAIT_TIME BIGINT audit_subsystem_wait_time - Audit subsystem wait time monitor element
AUDIT_SUBSYSTEM_WAITS_TOTAL BIGINT audit_subsystem_waits_total - Total audit subsystem waits monitor element
DIAGLOG_WRITE_WAIT_TIME BIGINT diaglog_write_wait_time - Diagnostic log file write wait time monitor element
DIAGLOG_WRITES_TOTAL BIGINT diaglog_writes_total - Total diagnostic log file writes monitor element
FCM_MESSAGE_SENDS_TOTAL BIGINT fcm_message_sends_total - Total FCM message sends monitor element
FCM_MESSAGE_RECVS_TOTAL BIGINT fcm_message_recvs_total - Total FCM message receives monitor element
FCM_MESSAGE_SEND_VOLUME BIGINT fcm_message_send_volume - FCM message send volume monitor element
FCM_MESSAGE_RECV_VOLUME BIGINT fcm_message_recv_volume - FCM message received volume monitor element
FCM_TQ_SENDS_TOTAL BIGINT fcm_tq_sends_total - FCM table queue send total monitor element
FCM_TQ_RECVS_TOTAL BIGINT fcm_tq_recvs_total - FCM table queue receives total monitor element
FCM_TQ_SEND_VOLUME BIGINT fcm_tq_send_volume - FCM table queue send volume monitor element
FCM_TQ_RECV_VOLUME BIGINT fcm_tq_recv_volume - FCM table queue received volume monitor element
TQ_TOT_SEND_SPILLS BIGINT tq_tot_send_spills - Total number of table queue buffers overflowed monitor element
AUDIT_EVENTS_TOTAL BIGINT audit_events_total - Total audit events monitor element
TOTAL_APP_SECTION_EXECUTIONS BIGINT total_app_section_executions - Total application section executions 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
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
Parent topic: Monitoring package cache events
Related concepts:
Interfaces that return monitor data in XML documents