Monitoring utility history using the change history event monitor

The change history event monitor can capture a number of events related to the execution of utilities. These events can be used to monitor the history of utility execution on the database server. This event history is written to logical data groups, where each logical data group has an associated event monitor table.

The execution of a utility can generate one or more events in the change history event monitor. For example, the execution of the REORG utility generates two events that mark the beginning and end of the REORG operation. There is a one-to-many mapping between an event and a logical data group. An event can write information to more than one logical data group and can write more than one entry (row) to the table associated with a given logical data group. Each event corresponding to a particular invocation of a utility is identified by the utility_invocation_id element. The utility_invocation_id is a binary token that uniquely identifies a given invocation of a utility. The utility_invocation_id is the same on each member where the utility is executing. The utility_invocation_id will retain its uniqueness across database deactivation, reactivation, and member shutdown, allowing quick identification of all event monitor records corresponding to a given invocation of a utility. There is no need to join with other fields or worry about duplicate identifiers.

Using the utility_invocation_id you can identify all events describing a particular invocation of a utility. For example, when the REORG command is issued on a table, a UTILSTART event is generated when the utility starts execution and a UTILSTOP event is generated when the utility completes execution. Both the UTILSTART and UTILSTOP events will have the same utility_invocation_id, because they describe the same invocation of the REORG command. The utility_invocation_id can be used to join these events to compute the elapsed time for the utility.

The change history event monitor can monitor the execution of the following utility types:

BACKUP
LOAD
MOVETABLE
ONLINERECOVERY
REDISTRIBUTE
REORG
RESTORE
ROLLFORWARD
RUNSTATS

The change history event monitor will not capture the execution of an offline backup, restore, or rollforward operation. Online recovery operations will only be captured if they perform an asynchronous backward phase. Note that utility events are only be captured if the change history event monitor is active during the execution of the utility. If the event monitor is deactivated before the utility executes, no events are captured for the execution of that utility. For example, if the utility needs exclusive access to the table space where the event monitor target tables reside.

The following table lists the change history event monitor logical data groups and associated tables are associated with utility execution events. The table name is derived by concatenating the name of the logical data group used to populate the table with the name given to the event monitor when it was created using the CREATE EVENT MONITOR statement. The table names shown are the default table names when a name is not specified as part of the CREATE EVENT MONITOR statement.

Table 1. Logical data groups populated during utility executions
Logical data group	Default table name	Contains
CHANGESUMMARY	CHANGESUMMARY_evmon-name	Summary of all events captured by the change history event monitor
UTILSTART	UTILSTART_evmon-name	Utility startup information
UTILLOCATION	UTILLOCATION_evmon-name	Utility path or file information
UTILSTOP	UTILSTOP_evmon-name	Utility stop information
UTILPHASE	UTILPHASE_evmon-name	Utility phase information

The WHERE EVENT IN clause of the CREATE EVENT MONITOR (change history) statement controls which utilities are monitored by the change history event monitor. The following list indicates which controls enable the capture of which utilities:

UTILALL: Capture execution of the load, move table, online backup, online recovery, online restore, online rollforward, redistribute, reorg and runstats utilities.
BACKUP: Capture execution of the online backup utility.
LOAD: Capture execution of the load utility.
MOVETABLE: Capture execution of the table move utility (invocations of the ADMIN_MOVE_TABLE stored procedure).
ONLINERECOVERY: Capture execution of a crash recovery operation which utilized an asynchronous backward phase that allowed for database connectivity during the operation. (This includes the implicit crash recovery performed during an HADR TAKEOVER).
REDISTRIBUTE: Capture execution of the redistribute partition group utility.
REORG: Capture execution of the reorg utility.
RESTORE: Capture execution of the online restore utility.
ROLLFORWARD: Capture execution of the online rollforward utility.
RUNSTATS: Capture execution of the runstats utility.