CREATE EVENT MONITOR statement

Invocation

This statement can be embedded in an application program or entered 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

event-condition

evm-group-info

target-table-options

file-options

Notes:

• ¹ Other forms of these operators are also supported.
• ² Each clause can be specified once.
• ³ Clauses can be separated with a space or a comma.

Description

event-monitor-name

Name of the event monitor 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 exists in the catalog (SQLSTATE 42710).

FOR

Introduces the type of event to record.

DATABASE

Specifies that the event monitor records a database event when the last application disconnects from the database.

TABLES

Specifies that the event monitor records a table event for each active table when the last application disconnects from the database. For partitioned tables, a table event is recorded for each data partition of each active table. An active table is a table that changed since the first connection to the database.

DEADLOCKS

Note: This option was deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR LOCKING statement to monitor lock-related events, such as lock timeouts, lock waits, and deadlocks.

Specifies that the event monitor records a deadlock event whenever a deadlock occurs.

WITH DETAILS

Specifies that the event monitor is to generate a more detailed deadlock connection event for each application that is involved in a deadlock. This additional detail includes:

• Information about the statement that the application was running when the deadlock occurred, such as the statement text.
• The locks that are held by the application when the deadlock occurred. In a partitioned database environment, includes only those locks that are held on the database partition on which the application was waiting for its lock when the deadlock occurred. For partitioned tables, includes the data partition identifier.

HISTORY

Specifies that the event monitor data also includes:

• The history of all statements in the current unit of work at the participating node (including WITH HOLD cursors that are opened in previous units of work). SELECT statements that are entered at the uncommitted read (UR) isolation level are not included in the statement history.
• The statement compilation environment for each SQL statement in binary format (if available)

VALUES

Specifies that the event monitor data also includes:

• The data values used as input variables for each SQL statement. These data values do not include LOB data, long data, structured type data, or XML data.

Only one of the following commands: DEADLOCKS, DEADLOCKS WITH DETAILS, DEADLOCKS WITH DETAILS HISTORY, or DEADLOCKS WITH DETAILS HISTORY VALUES can be specified in a single CREATE EVENT MONITOR statement (SQLSTATE 42613).

TABLESPACES

Specifies that the event monitor records a table space event for each table space when the last application disconnects from the database.

BUFFERPOOLS

Specifies that the event monitor records a buffer pool event when the last application disconnects from the database.

CONNECTIONS

Specifies that the event monitor records a connection event when an application disconnects from the database.

STATEMENTS

Specifies that the event monitor records a statement event whenever an SQL statement finishes running.

TRANSACTIONS

Note: This option was deprecated. Its use is no longer recommended and might be removed in a future release. Use the CREATE EVENT MONITOR FOR UNIT OF WORK statement to monitor transaction events.

Specifies that the event monitor records a transaction event whenever a transaction completes (that is, whenever there is a commit or rollback operation).

WHERE event-condition

Defines a filter that determines which connections cause a CONNECTION, STATEMENT, or TRANSACTION event to occur. If the result of the event condition is TRUE for a particular connection, then that connection generates the requested events.

This clause is a special form of the WHERE clause that should not be confused with a standard search condition.

To determine whether an application generates events for a particular event monitor, the WHERE clause is evaluated:

• For each active connection when an event monitor is first turned on,
• Then, for each new connection to the database at connect time

The WHERE clause is not evaluated for each event.

If no WHERE clause is specified, all events of the specified event type are monitored.

The event-condition must not exceed 32 678 bytes in the database code page (SQLSTATE 22001).

APPL_ID

Specifies that the application ID of each connection is compared with the comparison-string to determine whether the connection generates CONNECTION, STATEMENT, or TRANSACTION events (whichever was specified).

AUTH_ID

Specifies that the authorization ID of each connection is compared with the comparison-string to determine whether the connection generates CONNECTION, STATEMENT, or TRANSACTION events (whichever was specified).

APPL_NAME

