DB2 10.5 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 when the cache entry for a section is flushed from the package cache.

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

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

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

>----WRITE TO--+-TABLE--| formatted-event-table-info |-----------------------+---->
               '-UNFORMATTED EVENT TABLE--+--------------------------------+-'     
                                          '-(--| target-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-'  +-<--+                         
                                 +-<=-+                         
                                 +-=--+                         
                                 '->=-'                         

formatted-event-table-info

|--+---------------------------------------------------+--------|
   | .-,---------------------------------------------. |   
   | V                                               | |   
   '---evm-group--+--------------------------------+-+-'   
                  '-(--| target-table-options |--)-'       

target-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 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.
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
Package Cache
  • PKGCACHE
  • PKGCACHE_METRICS
  • PKGCACHE_STMT_ARGS
  • 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 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.
target-table-options
Identifies options for the target table. If a value for target-table-options is not specified, CREATE EVENT MONITOR FOR PACKAGE CACHE 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-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 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.

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, 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 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 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