Collecting change history event data

You can use the change history event monitor to collect information about activities that might impact the performance, behavior, and stability of your databases and database management systems.

Before you begin

To create a change history event monitor and collect change history event monitor data, you must have DBADM, or SQLADM authority.

About this task

The change history event monitor captures changes that might impact the running of your regular database workload. When your regular workload experiences a degradation in performance or you observe unexpected behavior, you must determine what changes occurred that might be causing the problem. Each change-related event is uniquely identified by the following three key fields:
Event timestamp
The time that the event occurred.
Event ID
A numeric token that ensures uniqueness in cases where the event timestamp is common.
Member
The database manager process where the event occurred. Member ensures global uniqueness because event timestamp and event ID are only unique per member.

All logical groups contain these three fields and all records or rows corresponding to the same event contain the same values for these fields. These common values facilitate the joining of information across different logical data groups. Utility operations and configuration parameter updates on different members are captured as different events and result in different values for these key fields.

Restrictions

Change history event monitor data can be written only to tables associated with logical data groups. The change history event monitor does not write to unformatted event tables, files, or named pipes.

Procedure

To collect detailed information about activities that might be impacting database performance, behavior, or stability, perform the following steps:

  1. Decide which change history events are of interest. The change history event monitor is capable of capturing events describing the following:
    • Configuration parameter changes
    • Registry variable changes
    • DDL execution
    • Occurrence of a commit, rollback, or rollback to savepoint
    • Event monitor startup information
    • Utility startup information
    • Utility path or file information
    • Utility stop information
    • Utility phase information
  2. Create a change history event monitor called whats_changed by using the CREATE EVENT MONITOR FOR CHANGE HISTORY statement. Use the WHERE EVENT IN clause to specify the change history events that should be captured. The following example shows how to create a change history event monitor that captures all event types: 
    CREATE EVENT MONITOR whats_changed 
   FOR CHANGE HISTORY WHERE EVENT IN (ALL)
   WRITE TO TABLE
  3. Activate the change history event monitor called whats_changed by running the following statement: 
    SET EVENT MONITOR whats_changed STATE 1

Results

Whenever a change history event takes place while the change history event monitor is active (for example a database configuration update), information about the event will be captured in the change history event monitor tables. Only events identified in the WHERE EVENT IN clause of the event monitor will be captured by the change history event monitor.

Example