DB2 10.5 for Linux, UNIX, and Windows

CREATE EVENT MONITOR statement

The CREATE EVENT MONITOR statement defines a monitor that will record certain events that occur when using the database. The definition of each event monitor also specifies where the database should record the events.

Several different types of event monitors can be created using this statement. Some of these types are described in the sections below, while the remaining types are described separately (see Related links). The types of event monitors described separately are:
  • Activities. The event monitor will record activity events that occur when using the database. The definition of the activities event monitor also specifies where the database should record the events.
  • Change history. The event monitor will record events for changes to configuration parameters, registry variables, and the execution of DDL statements and utilities. The event monitor will also record initial configuration and registry values at event monitor startup time.
  • Locking. The event monitor will record lock-related events that occur when using the database. All records are collected in the unformatted event table.
  • Package cache. The event monitor will record events related to the package cache statement.
  • Statistics. The event monitor will record statistics events that occur when using the database. The definition of the statistics event monitor also specifies where the database should record the events.
  • Threshold violations. The event monitor will record threshold violation events that occur when using the database. The definition of the threshold violations event monitor also specifies where the database should record the events.
  • Unit of work. The event monitor will record events when a unit of work completes. All records are collected in the unformatted event table.

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---------------->

   .-,------------------------------------------------------------.   
   V                                                              |   
>----+-+-DATABASE---------------------------------------------+-+-+-->
     | +-TABLES-----------------------------------------------+ |     
     | +-DEADLOCKS--+---------------------------------------+-+ |     
     | |            '-WITH DETAILS--+---------------------+-' | |     
     | |                            '-HISTORY--+--------+-'   | |     
     | |                                       '-VALUES-'     | |     
     | +-TABLESPACES------------------------------------------+ |     
     | '-BUFFERPOOLS------------------------------------------' |     
     '-+-CONNECTIONS--+--+----------------------------+---------'     
       +-STATEMENTS---+  '-WHERE--| event-condition |-'               
       '-TRANSACTIONS-'                                               

>--WRITE TO--+-TABLE--| evm-group-info |---------+--●----------->
             +-PIPE--pipe-name-------------------+      
             '-FILE--path-name--| file-options |-'      

   .-MANUALSTART-.      
>--+-------------+--●------------------------------------------->
   '-AUTOSTART---'      

>--+----------------------------------------+--●---------------->
   '-ON DBPARTITIONNUM--db-partition-number-'      

   .-LOCAL--.      
>--+--------+--●-----------------------------------------------><
   '-GLOBAL-'      

event-condition

   .-AND | OR-----------------------------------------------------.   
   V                                                              |   
|----+-----+--+-+-APPL_ID---+--+-=---------+--comparison-string-+-+--|
     '-NOT-'  | +-AUTH_ID---+  |    (1)    |                    |     
              | '-APPL_NAME-'  +-<>--------+                    |     
              |                +->---------+                    |     
              |                |    (1)    |                    |     
              |                +->=--------+                    |     
              |                +-<---------+                    |     
              |                |    (1)    |                    |     
              |                +-<=--------+                    |     
              |                +-LIKE------+                    |     
              |                '-NOT--LIKE-'                    |     
              '-(--event-condition--)---------------------------'     

evm-group-info

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

   .-BLOCKED-.   
>--+---------+--------------------------------------------------|

target-table-options

   .-------------------------------------------------.   
   V  (2)   (3)                                      |   
|----------------+-TABLE--table-name---------------+-+----------|
                 +-IN--tablespace-name-------------+     
                 | .-PCTDEACTIVATE--100-----.      |     
                 +-+-PCTDEACTIVATE--integer-+------+     
                 +-TRUNC---------------------------+     
                 |                  .-,-------.    |     
                 |                  V         |    |     
                 '-+-INCLUDES-+--(----element-+--)-'     
                   '-EXCLUDES-'                          

file-options

      .-MAXFILES--NONE------------.      
|--●--+---------------------------+--●-------------------------->
      '-MAXFILES--number-of-files-'      

                                  .-BUFFERSIZE--4-----.      
>--+------------------------+--●--+-------------------+--●------>
   '-MAXFILESIZE--+-pages-+-'     '-BUFFERSIZE--pages-'      
                  '-NONE--'                                  

   .-BLOCKED----.     .-APPEND--.      
>--+------------+--●--+---------+--●----------------------------|
   '-NONBLOCKED-'     '-REPLACE-'      

Notes:
  1. Other forms of these operators are also supported.
  2. Each clause can be specified only once.
  3. 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.
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 has changed since the first connection to the database.
DEADLOCKS
Note: This option has been 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 executing when the deadlock occurred, such as the statement text
  • The locks held by the application when the deadlock occurred. In a partitioned database environment, this 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, this includes the data partition identifier.
HISTORY
Specifies that the event monitor data will also include:
  • The history of all statements in the current unit of work at the participating node (including WITH HOLD cursors opened in previous units of work). SELECT statements issued 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 will also include:
  • The data values used as input variables for each SQL statement. These data values will not include LOB data, long data, structured type data, or XML data.

Only one of: 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 a SQL statement finishes executing.
TRANSACTIONS
Note: This option has been 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 will generate 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 if an application will generate events for a particular event monitor, the WHERE clause is evaluated:
  • For each active connection when an event monitor is first turned on
  • Subsequently 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 will be monitored.

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

