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
- Event timestamp
- The time that the event occurred.
- Event ID
- A numeric token that ensures uniqueness in cases where the event timestamp is common.
- 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.
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.
To collect detailed information about activities that might be impacting database performance, behavior, or stability, perform the following steps:
- Decide which change history events are of interest. The
change history event monitor is capable of capturing events describing
- 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
- Create a change history event monitor called
whats_changedby 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
- Activate the change history event monitor called
whats_changedby running the following statement:
SET EVENT MONITOR whats_changed STATE 1
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.