DB2 10.5 for Linux, UNIX, and Windows

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:

Syntax

Read syntax diagramSkip visual syntax diagram
>>-CREATE EVENT MONITOR--event-monitor-name--------------------->

                                         .-,---------------------.      
                                         V                       |      
>--FOR CHANGE HISTORY WHERE EVENT IN--(------| event-control |---+--)-->

                                                   .-AUTOSTART---.   
>--WRITE TO TABLE--| formatted-event-table-info |--+-------------+-><
                                                   '-MANUALSTART-'   

event-control

|--+--------------+---------------------------------------------|
   +-ALL----------+   
   +-BACKUP-------+   
   +-CFGALL-------+   
   +-DBCFG--------+   
   +-DBCFGVALUES--+   
   +-DBMCFG-------+   
   +-DBMCFGVALUES-+   
   +-DDLALL-------+   
   +-DDLDATA------+   
   +-DDLFEDERATED-+   
   +-DDLMONITOR---+   
   +-DDLSECURITY--+   
   +-DDLSQL-------+   
   +-DDLSTORAGE---+   
   +-DDLWLM-------+   
   +-DDLXML-------+   
   +-LOAD---------+   
   +-MOVETABLE----+   
   +-REDISTRIBUTE-+   
   +-REGVAR-------+   
   +-REGVARVALUES-+   
   +-REORG--------+   
   +-RESTORE------+   
   +-ROLLFORWARD--+   
   +-RUNSTATS-----+   
   '-UTILALL------'   

formatted-event-table-info

|--+---------------------------------------------------+--------|
   | .-,---------------------------------------------. |   
   | V                                               | |   
   '---evm-group--+--------------------------------+-+-'   
                  '-(--| target-table-options |--)-'       

target-table-options

   .--------------------------------------------.   
   V  (1)   (2)                                 |   
|----------------+-TABLE--table-name----------+-+---------------|
                 +-IN--tablespace-name--------+     
                 | .-PCTDEACTIVATE--100-----. |     
                 '-+-PCTDEACTIVATE--integer-+-'     

Notes:
  1. Each condition can be specified only once (SQLSTATE 42613).
  2. 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.
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 the ALTER DATABASE statement and 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).
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.
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.
UTILALL
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.
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