Package cache statement eviction event monitoring

The package cache event monitor captures data related to statement entries that have been flushed from the database package cache. This event monitor provides the history of the contents of the package cache which can help with SQL query performance and problem determination issues.

Overview

The package cache event monitor collects the same information as the MON_GET_PKG_CACHE_STMT table function, including the full set of available activity metrics and the executable section information of an entry.

Starting in Version 10.1, you can get information about input arguments related to the longest-running statement. This statement is the one associated with the monitor element max_coord_stmt_exec_time. The input arguments associated with this statement are recorded as part of the pkgcache_stmt_args logical data group.

Two control mechanisms on the CREATE EVENT MONITOR statement help limit the volume of data that can be captured. The two control mechanisms provide the following capabilities:

Filter entries with the WHERE clause based on one or more of the following conditions:
- Whether the last update of the metrics for an entry occurs after a specific time before it is evicted (UPDATED_SINCE_BOUNDARY_TIME). An entry will only be collected if the time that the metrics were last updated is more recent than boundary time defined for the event monitor. The boundary time for an event monitor can be set using the MON_GET_PKG_CACHE_STMT table function. If no boundary time has been set for the event monitor, the UPDATED_SINCE_BOUNDARY_TIME clause will have no effect.
- The number of times the section of an entry was executed (NUM_EXECUTIONS)
- The total aggregated amount of time spent executing the statement (STMT_EXEC_TIME)
COLLECT DATA clause options:
- COLLECT BASE DATA
  Same information collected as the MON_GET_PKG_CACHE_STMT table function, as well as the full set of available activity metrics
- COLLECT DETAILED DATA
  Collects the same information gathered with the COLLECT BASE DATA clause and includes the executable section of the entry

When you need to investigate the individual execution of an SQL statement, you can use the MON_GET_PKG_CACHE_STMT table function (if the entries are still in the package cache) to compare the behavior of a cached entry relative to others. The execution metrics, compilation environment, and detailed descriptions for a cached entry are available for diagnostic purposes.

If an entry has already been flushed from the package cache, you can use the package cache event monitor to review the history of the cached entries which were flushed from the package cache. The history data contains the same information that the MON_GET_PKG_CACHE_STMT table function provides. In addition, the event monitor also provides the executable section of the statement. All of this applies to both dynamic and static SQL statements.

Creating a package cache event monitor

To create a package cache event monitor and collect package cache event monitor data, you must have DBADM or SQLADM authority.

A package cache event monitor can write its output to either a regular table or an unformatted event table.

Before you create a package cache event monitor, identify the table space where you plan to store the output for your event monitor. The CREATE EVENT MONITOR statement will assume a default table space if you do not specify one. However, the recommended practice is to have a table space dedicated and configured to store the output table or tables associated with any event monitor. If you are using an unformatted event table, create package cache event monitors in table spaces with at least an 8K pagesize to ensure that event data is contained within the inlined BLOB column of the UE table. If the BLOB column is not inlined, then the performance of writing and reading the events to the unformatted event table might not be efficient.

To setup a package cache event monitor using defaults and best practices, complete the following steps:

Create the event monitor by issuing the CREATE EVENT MONITOR statement. The following example uses defaults where possible and specifies to store the unformatted event table in an existing table space MY_EVMON_TABLESPACE:
```
CREATE EVENT MONITOR MY_PKGCACHE_EVMON
  FOR PACKAGE CACHE
   WRITE TO UNFORMATTED EVENT TABLE (IN MY_EVMON_TABLESPACE)
```

Enabling data collection

To enable data collection, you must activate the event monitor using the SET EVENT MONITOR STATE statement. The package cache event monitor is not a passive event monitor; following activation, it automatically starts collecting data whenever a statement is flushed from the package cache and meets the filter criteria set at the time of creation of the package cache event monitor.

Accessing event data captured by a package cache event monitor

A unit of work event monitor can write data to a regular table or it can write data in binary format to an unformatted event (UE) table. You can access the data in regular tables by using SQL.

To access data in a UE table, use one of the following table functions:

EVMON_FORMAT_UE_TO_XML: Extracts data from an unformatted event table into an XML document.
EVMON_FORMAT_UE_TO_TABLES: Extracts data from an unformatted event table into a set of relational tables.

When you use one of these table functions, you can specify which data to extract by including a SELECT statement as one of the parameters to the function. You have full control over selection, ordering, and other aspects provided by the SELECT statement.

The schema file ~/sqllib/misc/DB2EvmonPkgCache.xsd is used to document the expected output of the package cache event monitor report in an XML document. The schema file will reference a common monitor schema file (DB2MonCommon.xsd) to avoid duplicating the common contents.

An XML stylesheet is provided in ~/sqllib/samples/jdbc/DB2EvmonPkgCache.xsl.

Use these table functions to specify the data to extract using a SELECT statement. You have full control over selection, ordering, and other aspects provided by the SELECT statement.

You can also use the db2evmonfmt command to perform the following tasks:

Select events of interest based on the following attributes: executable ID, section type, query cost estimate, statement package cache ID, and flush time.
Choose whether to receive the output in the form of a text report or a formatted XML document.
Control the output format by creating your own XSLT style sheets instead of using the ones provided by the db2evmonfmt command.

For example, the following command provides a package cache report that:

Selects package cache events that have occurred in the past 24 hours in the database SAMPLE. These event records are obtained from the unformatted event table called SAMPLE_PKGCACHE_EVENTS.
Provides formatted text output using the DB2EvmonPkgCache.xsl style sheet.

java db2evmonfmt -d SAMPLE -ue SAMPLE_PKGCACHE_EVENTS -ftext -ss DB2EvmonPkgCache.xsl -hours 24