Specifies that the application program name of each connection is compared with the comparison-string to determine whether the connection generates CONNECTION, STATEMENT, or TRANSACTION events (whichever was specified).

The application program name is the first 20 bytes of the application program file name after the last path separator.

comparison-string

A string to be compared with the APPL_ID, AUTH_ID, or APPL_NAME of each application that connects to the database. comparison-string must be a string constant (that is, you cannot use host variables and other string expressions).

WRITE TO

Introduces 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 with a target table is kept, whereas data for groups not having a target table is discarded. Each monitor element that is 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.

evm-group-info

Defines the target table for a logical data group. This clause must be specified for each grouping that is to be recorded. However, if no evm-group-info clauses are specified, all groups for the event monitor type are recorded.

For more information about logical data groups, see Logical data groups and event monitor output tables .

evm-group

Identifies the 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:

Table 1. Values for evm-group based on the type of event monitor

Type of Event Monitor	evm-group value
Database	DB CONTROL1 DBMEMUSE
Tables	TABLE CONTROL1
Deadlocks	CONNHEADER DEADLOCK DLCONN CONTROL1
Deadlocks with details	CONNHEADER DEADLOCK DLCONN2 DLLOCK3 CONTROL1
Deadlocks with details history	CONNHEADER DEADLOCK DLCONN2 DLLOCK3 STMTHIST CONTROL1
Deadlocks with details history values	CONNHEADER DEADLOCK DLCONN2 DLLOCK3 STMTHIST STMTVALS CONTROL1
Table spaces	TABLESPACE CONTROL1
Buffer pools	BUFFERPOOL CONTROL1
Connections	CONNHEADER CONN CONTROL1 CONNMEMUSE
Statements	CONNHEADER STMT SUBSECTION4 CONTROL1
Transactions	CONNHEADER XACT CONTROL1
Activities	ACTIVITY ACTIVITYMETRICS ACTIVITYSTMT ACTIVITYVALS CONTROL1
Statistics	QSTATS SCSTATS SCMETRICS WCSTATS WLSTATS WLMETRICS HISTOGRAMBIN CONTROL1
Threshold Violations	THRESHOLDVIOLATIONS CONTROL1
Locking5	LOCK LOCK_PARTICIPANTS LOCK_PARTICIPANT_ACTIVITIES LOCK_ACTIVITY_VALUES CONTROL1
Package Cache5	PKGCACHE PKGCACHE_METRICS CONTROL1
Unit of Work5	UOW UOW_METRICS UOW_PACKGE_LIST UOW_EXECUTABLE_LIST CONTROL1
Change History	CHANGESUMMARY EVMONSTART TXNCOMPLETION DDLSTMTEXEC DBDBMCFG REGVAR UTILSTART UTILSTOP UTILPHASE UTILLOCATION CONTROL1
1 Logical data groups dbheader (conn_time element only), start, and overflow, are all written to the CONTROL group. The overflow group is written if the event monitor is non-blocked and events were discarded. 2 Corresponds to the DETAILED_DLCONN event. 3 Corresponds to the LOCK logical data groups that occur within each DETAILED_DLCONN event. 4 Created only for partitioned database environments. 5 Refers to the Formatted Event Table version of this event monitor type.

target-table-options

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

• A derived table name is used (see description for TABLE table-name).
• A default table space is chosen (see description for IN tablespace-name).
• All elements are included.
• PCTDEACTIVATE and TRUNC are not specified.

TABLE table-name

Specifies the name of the target table. The target table must be a nonpartitioned row-organized table. If the name is unqualified, the table schema defaults to the value in the CURRENT SCHEMA special register. 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

Defines the table space in which the table is to be created. If no table space name is provided, the table space is chosen by using the same process as when a table is created without a table space name that uses CREATE TABLE.

PCTDEACTIVATE integer

If a table for the event monitor is being created in the 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 0 - 100, where 100 means that the event monitor deactivates when the table space becomes full. The default value 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 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 that is specified by PCTDEACTIVTATE before the table space is automatically resized.

