Creating event monitors that write to unformatted event (UE) tables

If the performance of event monitor data collection is particularly important, you might choose to have your event monitor write its output to an unformatted event (UE) table. Most of the data written to a UE table is written as inline binary data, which allows for faster I/O as data is collected.

Another advantage of UE tables over regular tables for event monitors is that you generally do not have to be concerned at event monitor creation time with different options such as what buffer size to use, whether the event monitor is blocked or unblocked, or what types of data (logical groups) must be collected. However, because most of the data collected is in binary format, you must post-process the UE table to be able to examine the event data.

Note: The following features related to the UE tables are available:

You can use the procedure EVMON_UPGRADE_TABLES to upgrade the UE tables produced by event monitors in earlier releases. This capability lets you more easily retain event monitor data that was recoreded earlier.
All event monitors that can write their output to UE tables can also write to regular tables.
You can prune unneeded data from UE tables using the option PRUNE_UE_TABLE of the procedure EVMON_FORMAT_UE_TO_TABLES.

Before you begin

Keep the following considerations in mind when creating an event monitor that writes to an unformatted event table:

You need SQLADM or DBADM authority to create an event monitor that writes to a UE table.
Use a table space for your unformatted event tables that is optimized for performance. When you create the table space, keep the following guidelines in mind:
- Specify a page size (PAGESIZE) as large as possible, up to 32KB. A large page size ensures that the BLOB containing the event data can be written inline with the table row. If the page size is too small to allow the BLOB to be inlined, performance of the event monitor might be diminished. The database manager attempts to inline the event_data BLOB column in the unformatted event table, but this is not always possible. To check that the rows in the unformatted event table have been inlined, use the ADMIN_IS_INLINED function. If the rows have not been inlined, use the ADMIN_EST_INLINE_LENGTH functions to determine how much space the rows need.
- Specify the NO FILE CACHING SYSTEM option.
In a partitioned database environment, consider on which partitions the table space exists. If a table space for a target unformatted event table does not exist on some database partition, data for that target unformatted event table is ignored. This behavior allows users to choose a subset of database partitions for monitoring to be chosen, by creating a table space that exists only on certain database partitions.

About this task

The following event monitor types support the use of UE tables:

Unit of work
Package Cache
Locking

Note: Despite their name, unformatted event tables are still relational tables. The main difference between a UE table produced by, say, a locking event monitor and a regular table produced by a locking event monitor is that most of the data in a UE table is written in binary format in the EVENT_DATA column. See Unformatted event table column definitions for more information about the structure of UE tables.

Procedure

To create an event monitor that writes to a UE table:

Formulate a CREATE EVENT MONITOR statement, using the WRITE TO UNFORMATTED EVENT TABLE clause.
For example, to create a unit of work event monitor called uowmon, you might use a statement like the one that follows:
```
CREATE EVENT MONITOR uowmon FOR UNIT OF WORK
                     WRITE TO UNFORMATTED EVENT TABLE
```
By default, the name of the UE table that the event monitor creates is the same as the name of the event monitor.

To specify an alternative to the default table name, use the TABLE clause.

For example, if you want to have the UE table called myunitsofwork, construct the statement as follows:

CREATE EVENT MONITOR uowmon FOR UNIT OF WORK
                     WRITE TO UNFORMATTED EVENT TABLE
                     TABLE myunitsofwork

You can also specify the table space in which to store the UE table using the IN tablespace-name clause:

CREATE EVENT MONITOR uowmon FOR UNIT OF WORK
                     WRITE TO UNFORMATTED EVENT TABLE
                     TABLE myunitsofwork
                     IN mytablespace

CREATE EVENT MONITOR uowmon FOR UNIT OF WORK
                     WRITE TO UNFORMATTED EVENT TABLE
                     IN mytablespace

The first example places the UE table myunitsofwork in table space mytablespace; the second example places a UE table named uowmon (the default, as no table name is specified) in table space mytablespace.

By default, any event monitor that writes to a UE table is created to activate automatically on database activation. You can override this behaviour using the MANUALSTART clause:
```
CREATE EVENT MONITOR uowmon FOR UNIT OF WORK
                     WRITE TO UNFORMATTED EVENT TABLE
                     MANUALSTART
```
In the preceding example, the event monitor uowmon must always be activated manually, using the SET EVENT MONITOR STATE statement.

What to do next

By default, event monitors that were introduced in version 9.7 or later are created as AUTOSTART event monitors. They are activated automatically when the database is next activated, and on subsequent database activations thereafter. If you want to activate the event monitor immediately, before the next database activation, use the SET EVENT MONITOR STATE statement to manually start the event monitor. In addition for each of the locking, unit of work and package cache event monitors, you must also enable data collection.