The CREATE EVENT MONITOR (locking) statement creates an
event monitor that will record lock-related events that occur when
using the database.
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 LOCKING------>
>----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.
- LOCKING
- Specifies that this passive event monitor will record any lock
event produced when DB2® runs
into one or more of these conditions:
- LOCKTIMEOUT: the lock has timed-out.
- DEADLOCK: the lock was involved in a deadlock (victim and participant(s)).
- LOCKWAIT: locks that are not acquired in the specified duration.
The creation of the lock event monitor does not indicate that
the locking data will be collected immediately. The actual locking
event of interest is controlled at the workload level or database
level.
- WRITE TO
- Specifies the target for the data.
- TABLE
- Indicates that the target for the event monitor data is a set
of formatted event 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, see the Related reference section
of this topic.
- 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 |
Locking |
- LOCK
- LOCK_PARTICIPANTS
- LOCK_PARTICIPANT_ACTIVITIES
- LOCK_ACTIVITY_VALUES
- 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
locking event monitor data. Data is stored in an internal binary format
within an inlined BLOB column. Each event can insert multiple records
into this table and each inserted record can be of a different type
with the associated BLOB content varying as well. 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 FOR LOCKING 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. If a name is not provided for an unformatted event table,
the unqualified name is equal to the event-monitor-name,
that is, the unformatted event table will be named after the event
monitor. If no name is provided for a formatted event table, the unqualified
name is derived from evm-group and event-monitorname 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. The CREATE EVENT MONITOR FOR LOCKING 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.
When
specifying the table space name for a formatted event table, the table
space's 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. The default value is 100, where 100 means 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.
- 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 locking 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 target table is created when the
CREATE EVENT MONITOR FOR LOCKING statement executes, if it doesn't
already exist.
- During CREATE EVENT MONITOR FOR LOCKING
processing, if a table is found to have already been defined for use
by another event monitor, the CREATE EVENT MONITOR FOR LOCKING 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 the
event monitor will re-use the table.
- 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.
- The
FLUSH EVENT MONITOR statement is not applicable to this event monitor
and will have no effect when issued against it.
- 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 DB2 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 32KB table space and associated bufferpool.
- Example
- The lock event monitor currently has the following two record
types:
- Application Info Record
- Application Activity Record
Application Info Record = maximum size 3.5KB
Application
Activity Record = 3KB + SQL statement text size (where SQL statement
text size is max 2MB)
The Application Info Record is very small
and should always be inlined as long as a 4KB page size is being used.
The Application Activity Record will be inlined based on the following
formula:
Application Activity Record < inline length (Pagesize - overhead non-LOB columns (0.5KB))
3KB + SQL statement text < inline length (Pagesize - overhead non-LOB columns (0.5KB))
SQL statement text < Pagesize - nonLOB overhead (1K) - 3KB
SQL statement text < 16KB - 1KB - 3KB
< 12KB
Therefore,
when using a 16KB pagesize, the lock event monitor records will only
be inlined if the SQL statement being captured is less than 12KB in
size.
- Create only one locking event monitor per database.
Creating more than one locking event monitor uses additional processor
cycles and storage, without providing any additional data.
Important: For compatibility with older versions of the product,
all databases are created with the DB2DETAILDEADLOCK event monitor
enabled. The locking event monitor introduced in DB2 Version 9.7 is the preferred mechanism for
collecting data related to locks; the DB2DETAILEDDEALOCK event monitor
is deprecated and might be removed in a future release. When you create
a locking event monitor, disable and drop the DB2DETAILEDDEADLOCK
event monitor to prevent the collection of duplicate, unnecessary
information.
To remove the DB2DETAILDEADLOCK event monitor, issue
the following SQL statements:
SET EVENT MONITOR DB2DETAILDEADLOCK state 0
DROP EVENT MONITOR DB2DETAILDEADLOCK
- 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 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 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 unformatted
event tables that do reside on that database partition is recorded.
Examples
- Example
1: This example creates a locking event monitor LOCKEVMON that
will collect locking events that occur on the database of creation.
CREATE EVENT MONITOR LOCKEVMON
FOR LOCKING
WRITE TO TABLE
This event monitor
writes its output to the following tables:
- LOCK_LOCKEVMON
- LOCK_PARTICIPANTS_LOCKEVMON
- LOCK_PARTICIPANT_ACTIVITIES_LOCKEVMON
- LOCK_ACTIVITY_VALUES_LOCKEVMON
- CONTROL_LOCKEVMON
- Example 2: This example creates a locking event monitor
LOCKEVMON that will collect locking events that occur on the database
of creation and store it in the unformatted event table IMRAN.LOCKEVENTS.
CREATE EVENT MONITOR LOCKEVMON
FOR LOCKING
WRITE TO UNFORMATTED EVENT TABLE (TABLE IMRAN.LOCKEVENTS)
- Example 3: This example creates a locking event monitor
LOCKEVMON that will collect locking events that occur on the database
of creation and store it in the unformatted event table IMRAN.LOCKEVENTS
in table space APPSPACE. The event monitor will deactivate when the
table space becomes 85% full.
CREATE EVENT MONITOR LOCKEVMON
FOR LOCKING
WRITE TO UNFORMATTED EVENT TABLE
(TABLE IMRAN.LOCKEVENTS IN APPSPACE PCTDEACTIVATE 85)