It is recommended that, when a target table space has auto-resize enabled, the PCTDEACTIVATE parameter be set to 100.

TRUNC

Specifies that the STMT_TEXT and STMT_VALUE_DATA columns are defined as VARCHAR(n), where n is the largest size that can fit into the table row. In this case, any data that is longer than n bytes is truncated. The following example illustrates how the value of n is calculated. Assume that:

• The table is created in a table space that uses 32K pages.
• The total length of all the other columns in the table equals 357 bytes.

In this case, the maximum row size for a table is 32677 bytes. Therefore, the element would be defined as VARCHAR(32316); that is, 32677 - 357 - 4. If TRUNC is not specified, the column is defined as CLOB(2M). STMT_TEXT is found in the STMT event group, the STMT_HISTORY event group, and the DLCONN event group (for deadlocks with details event monitors). STMT_VALUE_DATA is found in the DATA_VALUE event group.

INCLUDES

Specifies that the following elements are to be included in the table.

EXCLUDES

Specifies that the following elements are not to be included in the table.

element

Identifies a monitor element. Element information can be provided in one of the following forms:

• Specify no element information. In this case, all elements are included in the CREATE TABLE statement.
• Specify the elements to include in the form: INCLUDES (element1, element2, ..., elementn). Only table columns are created for these elements.
• Specify the elements to exclude in the form: EXCLUDES (element1, element2, ..., elementn). Only table columns are created for all elements except these.

Use the db2evtbl command to build a CREATE EVENT MONITOR statement that includes a complete list of elements for a group.

BLOCKED

Specifies that each agent that generates an event must wait for an event buffer to be written out to disk if the agent determines that both event buffers are full. Select BLOCKED to prevent event data loss. It is the default option.

PIPE

Specifies that the target for the event monitor data is a named pipe. The event monitor writes the data to the pipe in a single stream (that is, as if it were a single, infinitely long file). Writing the data to a pipe, an event monitor does not perform blocked writes. If the pipe buffer has no room, then the event monitor discards the data. It is the monitoring application's responsibility to read the data promptly if it wants to ensure no data loss.

pipe-name

The name of the pipe (FIFO on AIX®) to which the event monitor writes the data.

The naming rules for pipes are platform-specific.

Operating system	Naming rules
AIX HP-UX Solaris	Pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see description for path-name).
Linux®	Pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see description for path-name).
Windows	There is special syntax for a pipe name and as a result, absolute pipe names are required.

The existence of the pipe is not checked at event monitor creation time. It is the responsibility of the monitoring application to create and open the pipe for reading at the time that the event monitor is activated. If the pipe is not available now, then the event monitor turns itself off and logs an error. (That is, if the event monitor was activated at database start time as a result of the AUTOSTART option, then the event monitor logs an error in the system error log). If the event monitor is activated by the SET EVENT MONITOR STATE SQL statement, then that statement fails (SQLSTATE 58030).

In a Db2® pureScale® environment, the pipe-name must be on a shared file system whether the event monitor is LOCAL or GLOBAL. This requirement is to allow this event monitors to operate correctly if a member failover occurs. Failure to use a pipe-name on a shared file system results in an error (SQLSTATE 428A3) if the event monitor activates during a member failover.

FILE

Indicates that the target for the event monitor data is a file (or set of files). The event monitor writes out the stream of data as a series of eight character numbered files, with the extension evt. (For example, 00000000.evt, 00000001.evt, and 00000002.evt). The data should be considered to be one logical file even though the data is broken up into smaller pieces (that is, the start of the data stream is the first byte in the file00000000.evt; the end of the data stream is the last byte in the file nnnnnnnn.evt).

The maximum size of each file and the maximum number of files can be defined. An event monitor does not split a single event record across two files. However, an event monitor might write related records in two different files. It is the responsibility of the application that uses this data to track related information when it is processing the event files.

path-name

The name of the directory in which the event monitor writes the event files data. The path must be known at the server; however, the path itself might reside on another database partition (for example, an NFS-mounted file). A string constant must be used to specify the path-name.

