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
>>-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:
- 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 ../../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
- 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)
DB2 10.5 for Linux, UNIX, and Windows