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.
- 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
- 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
- Unit of work
- Package Cache
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 behavior using the MANUALSTART
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.