The directory does not have to exist at CREATE EVENT MONITOR time. However, a check is made for the existence of the target path when the event monitor is activated. Then, if the target path does not exist, an error (SQLSTATE 428A3) is raised.

If an absolute path is specified, the specified path is the one used.

In environments other than Db2 pureScale, if a relative path (a path that does not start with the root) is specified, then the path relative to the DB2EVENT directory in the database directory is used. In a Db2 pureScale environment, if a relative path is specified, then the path relative to the directory owning the database in the database directory is used.

It is possible to specify two or more event monitors that have the same target path. However, when one of the event monitors is activated for the first time, and if the target directory is not empty, it is impossible to activate any of the other event monitors.

In a Db2 pureScale environment, the path-name must be on a shared file system whether this event monitor is LOCAL or GLOBAL. This requirement is to allow event monitors to operate correctly if a member failover occurs. Failure to use a path-name on a shared file system results in an error (SQLSTATE 428A3) if the event monitor activates during a member failover.

file-options

Specifies the options for the file format.

MAXFILES NONE

Specifies that the event monitor can create any number of event files. This is the default.

MAXFILES number-of-files

Specifies that the number of event monitor files that exist for a particular event monitor at any time is limited. Whenever an event monitor must create another file, it checks to make sure that the number of .evt files in the directory is less than number-of-files. If this limit is already reached, then the event monitor turns itself off.

If an application removes the event files from the directory after they are written, then the total number of files that an event monitor can produce can exceed number-of-files. This option is provided so that the event data does not use more than a specified amount of disk space.

MAXFILESIZE pages

Specifies that the size of each event monitor file has a limit. Whenever an event monitor writes a new event record to a file. It checks that the file does not grow to be greater than pages (in units of 4K pages). If the resulting file would be too large, then the event monitor switches to the next file. The default for this option is:

• Linux - 1000 4K pages
• UNIX - 1000 4K pages
• Windows - 200 4K pages

The number of pages must be greater than at least the size of the event buffer in pages. If this requirement is not met, then an error (SQLSTATE 428A4) is raised.

MAXFILESIZE NONE

Specifies that a file's size has no set limit. If MAXFILESIZE NONE is specified, then MAXFILES 1 must also be specified. This option means that one file contains all of the event data for a particular event monitor. In this case, the only event file is 00000000.evt.

BUFFERSIZE pages

Specifies the size of the event monitor buffers (in units of 4K pages). All event monitor file I/O is buffered to improve the performance of the event monitors. The larger the buffers, the less I/O is performed by the event monitor. Highly active event monitors have larger buffers than relatively inactive event monitors. When the monitor is started, two buffers of the specified size are allocated. Event monitors use double buffering to permit asynchronous I/O.

The default size of each buffer is four pages (two 16K buffers are allocated). The minimum size is one page. The maximum size of the buffers is limited by the value of the MAXFILESIZE parameter and the size of the monitor heap because the buffers are allocated from that heap. If many event monitors are being used at the same time, increase the size of the mon_heap_sz database manager configuration parameter.

Event monitors that write their data to a pipe also have two internal (non-configurable) buffers that are each one page in size. These buffers are also allocated from the monitor heap (MON_HEAP). For each active event monitor that has a pipe target, increase the size of the database heap by two pages.

BLOCKED

Specifies that each agent that generates an event must wait for an event buffer to be written out to disk if the agent determines that both event buffers are full. BLOCKED must be selected to prevent event data loss. This is the default option.

NONBLOCKED

Specifies that each agent that generates an event must not wait for the event buffer to be written out to disk if the agent determines that both event buffers are full. NONBLOCKED event monitors do not slow down database operations to the extent of BLOCKED event monitors. However, NONBLOCKED event monitors are subject to data loss on highly active systems.

APPEND

Specifies that if event data files exist when the event monitor is turned on, then the event monitor appends the new event data to the existing stream of data files. When the event monitor is reactivated, it resumes writing to the event files from when it was turned off. APPEND is the default option.

