The CREATE EVENT MONITOR (unit of work) statement creates
an event monitor that will record events when a unit of work completes.
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
>>-CREATE EVENT MONITOR--event-monitor-name--------------------->
>----FOR UNIT OF WORK------------------------------------------->
>----WRITE TO--+-TABLE--| formatted-event-table-info |-----------------------+---->
'-UNFORMATTED EVENT TABLE--+--------------------------------+-'
'-(--| target-table-options |--)-'
.-AUTOSTART---.
>--+-------------+---------------------------------------------><
'-MANUALSTART-'
formatted-event-table-info
|--+---------------------------------------------------+--------|
| .-,---------------------------------------------. |
| V | |
'---evm-group--+--------------------------------+-+-'
'-(--| target-table-options |--)-'
target-table-options
.--------------------------------------------.
V (1) (2) |
|----------------+-TABLE--table-name----------+-+---------------|
+-IN--tablespace-name--------+
| .-PCTDEACTIVATE--100-----. |
'-+-PCTDEACTIVATE--integer-+-'
Notes:
- Each table option can be specified a maximum of one time
(SQLSTATE 42613).
- 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.
- UNIT OF WORK
- Specifies that this passive event monitor will record an event
whenever a unit of work is completed (that is, whenever there is a
commit or rollback).
The creation of the unit of work event monitor
does not indicate that the unit of work data will be collected immediately.
The actual unit of work event of interest is controlled at the workload
level.
- WRITE TO
- Specifies 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 formatted event tables for the event monitor. This clause should specify each
grouping that is to be recorded. However, if no evm-group 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 a 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 |
Unit of work |
- UOW
- UOW_METRICS
- UOW_PACKGE_LIST
- UOW_EXECUTABLE_LIST
- CONTROL
|
- UNFORMATTED EVENT TABLE
- Specifies that the target for the event monitor is an unformatted
event table. The unformatted event table is used to store collected
unit of work event monitor data.
Data is stored in its original binary format within an inlined BLOB
column. The BLOB column can contain multiple binary records of different
types. The data in the BLOB column is not in a readable format and
requires conversion, through use of the db2evmonfmt Java™-based tool, EVMON_FORMAT_UE_TO_XML
table function, or EVMON_FORMAT_UE_TO_TABLES procedure, into a consumable
format such as an XML document or a relational table.
- target-table-options
- Identifies options for the target table. If a value for target-table-options is
not specified, CREATE EVENT MONITOR processing proceeds as follows:
- A derived table name is used (as explained in the description
for TABLE table-name).
- A default table space is chosen using the same process as when
a table is created without a table space name using CREATE TABLE.
- PCTDEACTIVATE is set to 100.
- 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. For an unformatted event
table if a name is not provided, the unqualified name is equal to
the event-monitor-name, that is, the unformatted
event table will be named after the event monitor. For a formatted
event table 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
- Specifies the table space in which the
table is to be created. The CREATE EVENT MONITOR FOR UNIT OR WORK
statement does not create table spaces.
If
a table space name is not 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.
- 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 unit of work event
monitor.
- 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.
Notes
- The
table is created when the CREATE EVENT MONITOR FOR UNIT OF WORK statement
executes, if it doesn't already exist.
- During
CREATE EVENT MONITOR FOR UNIT OF WORK processing, if a table is found
to have already been defined for use by another event monitor, the
CREATE EVENT MONITOR FOR UNIT OF WORK 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. If the table exists and is
not defined for use by another event monitor, then no table is created,
any other table target-table-options parameters are ignored,
and processing continues. A warning is passed back to the application
program.
- Dropping the event monitor will not drop any tables. Any associated
tables must be manually dropped after the event monitor is dropped.
- Lock
event data is not automatically pruned from either unformatted event
tables or regular tables created by this event monitor. An option
for pruning data from UE tables is available when using the EVMON_FORMAT_UE_TO_TABLES
procedure. For event monitors that write to regular tables, event
data must be pruned manually.
- For
unformatted event tables event data is inserted into the table into
an inlined BLOB data column. Normally, BLOB data is stored in a separate
LOB table space and can experience additional performance overhead
as a result. When inlined into the data page of the base table, the
BLOB data does not experience this overhead. The database manager
will automatically inline the BLOB data portion of an unformatted
event table record if the size of the BLOB data is less than the table
space page size minus the record prefix. Therefore to achieve high
efficiency and application throughput, it is suggested that you create
the event monitor in as large a table space as possible up to and
including a 32 KB table space and associated bufferpool.
- Create only one unit of work event monitor per database and not
create multiple unit of work event monitors on the same database.
- In a partitioned database environment,
data is written only to target tables on the database partitions where
their table spaces exist. If a table space for a target unformatted
event 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 to be chosen, by creating a
table space that exists only on certain database partitions.
- In a multi-member environment, data
is only written to target tables on the member where work occurs within
the unit of work.
- 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.
- The unit of work event monitor is not affected by the unit or
work event monitor switch. The unit of work event monitor switch is
not changed when a unit or work event monitor is created, and the
contents of the unit or work event monitor are not affected by changes
to the unit of work event monitor switch.
- The
FLUSH EVENT MONITOR statement is not applicable to this event monitor
and will have no effect when issued against it.
- Creation of the unit of work event monitor does not cause events
to be written to the event monitor. The unit of work event monitor
must be activated with SET EVENT MONITOR STATE, and the unit of work
data must be collected by either altering the appropriate workload
to specify COLLECT UNIT OF WORK DATA or setting the mon_uow_data database
configuration parameter to a value other than NONE.
- When using unformatted event tables,
create the unit of work event monitor in a table space with at least
8 KB page size to ensure that the event data is contained within the
inlined BLOB column of the unformatted event table. If the BLOB column
is not inlined, then the performance of writing and reading the events
to the unformatted event table might not be efficient.
Examples
- Example
1: This example creates a unit of work event monitor UOWEVMON
that collects data for unit of work events that occur on the database
of creation, and writes data tables using default table names:
CREATE EVENT MONITOR UOWEVMON
FOR UNIT OF WORK
WRITE TO TABLE
This event monitor writes
its output to the following tables:
- UOW_UOWEVMON
- UOW_METRICS_UOWEVMON
- UOW_PACKAGE_LIST_UOWEVMON
- UOW_EXECUTABLE_LIST_UOWEVMON
- UOW_CONTROL_UOWEVMON
Note: Whether the tables for package list and executable list
information are populated with data is dependent on whether you specify
that that data is to be collected. You control the collection of this
data is using the mon_uow_pkglist or mon_uow_execlist configuration
parameters, or with the appropriate COLLECT UNIT OF WORK DATA clause
on the CREATE or ALTER WORKLOAD statements.
- Example 2: This example creates a unit of work event monitor
UOWEVMON that will collect unit of work events that occur on the database
of creation and store it in the unformatted event table GREG.UOWEVENTS.
CREATE EVENT MONITOR UOWEVMON
FOR UNIT OF WORK
WRITE TO UNFORMATTED EVENT TABLE (TABLE GREG.UOWEVENTS)
- Example 3: This example creates a unit of work event monitor
UOWEVMON that will collect unit of work events that occur on the database
of creation and store it in the unformatted event table GREG.UOWEVENTS
in table space APPSPACE. The event monitor will deactivate when the
table space becomes 85% full.
CREATE EVENT MONITOR UOWEVMON
FOR UNIT OF WORK
WRITE TO UNFORMATTED EVENT TABLE
(TABLE GREG.UOWEVENTS IN APPSPACE PCTDEACTIVATE 85)