DB2 Version 9.7 for Linux, UNIX, and Windows

CREATE EVENT MONITOR (package cache) statement

The CREATE EVENT MONITOR (package cache) statement creates an event monitor that will record events related to the package cache statement.

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

Read syntax diagramSkip visual syntax diagram
>>-CREATE EVENT MONITOR--event-monitor-name--------------------->

>----FOR PACKAGE CACHE----| filter-and-collection-options |----->

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

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

filter-and-collection-options

                                   .-COLLECT BASE DATA-----.   
|--+----------------------------+--+-----------------------+----|
   '-WHERE--| event-condition |-'  '-COLLECT DETAILED DATA-'   

event-condition

   .-AND----------------------------------------------------.   
   V  (1)                                                   |   
|----------+-UPDATED_SINCE_BOUNDARY_TIME------------------+-+---|
           '-+-NUM_EXECUTIONS-+--+->--+--integer-constant-'     
             '-STMT_EXEC_TIME-'  +-<--+                         
                                 +-<=-+                         
                                 +-=--+                         
                                 '->=-'                         

unformatted-event-table-options

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

Notes:
  1. Each condition can be specified only once (SQLSTATE 42613).
  2. Each unformatted event table option can be specified a maximum of one time (SQLSTATE 42613).
  3. 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.
PACKAGE CACHE
Specifies that this event monitor will record an event when the cache entry for a static or dynamic SQL statement is flushed from the package cache. This event monitor is not passive and will start to record events once it is activated.
filter-and-collection-options
Specify a set of filter and collection options.
WHERE
event-condition
Defines a filter that determines whether entries that are flushed from the package cache should cause an event to occur. If the event condition is TRUE for a particular entry that is being flushed from the package cache, then that entry will be recorded as an event.

This clause is a special form of the WHERE clause that should not be confused with a standard search condition. This is a simple WHERE clause that includes the use of NOT, OR, and LIKE operators, unlike the WHERE clause specified for the CONNECTIONS, TRANSACTIONS, and STATEMENTS event monitors.

If the WHERE clause is not specified, all entries flushed from the package cache will be monitored.

UPDATED_SINCE_BOUNDARY_TIME
Specifies that evicted entries, whose metrics were updated after the boundary time, should be collected by this event monitor. The boundary time is set by calling the MON_GET_PKG_CACHE_STMT table function with the value of the input key "updated_boundary_time" set as the name of this event monitor.

The boundary time is initially set to the activation timestamp of the event monitor.

NUM_EXECUTIONS > | < | <= | = | >= integer-constant
Specifies that the monitor element num_executions should be compared with the integer-constant in order to determine whether to generate an event. NUM_EXECUTIONS is the number of times that the section of the evicted entry was executed.
Note: The num_executions monitor element counts all executions of a statement, whether or not the execution of the statement contributed to the activity metrics that are reported.
STMT_EXEC_TIME > | < | <= | = | >= integer-constant
Specifies that the monitor element stmt_exec_time should be compared with the integer-constant in order to determine whether to generate an event. STMT_EXEC_TIME is the total aggregated time spent executing the statement of the evicted entry. The unit of time for the integer-constant must be specified as milliseconds.
COLLECT BASE DATA
Specifies that the same level of information returned by the MON_GET_PKG_CACHE_STMT table function should be captured. This is the default collect option.
COLLECT DETAILED DATA
Specifies that the BASE level information should be collected as well as the runtime executable section of the flushed entry.
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 package cache 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.
(unformatted-event-table-options)
Identifies the unformatted event table. If a value for unformatted-event-table-options is not specified, CREATE EVENT MONITOR FOR PACKAGE CACHE 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.
IN tablespace-name
Specifies the table space in which the unformatted event table is to be created. The CREATE EVENT MONITOR FOR PACKAGE CACHE 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 package cache 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 by using the SET EVENT MONITOR STATE statement or by stopping the instance.

Notes

Use large table space for high throughput
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 32 KB table space, and associated bufferpool.
Inline of package cache records
For the package cache event monitor, the size of the stmt_text, comp_env_desc, and the section_env monitor elements will determine if the package cache record will be inlined or not. If the total of these fields exceeds the table space size, then the record will not be inlined.
Determine if EVENT_DATA is inlined
Use the ADMIN_IS_INLINED and ADMIN_EST_INLINE_LENGTH functions to determine whether the record is inlined and get an estimate of the inline length that is required.

Restrictions

Examples

Example 1: This example creates a package cache event monitor called CACHESTMTEVMON that will collect package cache statement events and write the data to the default unformatted event table CACHESTMTEVMON.
   CREATE EVENT MONITOR CACHESTMTEVMON
     FOR PACKAGE CACHE
     WRITE TO UNFORMATTED EVENT TABLE 
Example 2: This example creates a package cache event monitor called CACHESTMTEVMON that will collect package cache statement events and store it in the unformatted event table ALAN.STMTEVENTS.
   CREATE EVENT MONITOR CACHESTMTEVMON
     FOR PACKAGE CACHE
     WRITE TO UNFORMATTED EVENT TABLE (TABLE ALAN.STMTEVENTS)
Example 3: This example creates a package cache event monitor called CACHESTMTEVMON that will collect package cache statement events and store it in the unformatted event table ALAN.STMTEVENTS in table space APPSPACE. The event monitor will deactivate when the table space becomes 85% full.
   CREATE EVENT MONITOR CACHESTMTEVMON
     FOR PACKAGE CACHE
     WRITE TO UNFORMATTED EVENT TABLE 
        (TABLE ALAN.STMTEVENTS IN APPSPACE PCTDEACTIVATE 85)