The APPEND option does not apply at CREATE EVENT MONITOR time, if there exists event data in the directory where the newly created event monitor is to write its event data.

REPLACE

Specifies that if event data files exist when the event monitor is turned on, then the event monitor erases all of the event files and start writing data to file 00000000.evt.

MANUALSTART

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

AUTOSTART

Specifies that the event monitor is to be automatically activated whenever the database partition on which the event monitor runs is activated.

ON DBPARTITIONNUM db-partition-number

Specifies the database partition (in a partitioned database environment) or member (in a Db2 pureScale environment) on which a file or pipe event monitor is to run. When the monitoring scope is defined as LOCAL, data is collected only on the specified partition or member. When the monitoring scope is defined as GLOBAL, all database partitions or members collect data and report to the database partition or member with the specified number. The I/O component physically runs on the specified database partition or member, write records to the specified file or pipe.

When Db2 pureScale is enabled, -1 can be specified, which allows the I/O component to run from any active member. Additionally, if the I/O component is no longer able to run on member, the event monitor is restarted with the I/O component that is activated on another available active member.

This clause is not valid for table event monitors. In a partitioned database environment, write-to-table event monitors runs and writes events on all database partitions where table spaces for target tables are defined.

In a Db2 pureScale environment, write-to-table event monitors record events on all active members.

If this clause is not specified and Db2 pureScale is not enabled, the currently connected database partition number (for the application) is used.

If this clause is not specified and Db2 pureScale is enabled, the I/O component is able to run on any currently connected database partition number.

LOCAL

The event monitor reports only on the database partition that is running. It gives a partial trace of the database activity. This is the default.

This clause is valid for file or pipe monitors. It is not valid for table event monitors.

GLOBAL

The event monitor reports on all database partitions. For a partitioned database, only DEADLOCKS event monitors can be defined as GLOBAL.

This clause is valid for file or pipe monitors. It is not valid for table event monitors.

Rules

• Each of the event types (DATABASE, TABLES, DEADLOCKs, ...) can be specified only in a particular event monitor definition.

Notes

• Event monitor definitions are recorded in the SYSCAT.EVENTMONITORS catalog view. The events themselves are recorded in the SYSCAT.EVENTS catalog view. The names of target tables are recorded in the SYSCAT.EVENTTABLES catalog view.
• A performance impact occurs by using DEADLOCKS WITH DETAILS rather than DEADLOCKS. When a deadlock occurs, the database manager requires extra time to record the extra deadlock information.
• A CONNHEADER event is normally written whenever a connection is established. However, if an event monitor is created only for DEADLOCKS WITH DETAILS, a CONNHEADER event is written the first time that the connection participates in a deadlock.
• In a database with multiple database partitions, the ON DBPARTITIONNUM clause can be used with FILE and PIPE event monitors having a DEADLOCKS event type to indicate where the event monitor itself resides. Information from other database partitions, if relevant, is sent to that location for processing.
• In a database with multiple database partitions, a deadlock event monitor receives information about applications that have locks in the deadlock from all the database partitions on which those locks existed. If the database partition to which the application is connected (the application coordinator partition) is not one of the participating database partitions, no information about a deadlock event is received from that database partition.
• The BUFFERSIZE parameter restricts the size of STMT, STMT_HISTORY, DATA_VALUE, and DETAILED_DLCONN events. If an STMT or a STMT_HISTORY event cannot fit within a buffer, it is truncated by truncating statement text. If a DETAILED_DLCONN event cannot fit within a buffer, it is truncated by removing locks. If it still cannot fit, statement text is truncated. If a DATA_VAL event cannot fit within a buffer, the data value is truncated.

  Event monitors WITH DETAILS HISTORY VALUES (and to a lesser extent, WITH DETAILS HISTORY) use a significant amount of monitor heap space to track statements and their data values, as described in the mon_heap_sz configuration parameter.

