CREATE EVENT MONITOR (change history) statement

The CREATE EVENT MONITOR (change history) statement creates an event monitor that can record events for changes to configuration parameters, registry variables, and the execution of DDL statements and utilities.

The event monitor created by the CREATE EVENT MONITOR (change history) statement can also record initial configuration and registry values at event monitor startup time. The set of events recorded depends on the event controls specified in the CREATE EVENT MONITOR 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

The privileges held by the authorization ID of the statement must include one of the following authorities:

SQLADM authority
DBADM authority

Syntax

event-control

formatted-event-table-info

target-table-options

Notes:

¹ Each condition can be specified only once (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 exists in the catalog (SQLSTATE 42710).

FOR

Introduces the type of event to record.

CHANGE HISTORY

Specifies that this event monitor can record events for configuration changes, registry changes, and the execution of DDL statements and utilities. It can also record initial configuration and registry values at event monitor startup time. The set of events recorded depends on the event controls specified in the WHERE EVENT IN clause.

WHERE EVENT IN (event-control, ...)

Specifies one or more event controls used to identify which events are captured by the event monitor.

event-control

ALL: Capture all event types.
ADC: Capture execution of the automatic dictionary creation (ADC) utility.
BACKUP: Capture execution of the online backup utility.
CFGALL: Capture all configuration parameter and registry variable event types.
DBCFG: Capture database configuration parameter changes.
DBCFGVALUES: Record initial values for all database configuration parameters at event monitor startup time if any database configuration parameter update was not captured by the event monitor.
DBMCFG: Capture database manager configuration parameter changes.
DBMCFGVALUES: Record initial values for all database manager configuration parameters at event monitor startup time if any database manager configuration parameter update was not captured by the event monitor.
DDLALL: Capture execution for all types of DDL statements.
DDLDATA: Capture execution of index, sequence, table, and temporary table DDL.
DDLFEDERATED: Capture execution of nickname, server, type mapping, user mapping, and wrapper DDL.
DDLMONITOR: Capture execution of event monitor and usage list DDL.
DDLSECURITY: Capture execution of audit policy, grant, mask, permission role, revoke, security label, security label component, security policy, and trusted context DDL.
DDLSQL: Capture execution of alias, function, method, module, package, procedure, schema, synonym, transform, trigger, type, variable, and view DDL.
DDLSTORAGE: Capture execution of buffer pool, partition group, storage group, and table space DDL.
DDLWLM: Capture execution of histogram, service class, threshold, work action set, work class set, and workload DDL.
DDLXML: Capture execution of XSROBJECT DDL.
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.
REGVAR: Capture immediate registry variables changes.
REGVARVALUES: Record initial values for registry variables at event monitor startup time.
RESTORE: Capture execution of the online restore utility.
ROLLFORWARD: Capture execution of the online rollforward utility.
UTILALL: Capture execution of the load, move table, online backup, online restore, online rollforward, and redistribute utilities.

WRITE TO

Introduces 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. 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.

formatted-event-table-info

Defines the target table for a logical data group. Specify this clause for each grouping that is to be recorded. However, if no evm-group clauses are specified, the groups required for the event-control options specified are created along with the CONTROL, CHANGESUMMARY, and EVMONSTART logical groups.

evm-group

Identifies the 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
Change history	CONTROL CHANGESUMMARY EVMONSTART TXNCOMPLETION DDLSTMTEXEC DBDBMCFG REGVAR UTILSTART UTILSTOP UTILPHASE UTILLOCATION

target-table-options

Identifies the target table for the group.

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 no name is provided, 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

Defines the table space in which the table is to be created. If no table space name is provided, the table space is chosen using the same process as when a table is created without a table space name using CREATE TABLE.

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 full. The default value 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.

If a value for target-table-info is not specified, CREATE EVENT MONITOR processing proceeds as follows:

A derived table name is used.
A default table space is chosen.
The PCTDEACTIVATE parameter defaults to 100.

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.

MANUALSTART

Specifies that the event monitor must be activated manually using the SET EVENT MONITOR STATE statement. After a MANUALSTART event monitor is activated, it can be deactivated by using the SET EVENT MONITOR STATE statement or by stopping the instance.

Notes

Creation of target event tables: The target event tables are created when the CREATE EVENT MONITOR FOR CHANGE HISTORY statement executes if the target tables do not exist.
Previously created event tables: During CREATE EVENT MONITOR FOR CHANGE HISTORY processing, if an event table has already been defined for use by another event monitor, the CREATE EVENT MONITOR FOR CHANGE HISTORY statement fails, and an error is returned to the application program. An event table is defined for use by another event monitor if the event table name matches a value found in the SYSCAT.EVENTTABLES catalog view. If the event table exists and is not defined for use by another event monitor, then a table is not created, any other table options parameters are ignored, and processing continues. A warning is returned to the application program.
Dropping event monitors: Dropping the event monitor does not drop the event tables. The associated event tables must be manually dropped after the event monitor is dropped.
Pruning: The event tables must be manually pruned.
Behavior in a partitioned environment: In a partitioned environment, if some target event tables do not exist on a partition, but other target event tables do exist on that same partition, only the data for the target event tables that do exist on that partition is recorded.
FLUSH EVENT MONITOR: The FLUSH EVENT MONITOR statement is not applicable to this event monitor and has no effect when issued against it.
Modifying event controls after monitor creation: After the change history event monitor is created, the event controls specified using the WHERE EVENT IN clause in the CREATE EVENT MONITOR statement cannot be changed or altered. To change the event controls, the event monitor must be deactivated, dropped, and then recreated specifying a new set of event controls using the WHERE EVENT IN clause.

Examples

Example 1: This example creates a change history event monitor called CFG_WITH_OFFLINE that records configuration changes and initial values for configuration.

CREATE EVENT MONITOR CFG_WITH_OFFLINE
   FOR CHANGE HISTORY WHERE EVENT IN (CFGALL)
   WRITE TO TABLE
     CHANGESUMMARY (TABLE CHG_SUMMARY_HISTORY),
     DBDBMCFG (TABLE DB_DBM_HISTORY),
     REGVAR (TABLE REGVAR_HISTORY)
   AUTOSTART

In this example the target tables are explicitly specified. The previous statement creates the following tables:

CHG_SUMMARY_HISTORY
DB_DBM_HISTORY
REGVAR_HISTORY

Example 2: This example creates a change history event monitor called BKP_REST that collects events describing all online backup and restore utility executions.
```
CREATE EVENT MONITOR BKP_REST
   FOR CHANGE HISTORY WHERE EVENT IN (BACKUP, RESTORE)
   WRITE TO TABLE
```
In this example the target tables are not explicitly specified. The CREATE EVENT MONITOR statement creates only the target tables that are needed based on the controls specified in the WHERE EVENT IN clause, along with tables for the CONTROL, CHANGESUMMARY, and EVMONSTART logical data groups. The BACKUP and RESTORE controls enable collection of utility events for online backup and restore, and require the UTILSTART, UTILSTOP, UTILLOCATION, and UTILPHASE logical data groups. The previous statement creates the following tables:
```
CONTROL_BKP_REST
CHANGESUMMARY_BKP_REST
EVMONSTART_BKP_REST
UTILSTART_BKP_REST
UTILSTOP_BKP_REST
UTILLOCATION_BKP_REST
UTILPHASE_BKP_REST
```