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.
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).
The privileges held by the authorization ID of the statement must include one of the following authorities:
- SQLADM authority
- DBADM authority
- 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).
- 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.
- Capture all event types.
- Capture execution of the automatic dictionary creation (ADC) utility.
- Capture execution of the online backup utility.
- Capture all configuration parameter and registry variable event types.
- Capture database configuration parameter changes.
- 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.
- Capture database manager configuration parameter changes.
- 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.
- Capture execution for all types of DDL statements.
- Capture execution of index, sequence, table, and temporary table DDL.
- Capture execution of nickname, server, type mapping, user mapping, and wrapper DDL.
- Capture execution of event monitor and usage list DDL.
- Capture execution of audit policy, grant, mask, permission role, revoke, security label, security label component, security policy, and trusted context DDL.
- Capture execution of alias, function, method, module, package, procedure, schema, synonym, transform, trigger, type, variable, and view DDL.
- Capture execution of the ALTER DATABASE statement and buffer pool, partition group, storage group, and table space DDL.
- Capture execution of histogram, service class, threshold, work action set, work class set, and workload DDL.
- Capture execution of XSROBJECT DDL.
- Capture execution of the load utility.
- Capture execution of the table move utility (invocations of the ADMIN_MOVE_TABLE stored procedure).
- 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).
- Capture execution of the redistribute partition group utility.
- Capture immediate registry variables changes.
- Record initial values for registry variables at event monitor startup time.
- Capture execution of the reorg utility.
- Capture execution of the online restore utility.
- Capture execution of the online rollforward utility.
- Capture execution of the runstats utility.
- Capture execution of the load, move table, online backup, online restore, online rollforward, redistribute, reorg and runstats utilities.
- WRITE TO
- Introduces the target for the data.
- 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.
- 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.
- 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
- 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
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.
- 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.
- 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.
- 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.
- 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