DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE EVENT MONITOR (locking) statement

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:

DBADM authority
SQLADM authority

Syntax


>>-CREATE EVENT MONITOR--event-monitor-name----FOR LOCKING------>

>----WRITE TO UNFORMATTED EVENT TABLE--+-------------------------------------------+---->
                                       '-(--| unformatted-event-table-options |--)-'     

   .-AUTOSTART---.   
>--+-------------+---------------------------------------------><
   '-MANUALSTART-'   

unformatted-event-table-options

   .--------------------------------------------.   
   V  (1)   (2)                                 |   
|----------------+-TABLE--table name----------+-+---------------|
                 +-IN--tablespace name--------+     
                 | .-PCTDEACTIVATE--100-----. |     
                 '-+-PCTDEACTIVATE--integer-+-'

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.

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.

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.

(unformatted-event-table-options)

Identifies the unformatted event table. If a value for unformatted-event-table-options is not specified, CREATE EVENT MONITOR FOR LOCKING processing proceeds as follows:

A derived table name is used (described below).
A default table space is chosen (described below).
PCTDEACTIVATE is set to 100.

TABLE table-name

Specifies the name of the 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.

Please note the following:

The unformatted event 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 an unformatted event 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. An unformatted event table is defined for use by another event monitor if the unformatted event table name matches a value found in the SYSCAT.EVENTTABLES catalog view. If the unformatted event table exists and is not defined for use by another event monitor, then the event monitor will re-use the unformatted event table.
Dropping the event monitor will not drop the unformatted event table. The associated unformatted event tables must be manually dropped after the event monitor is dropped.
The unformatted event tables must be manually pruned.

IN tablespace-name

Defines the table space in which the unformatted event 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.

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 unformatted event table is being created in a 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 (meaning that the event monitor deactivates when the table space becomes completely full). If the table space has auto-resize enabled, then it is suggested that PCTDEACTIVATE be set to 100. This option is ignored for SMS table spaces.

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

Event data is inserted into the unformatted event 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 only written to target unformatted event 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 unformatted event 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 unformatted event tables do not reside on a database partition, but other target unformatted event 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, but will write data to the default unformatted event table LOCKEVMON.

   CREATE EVENT MONITOR LOCKEVMON
     FOR LOCKING
     WRITE TO UNFORMATTED EVENT TABLE

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)