DB2 10.5 for Linux, UNIX, and Windows

CREATE EVENT MONITOR (unit of work) statement

The CREATE EVENT MONITOR (unit of work) statement creates an event monitor that will record events when a unit of work completes.

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

The privileges held by the authorization ID of the statement must include one of the following authorities:
  • DBADM authority
  • SQLADM authority

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE EVENT MONITOR--event-monitor-name--------------------->

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

>----WRITE TO--+-TABLE--| formatted-event-table-info |-----------------------+---->
               '-UNFORMATTED EVENT TABLE--+--------------------------------+-'     
                                          '-(--| target-table-options |--)-'       

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

formatted-event-table-info

|--+---------------------------------------------------+--------|
   | .-,---------------------------------------------. |   
   | V                                               | |   
   '---evm-group--+--------------------------------+-+-'   
                  '-(--| target-table-options |--)-'       

target-table-options

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

Notes:
  1. Each 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.
TABLE
Indicates that the target for the event monitor data is a set of database tables. The event monitor separates the data stream into one or more logical data groups and inserts each group into a separate table. Data for groups having a target table is kept, whereas data for groups not having a target table is discarded. Each monitor element contained within a group is mapped to a table column with the same name. Only elements that have a corresponding table column are inserted into the table. Other elements are discarded.
formatted-event-table-info
Defines the target formatted event tables for the event monitor. This clause should specify each grouping that is to be recorded. However, if no evm-group clauses are specified, all groups for the event monitor type are recorded.

For more information about logical data groups, refer to ../../com.ibm.db2.luw.admin.mon.doc/doc/r0059240.html .

evm-group
Identifies a logical data group for which a target table is being defined. The value depends upon the type of event monitor, as shown in the following table:
Type of Event Monitor evm-group Value
Unit of work
  • UOW
  • UOW_METRICS
  • UOW_PACKGE_LIST
  • UOW_EXECUTABLE_LIST
  • CONTROL
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.
target-table-options
Identifies options for the target table. If a value for target-table-options is not specified, CREATE EVENT MONITOR processing proceeds as follows:
  • A derived table name is used (as explained in the description for TABLE table-name).
  • A default table space is chosen using the same process as when a table is created without a table space name using CREATE TABLE.
  • PCTDEACTIVATE is set to 100.
TABLE table-name
Specifies the name of the target table. The target table must be a non-partitioned table. If the name is unqualified, the table schema defaults to the value in the CURRENT SCHEMA special register. For an 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. For a formatted event table if no name is provided, the unqualified name is derived from evm-group and event-monitor-name as follows:
   substring(evm-group CONCAT '_'
     CONCAT event-monitor-name,1,128)
IN tablespace-name
Specifies the table space in which the 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 table for the event monitor is being created in an automatic storage (non-temporary) or 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, where 100 means that the event monitor deactivates when the table space becomes completely full. The default value assumed is 100 if PCTDEACTIVATE is not specified. This option is ignored for SMS table spaces.
Important: If the target table space has auto-resize enabled, set PCTDEACTIVATE parameter to 100. Alternatively, omit this clause entirely to have the default of 100 apply. Otherwise, the event monitor might deactivate unexpectedly if the table space reaches the threshold specified by PCTDEACTIVTATE before the table space is automatically resized.
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

Examples