DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE EVENT MONITOR (activities) statement

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


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


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


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

>--WRITE TO--+-TABLE--| wlm-table-options |------+--●----------->
             '-FILE--path-name--| file-options |-'      


   '-ON DBPARTITIONNUM--db-partition-number-'      



      | .-,----------------------------------------------. |   
      | V                                                | |   
                     '-(--| wlm-target-table-info |--)-'       



   V  (1)   (2)                                 |   
                 | .-PCTDEACTIVATE--100-----. |     



                                  .-BLOCKED-.     .-APPEND--.   
   '-MAXFILESIZE--+-pages-+-'                     '-REPLACE-'   


  1. Each clause can be specified only once.
  2. Clauses can be separated with a space or a comma.


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).
Introduces the type of event to record.
Specifies that the event monitor records an activity event when an activity finishes executing, or before the completion of execution if the event is triggered by the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure. The activity must either:
  • Belong to a service class or workload that has COLLECT ACTIVITY DATA set
  • Belong to a work class whose associated work action is COLLECT ACTIVITY DATA
  • Be identified as the activity that violated a threshold whose COLLECT ACTIVITY DATA clause was specified
  • Have been identified in a call to the WLM_CAPTURE_ACTIVITY_IN_PROGRESS procedure prior to completing
Introduces the target for the data.
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.
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.
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:
Type of Event Monitor evm-group Value
Identifies the target table for the group.
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 schema for the current authorization ID. 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.

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.

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.
If a value for wlm-target-table-info is not specified, CREATE EVENT MONITOR processing proceeds as follows:
  • A derived table name is used.
  • A default table space is chosen.
  • The PCTDEACTIVATE parameter defaults to 100.
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.
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.
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. On UNIX operating systems, pipe names are treated like file names. As a result, relative pipe names are permitted, and are treated like relative path-names (see path-name below). On Windows, however, 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).

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.

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, on a UNIX system, this might be 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 (a path that starts with the root directory on AIX, or a disk identifier on Windows) is specified, the specified path will be the one used. 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.

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.

Specifies the options for the file format.
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.

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:
  • Windows - 200 4K pages
  • UNIX - 1000 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.

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

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.
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.
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 activities event monitor.
ON DBPARTITIONNUM db-partition-number
Specifies the database partition on which a file or pipe event monitor is to run. When the monitoring scope is defined as LOCAL, data is only collected on the specified partition. When the monitoring scope is defined as GLOBAL, all database partitions collect data and report to the database partition with the specified number. The I/O component will physically run on the specified database partition, writing records to the specified file or pipe.

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.

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

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 not valid for table event monitors.




Example 1: Define an activity event monitor named DB2ACTIVITIES
                 IN USERSPACE1
                 PCTDEACTIVATE 100),
                        IN USERSPACE1
                        PCTDEACTIVATE 100),
                     IN USERSPACE1
                     PCTDEACTIVATE 100),
                     IN USERSPACE1
                     PCTDEACTIVATE 100),
                IN USERSPACE1
                PCTDEACTIVATE 100)