Target tables, control tables, and event monitor table management

You can define an event monitor so that it stores its event records in SQL tables. To do this, use the CREATE EVENT MONITOR statement with the WRITE TO TABLE clause.

When you create a write-to-table event monitor, the event monitor creates target tables to store records for each of the logical data groups returning data. In each table, the column names match the monitor element names that they represent. By default, the event monitor creates the tables in the event monitor creator's schema and names the tables by concatenating their corresponding logical data group name to the event monitor name.

For example, consider the following statement, which creates an event monitor that captures STATEMENTS events:

CREATE EVENT MONITOR test 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. Tables representing logical data groups that are specific to individual event types are created, along with a control table for every write-to-table event monitor. For the event monitor test, created by user riihi, the database manager creates the following tables:

riihi.connheader_test
riihi.stmt_test
riihi.subsection_test
riihi.control_test

The first three tables correspond to each of the logical data groups event_connheader, event_stmt, and event_subsection. The last table, riihi.control_test, is the control table. The control table contains event monitor metadata, specifically, from the event_start, event_dbheader (conn_time monitor element only), and event_overflow logical data groups.

Monitor elements are written to the overflow group only for non-blocked event monitors. With 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 is activated, it acquires an IN or IX table lock on each target table to prevent the table 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, to run a utility), 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.

You must manually prune write-to-table event monitor target tables, including the unformatted event (UE) tables. On highly active systems, event monitors can quickly fill disk space because of the high volume of data that they record. Unlike defining event monitors that write to files or named pipes, you can define write-to-table event monitors to record information fromonly certain logical data groups or monitor elements. You can use this feature to collect only the data that is relevant to your purposes and reduce the volume of data that event monitors generate. For example, the following statement defines an event monitor that captures connection events only from the event_conn logical data group and includes only the lock_waits monitor element:

CREATE EVENT MONITOR conn_monitor FOR CONNECTIONS WRITE TO TABLE
                     CONN(INCLUDES(lock_waits))

You might not want to have the target tables for an event monitor in the default schema, with default table names, in the default table space. If you anticipate high volumes of monitoring data, you might want the target tables to exist in their own table space. You can specify the schema, table, and table space names for the CREATE EVENT MONITOR statement. The schema name and table name form a derived name for the table. You can add the table space name after the table name by using the optional IN clause. Unlike the target tables, which the DB2 database manager automatically creates, a table space must exist if it you include it in an event monitor definition. If you do not specify a table space, a table space for which you have USE privileges is assigned.

A target table can be used by only a single event monitor. If you define a target table for another event monitor or if it cannot be created for any other reason, the CREATE EVENT MONITOR statement fails.

The table space name can be added after the table name with the optional IN clause. Unlike the target tables, which the DB2 database manager 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 is active only on database partitions where the table space containing the event monitor table exists. If 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 command log file.

For increased performance in retrieving event monitor data, you can create indexes for the event tables. If you add table attributes such as triggers, relational integrity, and constraints, the event monitor ignores them.

For example, the following statement defines an event monitor that captures STATEMENTS events, using the event_connheader, event_stmt, and event_subsection logical data groups. Each of the three target tables has different schema, table and table space combinations:

CREATE EVENT MONITOR test FOR STATEMENTS
WRITE TO TABLE CONNHEADER,
STMT (TABLE mydept.statements),
SUBSECTION (TABLE subsections, IN mytablespace)

Assuming that the user riihi issued the previous statement, the derived names and table spaces of the target tables are as follows:

CONNHEADER: riihi.connheader_test in the default table space
STMT: mydept.statements in the default table space
SUBSECTION: riihi.subsections in the mytablespace table space

If a target table does not exist when the event monitor activates, activation continues and data that would otherwise be inserted into the target table is ignored. Correspondingly, if a monitor element does not have a dedicated column in the target table, it is ignored.

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 the percentage of table space capacity at which the event monitor is deactivated. You can specify this value in the PCTDEACTIVATE clause for the CREATE EVENT MONITOR statement. For SMS table spaces, the value is set to 100. If you enabled the autoresize feature for the target table space, you should set the PCTDEACTIVATE value 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.