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.
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
- WLMADM authority
Syntax
>>-CREATE EVENT MONITOR--event-monitor-name--FOR ACTIVITIES----->
>--WRITE TO--+-TABLE--| formatted-event-table-info |-+--●------->
+-PIPE--pipe-name-----------------------+
'-FILE--path-name--| file-options |-----'
.-AUTOSTART---.
>--+-------------+--●--+--------------------------+--●---------->
'-MANUALSTART-' '-ON MEMBER--member-number-'
.-LOCAL-.
>--+-------+--●------------------------------------------------><
formatted-event-table-info
|--●--+---------------------------------------------------+----->
| .-,---------------------------------------------. |
| V | |
'---evm-group--+--------------------------------+-+-'
'-(--| target-table-options |--)-'
.-BLOCKED-.
>--●--+---------+--●--------------------------------------------|
target-table-options
.--------------------------------------------.
V (1) (2) |
|----------------+-TABLE--table-name----------+-+---------------|
+-IN--tablespace-name--------+
| .-PCTDEACTIVATE--100-----. |
'-+-PCTDEACTIVATE--integer-+-'
file-options
.-MAXFILES--NONE------------.
|--●--+---------------------------+--●-------------------------->
'-MAXFILES--number-of-files-'
.-BLOCKED-. .-APPEND--.
>--+------------------------+--●--+---------+--●--+---------+--->
'-MAXFILESIZE--+-pages-+-' '-REPLACE-'
'-NONE--'
>--●------------------------------------------------------------|
Notes:
- Each clause can be specified only once.
- 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.
- ACTIVITIES
- 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 before completing
- 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.
- formatted-event-table-info
- 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 ../../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:
Type of Event Monitor |
evm-group Value |
Activities |
- ACTIVITY
- ACTIVITYMETRICS
- ACTIVITYSTMT
- ACTIVITYVALS
- CONTROL
|
- target-table-options
- 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 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.
Since
the page size affects the INLINE LOB lengths used, consider specifying a table space with as large a
page size as possible in order to improve the INSERT performance of the event monitor.
- PCTDEACTIVATE integer
- If a table for the event monitor is being created in an automatic storage
(non-temporary) or DMS table space, the PCTDEACTIVATE parameter specifies how full the table space
must be before the event monitor automatically deactivates. The specified value, which represents a
percentage, can range from 0 to 100, where 100 means that the event monitor deactivates when the
table space becomes completely full. The default value assumed is 100 if PCTDEACTIVATE is not
specified. This option is ignored for SMS table spaces.
Important: If the target table
space has auto-resize enabled, set PCTDEACTIVATE parameter to 100. Alternatively, omit this clause
entirely to have the default of 100 apply. Otherwise, the event monitor might deactivate
unexpectedly if the table space reaches the threshold specified by PCTDEACTIVTATE before the table
space is automatically resized.
If a value for
target-table-options
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.
- 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).
- 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.
- 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.
- 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.
- 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.
- AUTOSTART
- Specifies that the event monitor is to be automatically activated
whenever the database partition on which the event monitor runs is
activated. This is the default behavior of the activities event monitor.
- ON MEMBER member-number
- Specifies
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.
When the DB2 pureScale feature is
enabled, -1 is the default.
If a value of -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.
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 member (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 member.
- LOCAL
- 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.
Rules
- The ACTIVITIES event type cannot be combined
with any other event types 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.
- If the member on
which the event monitor is to run is not active, event monitor activation
occurs when that member is
reactivated.
- 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.
- The
FLUSH EVENT MONITOR statement is not applicable to this event monitor
and will have no effect when issued against it.
- 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:
- 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 will
be 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 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.
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 statement 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[72]
sqlstate CHAR[5]
sqlwarn CHAR[11]
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.
Table Attributes: - 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.
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 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 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 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.
Run Time: - 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.
- In
an environment other than a partitioned database or a
DB2 pureScale
environment, all write to table event monitors are deactivated when the last application terminates
(and the database has not been explicitly activated).
In
a DB2 pureScale environment,
write to table event monitors are deactivated on a given member when the member stops and is
reactivated when the member restarts.
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.
- 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.
- 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
Example
Define
an activity event monitor named DBACTIVITIES
CREATE EVENT MONITOR DBACTIVITIES
FOR ACTIVITIES
WRITE TO TABLE
ACTIVITY (TABLE ACTIVITY_DBACTIVITIES
IN USERSPACE1
PCTDEACTIVATE 100),
ACTIVITYMETRICS (TABLE ACTIVITYMETRICS_DBACTIVITIES
IN USERSPACE1
PCTDEACTIVATE 100),
ACTIVITYSTMT (TABLE ACTIVITYSTMT_DBACTIVITIES
IN USERSPACE1
PCTDEACTIVATE 100),
ACTIVITYVALS (TABLE ACTIVITYVALS_DBACTIVITIES
IN USERSPACE1
PCTDEACTIVATE 100),
CONTROL (TABLE CONTROL_DBACTIVITIES
IN USERSPACE1
PCTDEACTIVATE 100)
AUTOSTART;