Monitoring table functions provide report information about the state of an instance or a database at a specific point in time. In contrast, event monitors collect monitor data as specific events or transitions occur. Event monitors provide a way to collect monitor data when events or activities occur that cannot be reported by table functions.
For example, suppose you want to capture monitor data whenever a deadlock cycle occurs. If you're familiar with the concept of deadlocks, you know that a special process known as the deadlock detector (daemon) runs quietly in the background and "wakes up" at predefined intervals to scan the locking system for deadlock cycles. If a deadlock cycle is found, the deadlock detector randomly selects, rolls back, and terminates one of the transactions involved in the cycle. As a result, the selected transaction receives an SQL error code, and all locks acquired on its behalf are released so that the remaining transactions can proceed. Information about such a series of events cannot be captured by table monitoring functions or the snapshot monitor because, in all likelihood, the deadlock cycle will have been broken long before data is collected from monitoring elements. An event monitor, on the other hand, could capture important information about such an event because it would be activated the moment the deadlock cycle was detected.
There is another significant difference between using table functions and event monitors: Table functions make use of data collected by monitoring elements in lightweight background processes that commence with database activation. In contrast, event monitors must be specifically created before they can be used. Several event monitors can exist, and each event monitor is activated only when a specific type of event or transition occurs. Table 3 shows the types of events that can cause an event monitor to be activated, along with the kind of monitor data that is collected for each event type.
Table 3. Event monitor types
|Event monitor name||When the event data is generated||Data collected|
|LOCKING||Upon detection of lock timeouts or deadlocks or lock waits beyond a specified duration.||Consolidated comprehensive information regarding applications involved any locking-related event, including the identification of participating statements, statement text, and a list of locks being held.|
|ACTIVITIES||Upon completion of an activity that executed in a WLM service class, workload or work class with COLLECT ACTIVITY DATA set.||Activity-level data for activities involving WLM objects.|
|STATEMENTS||End of SQL statement (for partitioned databases: end of SQL subsection).||Information about requests being made to the database with SQL statement execution, including start/stop time, statement text, fetch count, etc.|
|UNIT OF WORK||Upon completion of a unit of work.||Resource usage information and performance metrics for units of works that run on the system, including start/stop time, WLM information, etc. Recommended over the TRANSACTIONS event monitor.|
|PACKAGE CACHE||As entries are evicted from the package cache.||History of statements and related metrics for statements that are no longer in the package cache.|
|CONNECTIONS||End of connection.||Metrics and other monitor elements for each connection to the database by an application.|
|DATABASE||Database deactivation||Metrics and other monitor elements that reflect information about the database as a whole, including number of connections made to the database, rows of data inserted, etc.|
|Database deactivation||Counters for buffer pools, prefetchers, page cleaners and direct I/O for each buffer pool.|
|TABLES||Database deactivation||Table level counters, such as rows read or written, disk pages used by data, LOB or index objects.|
|STATISTICS||Collected automatically at regular intervals (database configuration parameter wlm_collect_int sets the interval).||Statistics computed from the activities that executed within each service class, workload, or work class that exists on the system.|
|THRESHOLD VIOLATIONS||Upon detection of a threshold violation.||Threshold violation information|
|CHANGE HISTORY||Upon monitor startup, when a configuration parameter or variable changes, or when a command, DDL, or utility completes.||Database and database manager configuration parameter changes, registry variable changes, execution of DDL statements, execution of certain DB2 utilities and commands, and change history event monitor startup.|
Event monitors can report the data they collect in a number of ways. All event monitors can write the data they collect to tables; some write to unformatted event (UE) tables, which can help improve performance. Others can also write directly to a file or named pipe.
You create different types of event monitors by using variations on
CREATE EVENT MONITOR statement. You can use the options for that
statement to specify the type of data that event monitors collect and
how the event monitors produce their output. The basic variants of the
CREATE EVENT MONITOR statement for each event monitor type are shown
NOTE: Parameters shown in angle brackets (
< >) are optional; parameters or options shown in normal
[ ]) are
required; and a comma, followed by ellipses (
...) indicate that the
preceding parameter can be repeated multiple times.
CREATE EVENT MONITORstatement basic variants
CREATE EVENT MONITOR [Name] FOR [DATABASE | TABLES | BUFFERPOOLS | TABLESPACES | STATISTICS | ACTIVITIES | THRESHOLD VIOLATIONS | CONNECTIONS <WHERE [EventCondition]> | STATEMENTS <WHERE [EventCondition]]> , ...] WRITE TO [TABLE [GroupName] (TABLE [TableName]) | PIPE [PipeName] | FILE [PathName]] [MANUALSTART | AUTOSTART] CREATE EVENT MONITOR [Name] FOR [LOCKING | PACKAGE CACHE <EventCondition]> | UNIT OF WORK] WRITE TO [TABLE [GroupName] (TABLE [TableName]) | UNFORMATTED EVENT TABLE [TableName]] [MANUALSTART | AUTOSTART] CREATE EVENT MONITOR [Name] FOR CHANGE HISTORY WHERE EVENT IN [EventControl , ...]] WRITE TO [TABLE [GroupName] (TABLE [TableName])] [MANUALSTART | AUTOSTART]
- Name identifies the name to be assigned to the event monitor being created.
- EventCondition identifies conditions used to determine which CONNECTION, STATEMENT, or SQL section within the PACKAGE CACHE the event monitor collects data for.
- GroupName identifies the logical data group for which the target table is defined (Choice depends on type of event monitor. Following logical data grouping of monitor elements, event monitors that write to tables generally produce one output table for each logical data group of monitor elements that they capture).
- TableName identifies the name assigned to the database table that all event monitor data is to be written to.
- PipeName identifies the name assigned to the named pipe that all event monitor data is to be written to.
- PathName identifies the name assigned to the directory that one or more files containing event monitor data is be written to.
Let's say you want to create an event monitor that captures monitor
data for both buffer pool and table space events and writes all data
collected to a directory named /export/home/bpts_data. To do that, execute
CREATE EVENT MONITOR statement:
CREATE EVENT MONITOR BPTS_EVENTS FOR BUFFERPOOLS, TABLESPACES WRITE TO FILE '/export/home/BPTS_DATA'
Now let's say you want to create an event monitor that captures all
locking related monitoring data, including deadlocks, and have the
data written to a UE table to minimize any potential effects on the
performance of the database. To do that, execute a
CREATE EVENT MONITOR LOCKEVMON FOR LOCKING WRITE TO UNFORMATTED EVENT TABLE (TABLE LOCK_EVENTS)
Depending on the type of event monitor, output can be written to one
or more database tables, one or more external files, a named pipe, or
a UE tables. Table and pipe event monitors stream event records
directly to the table or named pipe specified. File event monitors, on
the other hand, stream event records to a series of eight-character
numbered files that have the extension
00000001.evt, etc.). The data stored
in these files should be treated as if it were a single data stream
stored in a single file, even though the data is actually broken up
into several small pieces (the start of the data stream is the first
byte found in the file named 00000000.evt
and the end of the data stream is the last byte found in the last file
UE tables are relational tables, but they have only a limited number of columns. Most of the data associated with each event is written to a column containing an inline binary (BLOB) object. Writing event data in binary format reduces the time it takes to write each record to the table. For this reason, UE tables are particularly useful where event monitor performance is important.
If you specify the
AUTOSTART option when
creating an event monitor, the monitor will start automatically when
the database containing the event monitor is started. (A database is
started when it is activated with the
DATABASE command or
when the first connection to the database is established.) If you use
MANUALSTART, the resulting event
monitor won't collect monitor data until it has been started. Event
monitors can be started (and stopped) by executing the
MONITOR statement. The basic syntax for this statement is
SET EVENT MONITOR [MonitorName] STATE [0 | 1].
To start an event monitor, you must specify the value 1. To stop an event monitor, specify the value 0.
Some event monitors will only collect data if their corresponding monitoring elements are enabled for collection (LOCKING, ACTIVITIES, STATISTICS, UNIT OF WORK, and PACKAGE CACHE). We have covered how to enable monitor elements collection using the database manager configuration and the WLM clause settings. Other event monitors collect data by default, such as TABLE, which starts collecting data automatically as soon as it is activated.
The SQL function
EVENT_MON_STATE can be
used to determine the current state of any event monitor that has been
defined for a database. This function must be used in a query that
looks something like this:
SELECT EVENT_MON_STATE('CONN_EVENTS') FROM SYSIBM.SYSDUMMY1
(In this example, the table SYSIBM.SYSDUMMY1 is an empty table that is commonly used as a placeholder.)
Once started, an event monitor sits quietly in the background and waits for one of the events or transitions it's designed to monitor to take place. When such an event or transition occurs, the event monitor collects the appropriate monitor data and writes it to the monitor's output target (table, directory, or named pipe).
At times, an event monitor that has a low record-generation frequency
(such as one designed to monitor DATABASE events) can contain event
monitor data in memory that hasn't been written to the event monitor's
target location yet (because only a partial event record exists). To
examine the contents of an event monitor's active internal buffers,
execute the FLUSH EVENT MONITOR SQL statement. The basic syntax for
this statement is
FLUSH EVENT MONITOR
[MonitorName] <BUFFER> where MonitorName identifies the event monitor (by name) that
you want to force to write the contents of its active internal buffers
to its target location.
By default, records written to an event monitor's target
location prematurely are logged in the event monitor log and assigned
a partial record identifier. However, if you specify the
BUFFER option when executing the
EVENT MONITOR statement, only monitor data present in the event
monitor's active internal buffers is written to the event monitor's
target location. No partial record is logged in the event monitor log.
It is important to note that when event monitors are flushed, counters
aren't reset. As a result, the event monitor record that would have
been generated had the
FLUSH EVENT MONITOR statement not been executed
will still be generated when the event monitor is triggered normally.
All event monitors can write their output to regular tables that can be queried directly using SQL. Some event monitors support other output options:
- Regular tables — For a given event, each
monitor element or metrics collected for the event is written to
its own column in the table. This makes it possible to use a
SELECTstatement to query the output and examine the values for a specific monitor element.
- UE tables — For a given event, most of the data is written to a column as an inline binary (BLOB) object. SQL cannot be used to extract legible data, post-processing on the UE table is required.
- Files — Writing to files avoids any event monitor
additional processing by the database manager and allows offline
analysis of the extracted data. Two parameters control the amount
of space available for use (
MAXFILES), and once the space limit is reached, the event monitor will automatically flush all events and stop. The default setting for both parameters is
NONE, which indicates that there is no space limit.
- Pipes — Event monitor output can be written to a named pipe. This might be useful if you need to manipulate event data in real time by an external application.
Table 4 lists other supported output options for different types of event monitors.
Table 4. Output options for event monitors
|Event monitor||Regular table||UE table||File||Named pipe|
|UNIT OF WORK||Yes||Yes|
To access information collected in UE tables, a text report can be generated from the tables. Alternatively, data can be extracted into relational tables or XML so that it can be queried using SQL or pureXML. The following tools and methods are available:
db2evmonfmttool — A Java-based generic XML parser tool that produces a readable text output or a formatted XML output from UE tables. The tool allows selecting events of interest by filtering based on event ID, event type, time period, application, workload, or service class. The output format can also be completely controlled by creating your own XSLT style sheets instead of using the ones provided with db2evmonfmt.
EVMON_FORMAT_UE_TO_XMLroutine — Extracts data from a UE table into an XML document.
EVMON_FORMAT_UE_TO_TABLESroutine — Extracts data from a UE table into a set of relational tables.
EVMON_FORMAT_UE_TO_TABLES routines, you can
SELECT statement to specify the exact rows from the UE table
that you want to extract.
To view event monitor data written to files or named pipes,
you must use the text-based event monitor productivity tool, which
retrieves information from an event monitor data file or named pipe
and generates a formatted report. To activate the event monitor
productivity tool, execute the
command. The basic syntax for this command looks like
db2evmon -db [DatabaseAlias] -evm [MonitorName] where
DatabaseAlias identifies the database (by alias) on
which the event monitor whose data is to be displayed is defined, and
MonitorName identifies the name assigned to the event
monitor whose data is to be displayed. Or
db2evmon -path [MonitorTarget], where MonitorTarget identifies the location (directory or
named pipe) where data that has been collected by the event monitor
specified is stored.
Event monitors should only be used to monitor specific events or short workloads. They are designed to provide specific information that can be used to diagnose problems or undesired behavior of a database/application.
Unlike monitoring table functions, most event monitors have a considerable impact on performance. This is due to the amount of information written out for each event object. Additionally, SQL statement event monitors cause an even heavier performance impact because of all the extra work the database engine has to perform each time a query is executed; instead of being able to simply execute a query, the DB2 Database Manager must also generate and record all the characteristics and runtime information associated with the query. If this information is written to a text file, that slows things down even further.
While on the subject of files, when creating event monitors that write data to files, it is a good idea to impose file size limits to control the amount of disk space that event monitor output will consume. Otherwise, if you are monitoring a high-volume OLTP system, the output can quickly grow to hundreds of megabytes.
A common use for an event monitor is to capture deadlock information. If an event monitor is not used, it is almost impossible to determine exactly what locks and applications were involved in a deadlock cycle. A locking event monitor will collect information about all the applications and their locks when a deadlock cycle occurs. Armed with this information, the precise SQL statement that caused the deadlock cycle can be monitored or altered to correct the situation. Don't forget that the application that DB2 labels as the cause of a deadlock is the last application involved in the deadlock cycle; the real cause may actually be a transaction started much earlier by another application. Examine all the locks and applications involved to correctly determine where the problem originated.
Another common use for event monitors is to keep track of SQL statement processing. When an event monitor is used to capture information about every SQL statement executed, the properties of each statement, such as number of rows read, selected, deleted, etc., is recorded and is not presented as an aggregate total, as is the case with monitoring table functions. Furthermore, because the execution timeframe and start and stop times are recorded as well, detailed analysis of transactions and of how the execution of SQL by one application affects the execution of SQL by others can be performed. However, because of the volume of information produced and performance overhead required to run an SQL statement monitor, such a monitor should only be used for short tests or problem determination, and not in a production environment.