You can define an event monitor to store its event records in SQL tables. To do this, use the CREATE EVENT MONITOR statement with the WRITE TO TABLE clause.
Upon the creation of a write-to-table event monitor, the database creates target tables to store records for each of the logical data groups returning data. By default, the database creates the tables in the event monitor creator's schema, and names the tables according to their corresponding logical data group and event monitor name. In each table, the column names match the monitor element names that they represent.
CREATE EVENT MONITOR foo FOR STATEMENTS WRITE TO TABLE
Event
monitors using the STATEMENTS event type collect data from the event_connheader,
event_stmt, and event_subsection logical data groups. In addition
to the tables representing logical data groups specific to individual
event types, a control table is created for every write-to-table event
monitor. So, for the event monitor foo, created by user riihi,
the database creates the following tables: Monitor elements are written to the event_overflow group only for non-blocked event monitors. In non-blocked event monitors, agents that generate events do not wait for the event buffers to be written to the table if the buffers are full. Instead, they discard monitor data coming from agents when data is coming faster than the event monitor can write the data. In this case, the event monitor records information in the control table to indicate that an overflow has taken place. Included in this information is the monitor element message, which in the event of an overflow, contains the text OVERFLOW:n, where n represents the number of event records that were discarded because the event buffers were full.
Whenever a write-to-table event monitor activates, it will acquire IN table locks on each target table in order to prevent them from being modified while the event monitor is active. Table locks are maintained on all tables while the event monitor is active. If exclusive access is required on any of the target tables (for example, when a utility is to be run), first deactivate the event monitor to release the table locks before attempting such access.
Each column name in a target table matches an event monitor element identifier. Any event monitor element that does not have a corresponding target table column is ignored.
CREATE EVENT MONITOR foo_lite FOR TRANSACTIONS WRITE TO TABLE
XACT(INCLUDES(lock_escal))
There are circumstances where it may not be desirable to have the event monitor's target tables residing in the default schema, with default table names, in the default table space. For instance, you may want the target tables to exist in their own table space if you are anticipating high volumes of monitoring data.
You can specify the schema, table, and table space names in the CREATE EVENT MONITOR statement. The schema name is provided along with the table name, forming a derived name for the table.
A target table can only be used by a single event monitor. If a target table is found to already be defined for another event monitor, or if it cannot be created for any other reason, the CREATE EVENT MONITOR statement will fail.
The table space name can be added after the table name with the optional IN clause. Unlike the target tables, which DB2® automatically creates, a table space must already exist if it is included in an event monitor definition. If no table space is specified, then a table space over which the definer has USE privileges will be assigned.
In a partitioned database environment, a write-to-table event monitor will only be active on database partitions where the table space containing the event monitor table exists. When the target table space for an active event monitor does not exist on a particular database partition, the event monitor will be deactivated on that database partition, and an error is written to the db2diag log file.
For increased performance in retrieving event monitor data, you can create indexes for the event tables. You can also add additional table attributes, such as triggers, relational integrity, and constraints. The event monitor will ignore them.
CREATE EVENT MONITOR foo FOR STATEMENTS
WRITE TO TABLE CONNHEADER,
STMT (TABLE mydept.statements),
SUBSECTION (TABLE subsections, IN mytablespace)
Assuming
that the above statement was issued by the user 'riihi', the derived
names and table spaces of the target tables are as follows: For active write-to-table event monitors there is a risk that the table spaces storing event records can reach their capacity. To control this risk for DMS table spaces you can define at which percentage of table space capacity the event monitor will deactivate. This can be declared in the PCTDEACTIVATE clause in the CREATE EVENT MONITOR statement.
For SMS table spaces, the value is set to 100. It is recommended that when the target table space has auto-resize enabled the PCTDEACTIVATE be set to 100.
In a non-partitioned database environment, all write to table event monitors are deactivated when the last application terminates (and the database has not been explicitly activated). In a partitioned database environment, write to table event monitors are deactivated when the catalog partition deactivates.
Event type | Target table names | Available information |
---|---|---|
DEADLOCKS1 | CONNHEADER | Connection metadata |
DEADLOCK | Deadlock data | |
DLCONN | Applications and locks involved in deadlock | |
CONTROL | Event monitor metadata | |
DEADLOCKS WITH DETAILS1 | CONNHEADER | Connection metadata |
DEADLOCK | Deadlock data | |
DLCONN | Applications involved in deadlock | |
DLLOCK | Locks involved in deadlock | |
CONTROL | Event monitor metadata | |
DEADLOCKS WITH DETAILS HISTORY1 | CONNHEADER | Connection metadata |
DEADLOCK | Deadlock data | |
DLCONN | Applications involved in deadlock | |
DLLOCK | Locks involved in deadlock | |
STMTHIST | List of the previous statements in the unit of work | |
CONTROL | Event monitor metadata | |
DEADLOCKS WITH DETAILS HISTORY VALUES1 | CONNHEADER | Connection metadata |
DEADLOCK | Deadlock data | |
DLCONN | Applications involved in deadlock | |
DLLOCK | Locks involved in deadlock | |
STMTHIST | List of the previous statements in the unit of work | |
STMTVALS | Input Data values of statements in STMTHIST table | |
CONTROL | Event monitor metadata | |
STATEMENT | CONNHEADER | Connection metadata |
STMT | Statement data | |
SUBSECTION | Statement data specific to subsection | |
CONTROL | Event monitor metadata | |
TRANSACTIONS2 | CONNHEADER | Connection metadata |
XACT | Transaction data | |
CONTROL | Event monitor metadata | |
CONNECTIONS | CONNHEADER | Connection metadata |
CONN | Connection data | |
CONTROL | Event monitor metadata | |
CONNMEMUSE | Memory pool metadata | |
DATABASE | DB | Database manager data |
CONTROL | Event monitor metadata | |
DBMEMUSE | Memory pool metadata | |
BUFFERPOOLS | BUFFERPOOL | Buffer pool data |
CONTROL | Event monitor metadata | |
TABLESPACES | TABLESPACE | Tablespace data |
CONTROL | Event monitor metadata | |
TABLES | TABLE | Table data |
CONTROL | Event monitor metadata | |
ACTIVITIES | ACTIVITY | Activities that completed executing or were captured in progress. |
ACTIVITYSTMT | Statement information for activities that are statements. | |
ACTIVITYVALS | Input data values for activities that have them. The data types that can be reported excludes the following: CLOB, REF, BOOLEAN, STRUCT, DATALINK, LONG VARGRAPHIC, LONG, XMLLOB, and DBCLOB. | |
ACTIVITYMETRICS | Activities metrics | |
CONTROL | Event monitor metadata | |
STATISTICS | SCSTATS | Statistics computed from the activities that executed within each service class, work class, or workload in the system. |
WCSTATS | ||
WLSTATS | ||
HISTOGRAMBIN | ||
QSTATS | ||
CONTROL | Event monitor metadata | |
THRESHOLD VIOLATIONS | THRESHOLDVIOLATIONS | List of thresholds that have been violated as well as the times of violations. |
CONTROL | Event monitor metadata |
System monitor data type | SQL data type |
---|---|
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] |