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.
Keep the following considerations in mind when creating an event monitor that writes to an unformatted event table:
Before you begin
- 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.
The following event monitor types support the use of UE tables:
About this task
- Unit of work
- Package Cache
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.
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 TABLEBy 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 myunitsofworkYou 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
The first example places the UE table
CREATE EVENT MONITOR uowmon FOR UNIT OF WORK WRITE TO UNFORMATTED EVENT TABLE IN mytablespace
myunitsofworkin table space
mytablespace; the second example places a UE table named
uowmon(the default, as no table name is specified) in table space
- 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 MANUALSTARTIn the preceding example, the event monitor
uowmonmust always be activated manually, using the SET EVENT MONITOR STATE statement.
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.