DB2 10.5 for Linux, UNIX, and Windows

SET EVENT MONITOR STATE statement

The SET EVENT MONITOR STATE statement activates or deactivates an event monitor. The current state of an event monitor (active or inactive) is determined by using the EVENT_MON_STATE built-in function.

The SET EVENT MONITOR STATE statement is not under transaction control.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. 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 DBADM or SQLADM authority.

Syntax


>>-SET--EVENT--MONITOR--event-monitor-name--STATE--------------->

   .-=-.                      
>--+---+--+-0-------------+------------------------------------><
          +-1-------------+   
          '-host-variable-'

Description

event-monitor-name

Identifies the event monitor to activate or deactivate. The name must identify an event monitor that exists in the catalog (SQLSTATE 42704).

new-state

new-state can be specified either as an integer constant or as the name of a host variable that will contain the appropriate value at run time. The following values can be specified:

0: Indicates that the specified event monitor should be deactivated.
1: Indicates that the specified event monitor should be activated. The event monitor should not already be active; otherwise a warning (SQLSTATE 01598) is issued.
host-variable: The data type is INTEGER. The value specified must be 0 or 1 (SQLSTATE 42815). If host-variable has an associated indicator variable, the value of that indicator variable must not indicate a null value (SQLSTATE 42815).

Rules

Although an unlimited number of event monitors may be defined, a maximum of 128 event monitors can be active simultaneously on each database partition. In a multiple partition database environment, a maximum of 32 GLOBAL event monitors can be active simultaneously on each database.
In order to activate an event monitor, the transaction in which the event monitor was created must have been committed (SQLSTATE 55033). This rule prevents (in one unit of work) creating an event monitor, activating the monitor, then rolling back the transaction.
If the number or size of the event monitor files exceeds the values specified for MAXFILES or MAXFILESIZE on the CREATE EVENT MONITOR statement, an error (SQLSTATE 54031) is raised.
If the target path of the event monitor (that was specified on the CREATE EVENT MONITOR statement) is already in use by another event monitor, an error (SQLSTATE 51026) is raised.

Notes

Activating a non-WLM event monitor performs a reset of any counters associated with it. The reset of counters does not occur when activating WLM, locking, and unit of work event monitors.
When a WRITE TO TABLE event monitor is started using SET EVENT MONITOR STATE, it updates the EVMON_ACTIVATES column of the SYSCAT.EVENTMONITORS catalog view. If the unit of work in which the set operation was performed is rolled back for any reason, that catalog update is lost. When the event monitor is restarted, it will reuse the EVMON_ACTIVATES value that was rolled back.
If the database partition on which the event monitor is to run is not active, event monitor activation occurs when that database partition next activates.
After an event monitor is activated, it behaves like an autostart event monitor until that event monitor is explicitly deactivated or the instance is recycled. That is, if an event monitor is active when a database partition is deactivated, and that database partition is subsequently reactivated, the event monitor is also explicitly reactivated.
If an activity event monitor is active when the database deactivates, any backlogged activity records in the queue are discarded. To ensure that you obtain all activity event monitor records and that none are discarded, explicitly deactivate the activity event monitor first before deactivating the database. When an activity event monitor is explicitly deactivated, all backlogged activity records in the queue are processed before the event monitor deactivates.

Examples

Example 1: Activate an event monitor named SMITHPAY.
```
   SET EVENT MONITOR SMITHPAY STATE = 1
```
Example 2: Assume that MYSAMPLE is a multiple partition database with two database partitions, 0 and 2. Partition 2 is not yet active.
```
On database partition 0:

   CONNECT TO MYSAMPLE;
   CREATE EVENT MONITOR MYEVMON ON DBPARTITIONNUM 2;
   SET EVENT MONITOR MYEVMON STATE 1;
```
MYEVMON automatically activates whenever MYSAMPLE activates on database partition 2. This occurs until SET EVENT MONITOR MYEVMON STATE 0 is issued, or partition 2 is stopped.