CREATE EVENT MONITOR (unit of work) statement

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).

Authorization

Syntax

>>-CREATE EVENT MONITOR--event-monitor-name--------------------->

>----FOR UNIT OF WORK------------------------------------------->

>----WRITE TO UNFORMATTED EVENT TABLE--+-------------------------------------------+---->
                                       '-(--| unformatted-event-table-options |--)-'     

   .-AUTOSTART---.   
>--+-------------+---------------------------------------------><
   '-MANUALSTART-'   

unformatted-event-table-options

   .--------------------------------------------.   
   V  (1)   (2)                                 |   
|----------------+-TABLE--table name----------+-+---------------|
                 +-IN--tablespace name--------+     
                 | .-PCTDEACTIVATE--100-----. |     
                 '-+-PCTDEACTIVATE--integer-+-'     

1. Each unformatted event table option can be specified a maximum of one time (SQLSTATE 42613).
2. Clauses can be separated with a space or a comma.

Description

event-monitor-name

Name of the event monitor. This is a one-part name. It is an SQL identifier (either ordinary or delimited). The event-monitor-name must not identify an event monitor that already exists in the catalog (SQLSTATE 42710).

FOR

Introduces the type of event to record.

UNIT OF WORK

Specifies that this passive event monitor will record an event whenever a unit of work is completed (that is, whenever there is a commit or rollback).

The creation of the unit of work event monitor does not indicate that the unit of work data will be collected immediately. The actual unit of work event of interest is controlled at the workload level.

WRITE TO

Specifies the target for the data.

UNFORMATTED EVENT TABLE

Specifies that the target for the event monitor is an unformatted event table. The unformatted event table is used to store collected unit of work event monitor data. Data is stored in its original binary format within an inlined BLOB column. The BLOB column can contain multiple binary records of different types. The data in the BLOB column is not in a readable format and requires conversion, through use of the db2evmonfmt Java™-based tool, EVMON_FORMAT_UE_TO_XML table function, or EVMON_FORMAT_UE_TO_TABLES procedure, into a consumable format such as an XML document or a relational table.

(unformatted-event-table-options)

Identifies the unformatted event table. If a value for unformatted-event-table-options is not specified, CREATE EVENT MONITOR processing proceeds as follows:

• A derived table name is used (described below).
• A default table space is chosen (described below).
• PCTDEACTIVATE is set to 100.

TABLE table-name

Specifies the name of the unformatted event table. If a name is not provided, the unqualified name is equal to the event-monitor-name, that is, the unformatted event table will be named after the event monitor.

Note the following:

• The unformatted event table is created when the CREATE EVENT MONITOR FOR UNIT OF WORK statement executes, if it doesn't already exist.
• During CREATE EVENT MONITOR FOR UNIT OF WORK processing, if an unformatted event table is found to have already been defined for use by another event monitor, the CREATE EVENT MONITOR FOR UNIT OF WORK statement fails, and an error is passed back to the application program. An unformatted event table is defined for use by another event monitor if the unformatted event table name matches a value found in the SYSCAT.EVENTTABLES catalog view. If the unformatted event table exists and is not defined for use by another event monitor, then no table is created, any other table unformatted-event-table-options parameters are ignored, and processing continues. A warning is passed back to the application program.
• Dropping the event monitor will not drop the unformatted event table. The associated unformatted event tables must be manually dropped after the event monitor is dropped.
• The unformatted event tables must be manually pruned.

IN tablespace-name

Specifies the table space in which the unformatted event table is to be created. The CREATE EVENT MONITOR FOR UNIT OR WORK statement does not create table spaces.

If a table space name is not provided, the table space is chosen using the same process as when a table is created without a table space name using CREATE TABLE.

Since the page size affects the INLINE LOB lengths used, consider specifying a table space with as large a page size as possible in order to improve the INSERT performance of the event monitor.

PCTDEACTIVATE integer