• If the database partition on which the event monitor is to run is not active, event monitor activation occurs when that database partition next activates.
• After an event monitor is activated, it behaves like an autostart event monitor until that event monitor is explicitly deactivated or the instance is recycled. That is, if an event monitor is active when a database partition is deactivated, and that database partition is later reactivated, the event monitor is also explicitly reactivated.
• Write to table event monitors General notes:
  • All target tables are created when the CREATE EVENT MONITOR statement runs.
  • If the creation of a table fails for any reason, an error is passed back to the application program, and the CREATE EVENT MONITOR statement fails.
  • A target table can be used by one event monitor only. During CREATE EVENT MONITOR processing, if a target table is found to be defined for use by another event monitor, the CREATE EVENT MONITOR statement fails and an error is passed back to the application program. A table is defined for use by another event monitor if the table name matches a value found in the SYSCAT.EVENTTABLES catalog view.
  • During CREATE EVENT MONITOR processing, if a table exists, but is not defined for use by another event monitor, no table is created, and processing continues. A warning is passed back to the application program.
  • Any table spaces must exist before the CREATE EVENT MONITOR statement runs. The CREATE EVENT MONITOR statement does not create table spaces.
  • If specified, the LOCAL and GLOBAL keywords are ignored. With WRITE TO TABLE event monitors, an event monitor output process or thread is started on each database partition in the instance, and each of these processes reports data only for the database partition on which it is running.
  • The following event types from the flat monitor log file or pipe format are not recorded by writing to table event monitors:
    • LOG_STREAM_HEADER
    • LOG_HEADER
    • DB_HEADER (Elements db_name and db_path are not recorded. The element conn_time is recorded in CONTROL).
  • In a partitioned database environment, data is only written to target tables on the database partitions where their table spaces exist. If a table space for a target table does not exist on some database partition, data for that target table is ignored. This behavior allows users to choose a subset of database partitions for monitoring, by creating a table space that exists only on certain database partitions.

    In a Db2 pureScale environment, data is written from every member.

    In a partitioned database environment, if some target tables do not reside on a database partition, but other target tables do reside on that database partition, only the data for the target tables that do reside on that database partition is recorded.

  • Users must manually prune all target tables.
  Table Columns:
  • Column names in a table match an event monitor element identifier. Any event monitor element that does not have a corresponding target table column is ignored.
  • Use the db2evtbl command to build a CREATE EVENT MONITOR command that includes a complete list of elements for a group.
  • The types of columns that are used for monitor elements correlate to the following mapping:

    SQLM_TYPE_STRING                     CHAR[n], VARCHAR[n] or CLOB(n)
                                         (If the data in the event monitor
                                         record exceeds n bytes,
                                         it is truncated.)
    SQLM_TYPE_U8BIT and SQLM_TYPE_8BIT   SMALLINT, INTEGER or BIGINT
    SQLM_TYPE_16BIT and SQLM_TYPE_U16BIT SMALLINT, INTEGER or BIGINT
    SQLM_TYPE_32BIT and SQLM_TYPE_U32BIT INTEGER or BIGINT
    SQLM_TYPE_U64BIT and SQLM_TYPE_64BIT BIGINT
    sqlm_timestamp                       TIMESTAMP
    sqlm_time(elapsed time)              BIGINT
    sqlca:
      sqlerrmc                           VARCHAR[72]
      sqlstate                           CHAR[5]
      sqlwarn                            CHAR[11]
      other fields                       INTEGER or BIGINT

  • Columns are defined to be NOT NULL.
  • Because the performance of tables with CLOB columns is inferior to tables that have VARCHAR columns, consider using the TRUNC keyword to specify the STMT evm-group value (or the DLCONN evm-group value when using the DEADLOCKS WITH DETAILS event type).
  • Unlike other target tables, the columns in the CONTROL table do not match monitor element identifiers. Columns are defined as follows:

    Column Name	Data type	Nullable	Description
    PARTITION_KEY	INTEGER	N	Distribution key (partitioned database only)
    PARTITION_NUMBER	INTEGER	N	Database partition number (partitioned database only)
    EVMONNAME	VARCHAR(128)	N	Name of the event monitor
    MESSAGE	VARCHAR(128)	N	Describes the nature of the MESSAGE_TIME column. For more information, see message - Control Table Message monitor element .
    MESSAGE_TIME	TIMESTAMP	N	Timestamp

  • In a partitioned database environment, the first column of each table is named PARTITION_KEY, is NOT NULL, and is of type INTEGER. This column is used as the distribution key for the table. The value of this column is chosen so that each event monitor process inserts data into the database partition on which the process is running. That is, insert operations run locally on the database partition where the event monitor process is running. On any database partition, the PARTITION_KEY field contains the same value. This means that if a database partition is dropped and data is redistributed, all data on the dropped database partition goes to one other database partition instead of being evenly distributed. Therefore, before you remove a database partition, consider deleting all table rows on that database partition.
  • In a partitioned database environment, a column that is named PARTITION_NUMBER can be defined for each table. This column is NOT NULL and is of type INTEGER. It contains the number of the database partition on which the data was inserted. Unlike the PARTITION_KEY column, the PARTITION_NUMBER column is not mandatory. The PARTITION_NUMBER column is not allowed in a nonpartitioned database environment.
  Table Attributes:
  • Default table attributes are used. Besides, distribution key (partitioned databases only), no extra options are specified when you create tables.
  • Indexes on the table can be created.
  • Extra table attributes (such as volatile, RI, triggers, constraints) can be added, but the event monitor process (or thread) ignores them.
  • If not logged initially is added as a table attribute, it is turned off at the first COMMIT, and is not set back on.
  Event Monitor Activation:
  • When an event monitor activates, all target table names are retrieved from the SYSCAT.EVENTTABLES catalog view.
  • In a partitioned database environment, activation processing occurs on every database partition of the instance. On a particular database partition, activation processing determines the table spaces and database partition groups for each target table. The event monitor activates on a database partition if at least one target table exists on that database partition. Moreover, if some target table is not found on a database partition, that target table is flagged so that data that is destined for that table is dropped during runtime processing.
  • If a target table does not exist when the event monitor activates (or, in a partitioned database environment, if the table space does not reside on a database partition), activation continues, and data that would otherwise be inserted into this table is ignored.
  • Activation processing validates each target table. If validation fails, activation of the event monitor fails, and messages are written to the administration log.
  • During activation in a partitioned database environment, the CONTROL table rows for FIRST_CONNECT and EVMON_START are only inserted on the catalog database partition. The table space for the control table must exist on the catalog database partition. If it does not exist on the catalog database partition, these inserts are not inserted.
  • In a partitioned database environment, if a partition is not yet active when a write to table event monitor is activated, the event monitor is activated the next time that partition is activated.
  Run Time:
  • An event monitor runs with DATAACCESS authority.
  • While an event monitor is active, an insert operation into a target table fails:
    • Uncommitted changes are rolled back.
    • A message is written to the administration log.
    • The event monitor is deactivated.
  • If an event monitor is active, a local COMMIT runs when it finishes processing an event monitor buffer.
  • In a partitioned database environment, the actual statement text, which can be up to 2 MB, is only stored (in the STMT or DLCONN table) by the event monitor process on the application coordinator database partition. On other database partitions, this value has zero length.
  • In an environment other than a partitioned database or a Db2 pureScale database, all write to table event monitors are deactivated when the last application exits (and the database was not activated explicitly). In a Db2 pureScale environment, write to table event monitors are deactivated on a given member when the database deactivates on that member and reactivates when the database activates on that member again. In a partitioned database environment, write to table event monitors are deactivated when the catalog partition deactivates.
  • The DROP EVENT MONITOR statement does not drop target tables.
  • Whenever a write-to-table event monitor activates, it acquires IN table locks on each target table to prevent them from being modified while the event monitor is active. Table locks are maintained on all tables while the event monitor is active. If exclusive access is required for any of the target tables (for example, when a utility is to be run), first deactivate the event monitor to release the table locks, and then attempting such access.
