Logical data groups and event monitor output tables

Monitor elements that are frequently used together are grouped into logical data groups. Event monitors that write to tables generally produce one output table for each logical data group of monitor elements that they capture.

The following table presents the default target table names by event type.
Table 1. Write-to-table event monitor logical data groups
Event type Logical data group Information in logical group Name of table to which elements belonging to logical group are written
DEADLOCKS1 event_connheader Connection metadata. CONNHEADER_evmon-name
event_deadlock Deadlock data. DEADLOCK_evmon-name
event_dlconn Applications and locks that are involved in deadlock. DLCONN_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
DEADLOCKS WITH DETAILS1 event_connheader Connection metadata. CONNHEADER_evmon-name
event_deadlock Deadlock data. DEADLOCK_evmon-name
event_detailed_dlconn Applications that are involved in deadlock. DLCONN_evmon-name
dllock Locks that are involved in deadlock. DLLOCK_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
DEADLOCKS WITH DETAILS HISTORY1 event_connheader Connection metadata. CONNHEADER_evmon-name
event_deadlock Deadlock data. DEADLOCK_evmon-name
event_detailed_dlconn Applications that are involved in deadlock. DLCONN_evmon-name
dllock Locks that are involved in deadlock. DLLOCK_evmon-name
event_stmt List of the previous statements in the unit of work. STMTHIST_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
DEADLOCKS WITH DETAILS HISTORY VALUES1 event_connheader Connection metadata. CONNHEADER_evmon-name
event_deadlock Deadlock data. DEADLOCK_evmon-name
event_detailed_dlconn Applications that are involved in deadlock. DLCONN_evmon-name
dllock Locks that are involved in deadlock. DLLOCK_evmon-name
event_stmt_history List of the previous statements in the unit of work. STMTHIST_evmon-name
STMTVALS Input data values of statements in STMTHIST table. STMTVALS_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
STATEMENT event_connheader Connection metadata. CONNHEADER_evmon-name
event_stmt Statement data. STMT_evmon-name
event_subsection Statement data that is specific to subsection. SUBSECTION_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
TRANSACTIONS3 event_connheader Connection metadata. CONNHEADER_evmon-name
event_xact Transaction data. XACT_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
CONNECTIONS event_connheader Connection metadata. CONNHEADER_evmon-name
event_conn Connection data. CONN_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
event_connmemuse Memory pool metadata. CONNMEMUSE_evmon-name
DATABASE event_db Database manager data. DB_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
event_dbmemuse Memory pool metadata. DBMEMUSE_evmon-name
BUFFERPOOLS event_bufferpool Buffer pool data. BUFFERPOOL_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
TABLESPACES event_tablespace Table space data. TABLESPACE_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
TABLES event_table Table data. TABLE_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
ACTIVITIES event_activity Activities that completed executing or were captured in progress. ACTIVITY_evmon-name
event_activitystmt Statement information for activities that are statements. ACTIVITYSTMT_evmon-name
event_activityvals Input data values for activities that have them. The following data types are not reported: CLOB, REF, BOOLEAN, STRUCT, DATALINK, LONG VARGRAPHIC, LONG, XMLLOB, and DBCLOB. ACTIVITYVALS_evmon-name
activity_metrics Activities metrics. ACTIVITYMETRICS_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
STATISTICS event_scstats Statistics that are computed from the activities that executed within each service class, work class, or workload in the system. SCSTATS_evmon-name
event_superclassmetrics logical data group SUPERCLASSMETRICS_evmon-name
event_superclassstats logical data group SUPERCLASSSTATS_evmon-name
event_wcstats WCSTATS_evmon-name
event_wlstats WLSTATS_evmon-name
event_histogrambin HISTOGRAMBIN_evmon-name
event_qstats QSTATS_evmon-name
event_scmetrics SCMETRICS_evmon-name
event_wlmetrics WLMETRICS_evmon-name
event_osmetrics Statistics for operating system resources. OSMETRICS_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
THRESHOLD VIOLATIONS event_thresholdviolations List of thresholds that were violated and the times of violations. THRESHOLDVIOLATIONS_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
LOCKING lock Summary lock wait, lock timeout or deadlock event information. LOCK_EVENTevmon-name
lock_participants Information about lock participants. LOCK_PARTICIPANTS_evmon-name
lock_participant_activities Activity data for each lock participant. LOCK_PARTICIPANT_ACTIVITIES_evmon-name
lock_activity_values Details about the specific data being processed by a specific activity. LOCK_ACTIVITY_VALUES_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
PACKAGE CACHE pkgcache Summary package cache event information. This information includes detailed metrics in XML format in the METRICS column. PKGCACHE_EVENTevmon-name
pkgcache_metrics Table containing the same metrics that are included in the METRICS column of the PKGCACHE table. PKGCACHE_METRICS_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
UNIT OF WORK uow Summary unit of work event information. This information includes detailed metrics in XML format in the METRICS column. UOW_EVENTevmon-name
uow_metrics Table containing the same metrics that are included in the METRICS column of the PKGCACHE table. UOW_METRICS_evmon-name
uow_package_list Package list detail information. 4 UOW_PACKAGE_LIST_evmon-name
uow_executable_list Executable list information.4 UOW_EXECUTABLE_LIST_evmon-name
CONTROL2 Event monitor metadata. CONTROL_evmon-name
1
This option has been deprecated and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.
2
The CONTROL logical group consists of selected elements from one or more of the event_dbheader, event_start and event_overflow logical data groups.
3
This option has been deprecated and might be removed in a future release. Use the CREATE EVENT MONITOR FOR UNIT OF WORK statement to monitor transaction events.
4
Unless you explicitly specify which output tables to create for the unit of work event monitor, this table is included by default. If you do not set the configuration parameter for collecting the related information (mon_uow_pkglist or mon_uow_execlist) to ON, the table is created, but it contains no data.
The following logical data groups are not collected for write-to-table event monitors:
  • log_stream_header
  • log_header
  • dbheader (only the conn_time monitor element is collected)
The data type of each column in an event monitor table corresponds to the data type of the monitor element represented by the column. The following table contains a set of data type mappings that correspond the original system monitor data types of the monitor elements (found in sqlmon.h file) to the SQL data types of the table columns.
Table 2. System Monitor Data Type Mappings
System monitor data type SQL data type
SQLM_TYPE_DOUBLE DOUBLE
SQLM_TYPE_STRING CHAR[n], VARCHAR[n], CLOB[n]
SQLM_TYPE_U8BIT and SQLM_TYPE_8BIT SMALLINT, INTEGER, or BIGINT
SQLM_TYPE_U16BIT and SQLM_TYPE_16BIT SMALLINT, INTEGER, or BIGINT
SQLM_TYPE_U32BIT and SQLM_TYPE_32BIT INTEGER or BIGINT
SQLM_TYPE_U64BIT and SQLM_TYPE_64BIT BIGINT
SQLM_TIMESTAMP TIMESTAMP
SQLM_TIME BIGINT
SQLCA: SQLERRMC VARCHAR[72]
SQLCA: SQLSTATE CHAR[5]
SQLCA: SQLWARN CHAR[11]
SQLCA: other fields INTEGER or BIGINT
SQLM_TYPE_HANDLE BLOB[n]
Note:
  1. All columns are NOT NULL.
  2. Because the performance of tables with CLOB columns is inferior to tables that have VARCHAR columns, consider using the TRUNC keyword when specifying the stmt evmGroup (or dlconn evmGroup, when using deadlocks with details).
  3. SQLM_TYPE_HANDLE is used to represent the compilation environment handle object.