Database Events

A database event enables an application or the DBMS to notify other applications that a specific event has occurred. An event can be any type of program-detectable occurrence. Using database events, you can define an action that can be tied to a programmed response for the purpose of sequencing multiple actions or responding quickly to a specific database condition.

Ways to Work with Dbevent Objects

You can create and work with database events using either VDBA or SQL statements.

In VDBA, database events are implemented using dbevent objects. Using the Dbevents branch for a particular database in the Database Object Manager window, you can:

Create dbevent objects
View existing dbevent objects, including the detailed properties of each individual object
Drop dbevent objects

For the detailed steps for performing these procedures, see the Procedures section of online help.

Using SQL, you can also accomplish these tasks using the create dbevent and drop dbevent statements. For more information, see the SQL Reference Guide.

How Database Events Are Used

After a database event is defined for a table, it can be raised by all applications connected to the database, assuming appropriate privileges have been granted, as described in Database Event Grants.

The event can be raised from interactive or embedded SQL applications, as a result of triggering a security alarm, or in a database procedure (where it can, in turn, be invoked by rules). It can also be received by all applications connected to the database and registered to receive the event.

In general, database events work as follows:

An application or the DBMS raises an event, that is, issues a notification that a defined event has occurred.
The DBMS notifies monitor applications that are registered to receive the event.
The receiving application responds to the event by performing the action the monitor application designer specified when writing the program.

Note: You can also trace database events. For details, see the chapter "Using Monitoring and Tracing Tools" in the System Administrator Guide.

Raise an Event

To raise a database event, use the raise dbevent statement from interactive or embedded SQL applications or from within a database procedure.

A session can raise any event that is owned by the effective user, and any event for which the effective user, group, role, or public has been granted the raise privilege. For more information on granting privileges, see the chapter "Ensuring Access Security."

The raise dbevent statement requires you to specify an event_name parameter, which is the same as the value you enter in the Create Database Event dialog when you create the dbevent object using VDBA.

When the raise dbevent statement is issued, the DBMS sends an event message to all applications that are registered to receive the specified database event. If no applications are registered to receive the event, raising the event has no effect.

The optional event_text parameter is a string that can be used to pass context information or program handles to receiving applications. For example, use event_text to pass the name of the application that raised the event. You can retrieve this value using inquire_sql.

The with [no] share parameter enables you to specify which of the applications registered to receive the event are actually notified. If you specify with share or omit this parameter, the DBMS notifies all registered applications when the event is raised. If you specify with noshare, the DBMS notifies only the application that raised the event (assuming the program was also registered to receive the event).

If a transaction issues the raise dbevent statement and the transaction is subsequently rolled back, event queues are not affected by the rollback. The raised event remains queued to all sessions that registered for the event. The event queue is described in Receive an Event.

For the complete statement syntax and additional information about using the statement, see the raise dbevent entry in the SQL Reference Guide.

Register to Receive an Event

To register to receive a database event, use the register dbevent statement from interactive or embedded SQL applications or from within a database procedure. For each event, the registration is in effect until the session removes the event registration or disconnects from the database.

A session can register for any event that is owned by the effective user, and any event for which the effective user, group, role, or public has been granted the register privilege. Sessions must register for each event to be received. For more information on granting privileges, see the chapter "Ensuring Access Security."

The DBMS issues an error if:

A session attempts to register for a non-existent event.
A session attempts to register for an event for which the session does not have register privilege.
A session attempts to register twice for the same event.

If the register dbevent statement is issued from within a transaction that is subsequently rolled back, the registration is not rolled back.

For the complete statement syntax and additional information about using the statement, see the register dbevent entry in the SQL Reference Guide.

Receive an Event

To receive event information, an application must perform two steps:

Remove the next event from the session's event queue (using get dbevent, or implicitly, using whenever dbevent or set_sql dbeventhandler).
Inquire for event information (using inquire_sql).

Get the Next Event from the Event Queue

The get dbevent statement gets the next event, if any, from the queue of events that have been raised and for which the application has registered.

Obtain Event Information

To obtain event information, your application must issue the inquire_sql statement. With this statement, you specify one or more parameters to determine the type of information to retrieve. For example, to retrieve the text specified in the event_text parameter when the event was raised, use inquire_sql(dbeventtext).

For the complete statement syntax and additional information about using these statements, see the get dbevent and inquire_sql entries in the SQL Reference Guide.

Example: Using Database Events with Rules

The following example illustrates the use of database events in conjunction with rules in a manufacturing application. In this case, events are used to detect when a drill gets too hot; they take the drill off-line:

Create a database event named drill_hot to be raised when the drill overheats.
Create a database procedure that raises the drill_hot event; the procedure is executed when the rule defined in step 3 is triggered.
For example, the following procedure, take_drill_down, logs the time at which the drill was disabled and raises the drill_hot event:

Parameters

drill_id

Statements

insert into drill_log

select date('now'), 'OFFLINE', drill.*

from drill where id = :drill_id;
raise dbevent drill_hot;
Create a rule named drill_hot that is triggered whenever the drill temperature is logged. (This presumes another application that monitors and logs drill temperatures. This is created in the next step.)
For example, create a rule to execute the take_drill_down procedure (created in step 2) after any update operation in which the temperature column was changed. Using the following where clause causes the rule to be fired if the temperature exceeded 500 degrees:

new.temperature > 500

The drill_id parameter must be passed as shown below:

drill_id = drill.id
Finally, create an application that monitors the status of the drills.
In the following example, the monitor application registers to receive the drill_hot event and checks for events. If the monitor application receives the drill_hot event, it sends mail to a supervisor and sends the signals required to disable the drill:

exec sql register dbevent drill_hot;
...
exec sql get dbevent
exec sql inquire_sql (:evname = eventname, ....);
if (evname = 'drill_hot') then
send mail
take drill offline
endif;

The various pieces function together as follows:

The drill monitor application periodically logs the drill temperature to the drill log table.
When the drill monitor application logs a drill temperature in excess of 500 degrees, the drill_hot rule fires.
The drill_hot rule executes the take_drill_down database procedure, which raises the drill_hot event.
Finally, the event monitor process detects the drill_hot event, sends mail to notify the responsible user, and sends a signal that disables the overheated drill.

Remove an Event Registration

To remove a database event registration, use the remove dbevent statement from interactive or embedded SQL applications or from within a database procedure.

Using remove dbevent simply "unregisters" an application for a particular database event. The event is still defined for the database and can be received by other applications that are still registered.

After an event registration is removed, the DBMS does not notify the application when the specified event is raised. Pending event messages are not removed from the event queue.

For the complete statement syntax and additional information about using the statement, see the remove dbevent entry in the SQL Reference Guide.

Drop Database Events

You can drop a dbevent object from the database, in which case it cannot be raised and applications cannot register to receive it. Pending event messages are not removed from the event queue.

If an event is dropped while applications are registered to receive it, the event registrations are not dropped until each application disconnects from the database or removes its registration for the dropped event. If the event is recreated (with the same name), it can again be received by registered applications.