CREATE EVENT MONITOR (statistics) statement
The CREATE EVENT MONITOR (statistics) statement defines a monitor that 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.
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).
- DBADM authority
- SQLADM authority
- 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 a service class, workload, or work class event:
- Every period minutes, where period is the value of the wlm_collect_int database configuration parameter
- When the mon_collect_stats procedure is called
- WRITE TO
- 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 tables for an event monitor. 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 Logical data groups and event monitor output tables.
- 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 Statistics
- Identifies the target table for the group.
If a value for target-table-options is not specified, CREATE EVENT MONITOR processing proceeds as follows:
- TABLE table-name
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. 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 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.
- A derived table name is used.
- A default table space is chosen.
- PCTDEACTIVATE defaults to 100.
- BUFFERSIZE pages
- Specifies the size of the event monitor buffers (in units of 4K pages). Table event monitors
insert all data from a buffer, and issues a COMMIT once the buffer has been processed. The larger
the buffers, the larger the commit scope used by the event monitor. Highly active event monitors
should have larger buffers than relatively inactive event monitors. When a monitor is started, two
buffers of the specified size are allocated. Event monitors use double buffering to permit
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 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.Note: This keyword is not supported for statistics event monitors. The compiler accepts this keyword, but the keyword has no effect on the behavior of the event monitor.
- 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.Note: This keyword is not supported for statistics event monitors. The compiler accepts this keyword, but the keyword has no effect for statistics event monitors. The event monitor is created as if the BLOCKED keyword was specified.
- 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.
- 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.
name of the pipe
to which the event monitor will write the data. The naming rules for pipes are platform specific.
Operating system Naming rules 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).
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, an NFS mounted file). A string constant must be used when specifying the
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.
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.
- 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
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
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.Note: This keyword is not supported for statistics event monitors. The compiler accepts this keyword, but the keyword has no effect on the behavior of the event monitor.
- 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 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.Note: This keyword is not supported for statistics event monitors. The compiler accepts this keyword, but the keyword has no effect for statistics event monitors. The event monitor is created as if the BLOCKED keyword was specified.
- 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 member on which the event monitor runs is activated. This is the default behavior of the statistics event monitor.
- ON MEMBER member-number
the member 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 member. The I/O component
will physically run on the specified member, writing records
to the specified file or pipe.
If -1 is specified, it 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.
If this clause is not specified, the currently connected database partition number (for the application) is used.
- The event monitor reports only on the member 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 is not a valid scope for this type of event monitor.
- The STATISTICS event type cannot be combined with any other event types in a particular event monitor definition.
- 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.
- If the member on which the event monitor is to run is not active, event monitor activation occurs when that member 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 member is deactivated, and that member is subsequently reactivated, the event monitor is also explicitly reactivated.
- If you create the event monitor such that the logical data groups event_scstats or event_wlstats are included in the event monitor output, metrics are reported in two XML documents contained in the event monitor output. The monitor elements reported in the metrics document show the change in value for the monitor elements since the last time statistics were collected. The elements reported in details_xml are the same monitor elements, however, they show the values since the database was activated. That is, they continue to increase until the database is deactivated.
- Write to table event monitors: General notes:
- All target tables are created when the CREATE EVENT MONITOR statement executes.
- 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 only be used by one event monitor. During CREATE EVENT MONITOR processing, if a target table is found to have already been 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 already 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 is executed. 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 member in the instance, and each of these processes reports data only for the member on which it is running.
- The following event types from the flat monitor log file or pipe format are not recorded by
write to table event monitors:
- 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 member for monitoring, by
creating a table space that exists only on certain 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 same 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.
- 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 being 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 sqlstate CHAR sqlwarn CHAR other fields INTEGER or BIGINT
- Columns are defined to be NOT NULL.
- 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 details 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 member on which the process is running; that is, insert operations are performed locally on the member where the event monitor process is running. On any database partition, the PARTITION_KEY field will contain the same value. This means that if a database partition is dropped and data redistribution is performed, all data on the dropped database partition will go to one other database partition instead of being evenly distributed. Therefore, before removing a database partition, consider deleting all table rows on that database partition.
- In a partitioned database environment, a column 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 non-partitioned database environment.
- Default table attributes are used. Besides distribution key (partitioned databases only), no extra options are specified when creating tables.
- Indexes on the table can be created.
- Extra table attributes (such as volatile, RI, triggers, constraints, and so on) can be added, but the event monitor process (or thread) will ignore 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.
- 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 member of the instance. On a particular member, activation processing determines the table spaces and database partition groups for each target table. The event monitor only activates on a member 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 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. This requires that the table space for the control table exist on the catalog database partition. If it does not exist on the catalog database partition, these inserts are not performed.
- In a partitioned database environment, if a member is not yet active when a write to table event monitor is activated, the event monitor will be activated the next time that member is activated.
- An event monitor runs with DATAACCESS authority.
- If, 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, it performs a local COMMIT when it has finished processing an event monitor buffer.
an environment other than a partitioned database
environment, all write to table event monitors are deactivated when the last application terminates
(and the database has not been explicitly activated).
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 will acquire IN table locks on each target table in order 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 on any of the target tables (for example, when a utility is to be run), first deactivate the event monitor to release the table locks before attempting such access.
alternatives: The following syntax is supported
for cross-product compatability only. These alternatives are non-standard and should not be
- DBPARTITIONNUM or NODE can be specified in place of MEMBER, except when the DB2_ENFORCE_MEMBER_SYNTAX registry variable is set to ON.
- Commas can be used to separate multiple options in the target-table-options clause
CREATE EVENT MONITOR DBSTATISTICS FOR STATISTICS WRITE TO TABLE SCSTATS (TABLE SCSTATS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), SCMETRICS (TABLE SCMETRICS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), SUPERCLASSSTATS (TABLE SUPERCLASSTATS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), SUPERCLASSMETRICS (TABLE SUPERCLASSMETRICS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), WCSTATS (TABLE WCSTATS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), WLSTATS (TABLE WLSTATS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), QSTATS (TABLE QSTATS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), HISTOGRAMBIN (TABLE HISTOGRAMBIN_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), OSMETRICS (TABLE OSMETRICS_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100), CONTROL (TABLE CONTROL_DBSTATISTICS IN USERSPACE1 PCTDEACTIVATE 100) AUTOSTART;