APPL_ID
Specifies that the application ID of each connection should be compared with the comparison-string in order to determine if the connection should generate CONNECTION, STATEMENT or TRANSACTION events (whichever was specified).
AUTH_ID
Specifies that the authorization ID of each connection should be compared with the comparison-string in order to determine if the connection should generate CONNECTION, STATEMENT or TRANSACTION events (whichever was specified).
APPL_NAME
Specifies that the application program name of each connection should be compared with the comparison-string in order to determine if the connection should generate 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, host variables and other string expressions are not permitted).
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 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.
evm-group-info
Defines the target table for a logical data group. This clause should 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, refer to ../../com.ibm.db2.luw.admin.mon.doc/doc/r0059240.html .

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
Bufferpools
  • 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 using the same process as when a table is created without a table space name using CREATE TABLE.
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 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.
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 will be defined as CLOB(2M). Note that 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 should wait for an event buffer to be written out to disk if the agent determines that both event buffers are full. BLOCKED should be selected to guarantee no event data loss. This 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). When writing the data to a pipe, an event monitor does not perform blocked writes. If there is no room in the pipe buffer, then the event monitor will discard the data. It is the monitoring application's responsibility to read the data promptly if it wishes to ensure no data loss.
pipe-name
The name of the pipe (FIFO on AIX®) to which the event monitor will write 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 a special syntax for a pipe name and, as a result, absolute pipe names are required.

The existence of the pipe will not be checked at event monitor creation time. It is the responsibility of the monitoring application to have created and opened the pipe for reading at the time that the event monitor is activated. If the pipe is not available at this time, then the event monitor will turn itself off, and will log 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 will log an error in the system error log.) If the event monitor is activated via the SET EVENT MONITOR STATE SQL statement, then that statement will fail (SQLSTATE 58030).

In a DB2® pureScale® environment, the pipe-name must be on a shared file system whether this is a LOCAL or GLOBAL event monitor. This requirement is to allow these event monitors to operate correctly in the event of a member failover. Failure to use a pipe-name on a shared file system will result 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 8 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 file 00000000.evt; the end of the data stream is the last byte in the file nnnnnnnn.evt).

The maximum size of each file can be defined as well as the maximum number of files. An event monitor will never split a single event record across two files. However, an event monitor may write related records in two different files. It is the responsibility of the application that uses this data to keep track of such related information when processing the event files.

path-name
The name of the directory in which the event monitor should write the event files data. The path must be known at the server; however, the path itself could reside on another database partition (for example, an NFS mounted file). A string constant must be used when specifying 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. At that time, if the target path does not exist, an error (SQLSTATE 428A3) is raised.

If an absolute path is specified, the specified path will be 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 will be used. In a DB2 pureScale environment, if a relative path is specified, then the path relative to the database owning directory in the database directory will be used.

It is possible to specify two or more event monitors that have the same target path. However, once one of the event monitors has been activated for the first time, and as long as the target directory is not empty, it will be 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 is a LOCAL or GLOBAL event monitor. This requirement is to allow these event monitors to operate correctly in the event of a member failover. Failure to use a path-name on a shared file system will result 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 there is no limit to the number of event files that the event monitor will create. This is the default.
MAXFILES number-of-files
Specifies that there is a limit on the number of event monitor files that will exist for a particular event monitor at any time. Whenever an event monitor has to create another file, it will check to make sure that the number of .evt files in the directory is less than number-of-files. If this limit has already been reached, then the event monitor will turn itself off.

If an application removes the event files from the directory after they have been written, then the total number of files that an event monitor can produce can exceed number-of-files. This option has been provided to allow a user to guarantee that the event data will not consume more than a specified amount of disk space.

MAXFILESIZE pages
Specifies that there is a limit to the size of each event monitor file. Whenever an event monitor writes a new event record to a file, it checks that the file will 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 there is no set limit on a file's size. If MAXFILESIZE NONE is specified, then MAXFILES 1 must also be specified. This option means that one file will contain all of the event data for a particular event monitor. In this case the only event file will be 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 will be performed by the event monitor. Highly active event monitors should 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 4 pages (two 16K buffers are allocated). The minimum size is 1 page. The maximum size of the buffers is limited by the value of the MAXFILESIZE parameter, as well as 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 1 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 2 pages.

BLOCKED
Specifies that each agent that generates an event should wait for an event buffer to be written out to disk if the agent determines that both event buffers are full. BLOCKED should be selected to guarantee no event data loss. This is the default option.
NONBLOCKED
Specifies that each agent that generates an event should 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 already exist when the event monitor is turned on, then the event monitor will append the new event data to the existing stream of data files. When the event monitor is reactivated, it will resume writing to the event files as if it had never been turned off. APPEND is the default option.

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

REPLACE
Specifies that if event data files already exist when the event monitor is turned on, then the event monitor will erase all of the event files and start writing data to file 00000000.evt.
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. 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 will physically run on the specified database partition or member, writing 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, in the event that the I/O component is no longer able to run on a given member, the event monitor will be restarted with the I/O component running on another available active member.

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

In a DB2 pureScale environment, write-to-table event monitors will 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

Notes

Examples