If a unformatted event table is being created in a DMS table space, the PCTDEACTIVATE parameter specifies how full the table space must be before the event monitor automatically deactivates. The specified value, which represents a percentage, can range from 0 to 100. The default value is 100 (meaning that the event monitor deactivates when the table space becomes completely full). If the table space has auto-resize enabled, then it is suggested that PCTDEACTIVATE be set to 100. This option is ignored for SMS table spaces.

AUTOSTART

Specifies that the event monitor is to be automatically activated whenever the database partition on which the event monitor runs is activated. This is the default behavior of the unit of work event monitor.

MANUALSTART

Specifies that the event monitor must be activated manually using the SET EVENT MONITOR STATE statement. After a MANUALSTART event monitor has been activated, it can be deactivated only by using the SET EVENT MONITOR STATE statement or by stopping the instance.

Notes

• Event data is inserted into the unformatted event table into an inlined BLOB data column. Normally, BLOB data is stored in a separate LOB table space and can experience additional performance overhead as a result. When inlined into the data page of the base table, the BLOB data does not experience this overhead. The DB2® database manager will automatically inline the BLOB data portion of an unformatted event table record if the size of the BLOB data is less than the table space page size minus the record prefix. Therefore to achieve high efficiency and application throughput, it is suggested that you create the event monitor in as large a table space as possible up to and including a 32 KB table space and associated bufferpool.

  Example

  The lock event monitor currently has the following two record types:

  • Application Info Record
  • Application Activity Record

  Application Info Record = maximum size 3.5 KB

  Application Activity Record = 3 KB + SQL statement text size (where SQL statement text size is max 2 MB)

  The Application Info Record is very small and should always be inlined as long as a 4 KB page size is being used. The Application Activity Record will be inlined based on the following formula:

  Application Activity Record < inline length (Pagesize - overhead non-LOB columns (0.5 KB))
    3 KB + SQL statement text < inline length (Pagesize - overhead non-LOB columns (0.5 KB))
  
           SQL statement text < Pagesize - nonLOB overhead (1 KB) - 3 KB
           SQL statement text < 16 KB - 1 KB - 3 KB
                              < 12 KB

  Therefore, when using a 16 KB page size, the lock event monitor records will only be inlined if the SQL statement being captured is less than 12 KB in size.

• Create only one unit of work event monitor per database and not create multiple unit of work event monitors on the same database.
• In a partitioned database environment, data is only written to target unformatted event tables on the database partitions where their table spaces exist. 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.
• In a multi-member environment, data is only written to target unformatted event tables on the member where work occurs within the unit of work.
• In a partitioned database environment, if some target unformatted event tables do not reside on a database partition, but other target unformatted event tables do reside on that same database partition, only the data for the target unformatted event tables that do reside on that database partition is recorded.
• The unit of work event monitor is not affected by the unit or work event monitor switch. The unit of work event monitor switch is not changed when a unit or work event monitor is created, and the contents of the unit or work event monitor are not affected by changes to the unit of work event monitor switch.
• The FLUSH EVENT MONITOR statement does not cause an event to be written to the UOW Event Monitor.
• Creation of the unit of work event monitor does not cause events to be written to the event monitor. The unit of work event monitor must be activated with SET EVENT MONITOR STATE, and the unit of work data must be collected by either altering the appropriate workload to specify COLLECT UNIT OF WORK DATA or setting the mon_uow_data database configuration parameter to a value other than NONE.
• Create the unit of work event monitor in a table space with at least 8 KB page size to ensure that the event data is contained within the inlined BLOB column of the unformatted event table. If the BLOB column is not inlined, then the performance of writing and reading the events to the unformatted event table might not be efficient.

Examples

   CREATE EVENT MONITOR UOWEVMON
     FOR UNIT OF WORK
     WRITE TO UNFORMATTED EVENT TABLE 

   CREATE EVENT MONITOR UOWEVMON
     FOR UNIT OF WORK
     WRITE TO UNFORMATTED EVENT TABLE (TABLE GREG.UOWEVENTS)

   CREATE EVENT MONITOR UOWEVMON
     FOR UNIT OF WORK
     WRITE TO UNFORMATTED EVENT TABLE 
        (TABLE GREG.UOWEVENTS IN APPSPACE PCTDEACTIVATE 85)