• Syntax alternatives: The following syntax alternatives are supported for compatibility with previous versions of Db2 and with other database products. These alternatives are non-standard and should not be used.
  • NODE can be specified in place of DBPARTITIONNUM
  • Commas can be used to separate multiple options in the target-table-options clause.

Examples

• Example 1: The following example creates an event monitor called SMITHPAY. This event monitor collects event data for the database and for the SQL statements that are run by the PAYROLL application that is owned by the JSMITH authorization ID. The data is appended to the absolute path /home/jsmith/event/smithpay/. A maximum of 25 files are created. Each file is a maximum of 1 024 4K pages long. The file I/O is non-blocked.

     CREATE EVENT MONITOR SMITHPAY
       FOR DATABASE, STATEMENTS
       WHERE APPL_NAME = 'PAYROLL' AND AUTH_ID = 'JSMITH'
       WRITE TO FILE '/home/jsmith/event/smithpay'
       MAXFILES 25
       MAXFILESIZE 1024
       NONBLOCKED
       APPEND

• Example 2:  The following example creates an event monitor called DEADLOCKS_EVTS. This event monitor collects deadlock events and writes them to the relative path DLOCKS. One file is written, and the file size has no limit. Each time the event monitor is activated, it appends the event data to the file 00000000.evt if it exists. The event monitor is started each time that the database is started. The I/0 is blocked by default.

     CREATE EVENT MONITOR DEADLOCK_EVTS
       FOR DEADLOCKS
       WRITE TO FILE 'DLOCKS'
       MAXFILES 1
       MAXFILESIZE NONE
       AUTOSTART

