Altering an event monitor

You cannot change an event monitor, with one exception: you can add one or more logical data groups to the set of logical data groups that the event monitor collects. You use the ALTER EVENT MONITOR statement to add logical groups.

About this task

When you create an event monitor that writes to tables, by default, all logical data groups of monitor elements that are associated with that event monitor are captured. However, if you include the names of logical data groups in the CREATE EVENT MONITOR statement, only those groups are captured. For example, you might create an activities event monitor that captures data only from the event_activity and event_activity_metrics logical data groups, as shown in the following example:
CREATE EVENT MONITOR myacts FOR ACTIVITIES 
   WRITE TO TABLE
   event_activity, event_activity_metrics
The preceding DDL statement creates an event monitor that writes to two tables: ACTIVITY_myacts and ACTIVITY_METRICS_myacts.

Restrictions

You can use the ALTER EVENT MONITOR statement only to add logical data groups to an event monitor. You cannot remove a logical data group. You also cannot change the name, the target table space, or the value for PCTDEACTIVATE that is associated with the table that is used to capture the data in monitor elements that belong to a data group.

Procedure

To add additional logical data groups to an event monitor:

  1. Decide which logical data group you want to add.
    Using the preceding example of a locking event monitor where only two logical data groups are being captured, assume that you want to add the event_activitystmt and event_activityvals logical data groups.
  2. Formulate an ALTER EVENT MONITOR statement to add these new logical data groups. 
    ALTER EVENT MONITOR mylacts
   ADD LOGICAL GROUP event_activitystmt
   ADD LOGICAL GROUP event_activityvals
  3. Execute the statement.

Results

When the ALTER EVENT MONITOR statement completes execution, two additional tables are created for the event monitor myacts:
  • ACTIVITYSTMT_myacts
  • ACTIVITYVALS_myacts
The next time the event monitor is activated, these tables are populated with data from their corresponding logical data groups.
Remember: If you add new logical data groups to an event monitor, any data that existed for the logical data groups that were originally part of the table will not have any corresponding rows in the tables for the newly added logical group. Adjust your queries as needed, or consider pruning old data from the table after adding the logical groups.

Example

A database administrator creates a locking event monitor called mylocks by using the following SQL statement: 
CREATE EVENT MONITOR mylocks FOR LOCKING WRITE TO TABLE LOCK, LOCK_PARTICIPANTS
This statement collects information for monitor elements in the lock and lock_participants logical data groups. The tables to which the monitor element data is written are created with the default table names LOCK_MYLOCKS and LOCK_PARTICIPANTS_MYLOCKS.
Later on, the database administrator decides that she wants to collect information in the LOCK_PARTICIPANT_ACTIVITIES logical data group. She uses the following statement to modify the event monitor:
ALTER EVENT MONITOR mylocks ADD LOGICAL GROUP LOCK_PARTICIPANT_ACTIVITIES
This statement causes the monitor elements in the lock_participant_activities to be collected in addition to the other elements that already were collected. This new set of monitor elements are written to the table LOCK_PARTICIPANT_ACTIVITIES_MYLOCKS.
Later, the database administrator decides that she also needs the data from the control logical data group. However, she wants this data to be written to a table with a name other than the default name, and to a table space other than the default table space. She uses the following statement:
ALTER EVENT MONITOR mylocks ADD LOGICAL GROUP CONTROL TABLE ctl_mylocks IN mytbsp3
This statement adds the control logical data group to the output of the event monitor. This statement adds the control logical data group to the output of the event monitor. The data is written to the CTL_MYLOCKS table, and the table is written to the table space mytbsp3, instead of the default table space.