CREATE EVENT MONITOR (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

Syntax

filter-and-collection-options

event-condition

formatted-event-table-info

target-table-options

Notes:

• ¹ Each condition can be specified only once (SQLSTATE 42613).
• ² 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.

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 Logical data groups and event monitor output tables.

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

• The target table is created when the CREATE EVENT MONITOR FOR PACKAGE CACHE statement executes, if it doesn't already exist.
• During CREATE EVENT MONITOR FOR PACKAGE CACHE processing, if a table is found to have already been defined for use by another event monitor, the CREATE EVENT MONITOR FOR PACKAGE CACHE 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.
• In a partitioned database environment, data is written to target tables only on the members where their table spaces exist. If a table space for a target table does not exist on a member, the event data that would be written to that target table is not captured on that member. This behavior allows users to choose a subset of members for monitoring, by creating a table space that exists only on certain members.
• In a partitioned database environment, data is written to target tables only on the member where the entries are evicted from the database package cache.
• In a partitioned database environment, if some target tables do not reside on a member, but other target tables do reside on that same member, only the data for the target unformatted event tables that do reside on that member is recorded.
• The FLUSH EVENT MONITOR statement is not applicable to this event monitor and will have no effect when issued against it.
• After the package cache event monitor is created, the filter and control options cannot be changed or altered. To change the filter and control options, the event monitor must be deactivated, dropped, and then recreated with the new filter and control options.

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

• During database deactivation, evicted entries will not be collected by the package cache event monitor.

Examples

• Example 1: This example creates a package cache event monitor called CACHEEVMON that will collect data related to package cache section eviction events and write the data to tables.

     CREATE EVENT MONITOR CACHEEVMON
       FOR PACKAGE CACHE
       WRITE TO TABLE 

  This event monitor writes its output to the following tables:

  • PKGCACHE_CACHEEVMON
  • PKGCACHE_METRICS_CACHEEVMON
  • PKGCACHE_STMT_ARGS
  • CONTROL_CACHEEVMON
• Example 2: This example creates a package cache event monitor called CACHESTMTEVMON that will collect data related to package cache section eviction 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 data related to package cache section eviction 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)