• Example 3:  This example creates an event monitor called DB_APPLS. This event monitor collects connection events, and writes the data to the named pipe /home/jsmith/applpipe.

     CREATE EVENT MONITOR DB_APPLS
       FOR CONNECTIONS
       WRITE TO PIPE '/home/jsmith/applpipe'

• Example 4: This example, which assumes a partitioned database environment, creates an event monitor called FOO. This event monitor collects SQL statement events and writes them to SQL tables with the following derived names:
  • CONNHEADER_FOO
  • STMT_FOO
  • SUBSECTION_FOO
  • CONTROL_FOO
  Because no table space information is supplied, all tables are created in a table space selected by the system, based on the rules described under the IN tablespace-name clause. All tables include all elements for their group (that is, columns are defined whose names are equivalent to the element names.)

     CREATE EVENT MONITOR FOO
       FOR STATEMENTS
       WRITE TO TABLE

• Example 5: This example, which assumes a partitioned database environment, creates an event monitor called BAR. This event monitor collects SQL statement and transaction events and writes them to tables as follows:
  • Any data from the STMT group is written to table MYDEPT.MYSTMTINFO. The table is created in table space MYTABLESPACE. Create columns only for the following elements: ROWS_READ, ROWS_WRITTEN, and STMT_TEXT. Any other elements of the group is discarded.
  • Any data from the SUBSECTION group is written to table MYDEPT.MYSUBSECTIONINFO. The table is created in table space MYTABLESPACE. The table includes all columns, except START_TIME, STOP_TIME, and PARTIAL_RECORD.
  • Any data from the XACT group is written to table XACT_BAR. Because no table space information is supplied, the table is created in a table space selected by the system, based on the rules described under the IN tablespace-name clause. This table includes all elements that are contained in the XACT group.
  • No tables are created for connheader or control, and all data for these groups is discarded.

     CREATE EVENT MONITOR BAR
       FOR STATEMENTS, TRANSACTIONS
       WRITE TO TABLE
       STMT(TABLE MYDEPT.MYSTMTINFO IN MYTABLESPACE
         INCLUDES(ROWS_READ, ROWS_WRITTEN, STMT_TEXT)),
       STMT(TABLE MYDEPT.MYSTMTINFO IN MYTABLESPACE
         EXCLUDES(START_TIME, STOP_TIME, PARTIAL_RECORD)),
       XACT