Database Events

Database events enable an application or the DBMS Server to notify other applications that a specific event has occurred. An event is any occurrence that your application program is designed to handle.

The following diagram illustrates a typical use of database events: various applications or database procedures raise database events, and the DBMS Server notifies a monitor (receiving) application that is registered to receive the database events. The monitor application responds to the database events by performing the actions the application designer specified when writing the monitor application.

Database events can be raised by any of the following entities:

An application that issues the raise dbevent statement
An application that executes a database procedure that issues the raise dbevent statement
As the result of firing a rule that executes a database procedure that issues the raise dbevent statement
VMS: Database events cannot be broadcast across the nodes of a VMS cluster.

Example: Database Events in Conjunction with Rules

The following example uses database events in conjunction with rules to maintain inventory stock levels, as follows:

When the inventory table is updated, a rule is fired.
The rule executes a database procedure that checks stock levels.
If the on-hand quantity of a part falls below the required minimum, the procedure raises a stock_low database event.
Another application polls for stock_low database events. When the monitor application receives a stock_low database event, it generates a purchase order.

The detailed steps for this application are as follows:

Create a database event to be raised when the on-hand quantity of a part is low:
create dbevent stock_low;
Create a rule that is fired when the qty_on_hand column of the inventory table is updated; the rule executes a database procedure when the quantity falls below 100 (assuming your business requires a minimum of 100 of each part you stock):
create rule check_qty after update(qty_on_hand) of
inventory where qty_on_hand < 100
execute procedure issue_reorder(partno = old.partno);
Create the database procedure that raises the stock_low database event:
create procedure reorder(partno varchar(25)) as
     begin
          raise dbevent stock_low
               (dbeventtext = partno)
     end
At runtime, the stock monitoring application must register to receive the stock_low database event:
register dbevent stock_low

When the applications are running, the pieces work together as follows:

Whenever the qty_on_hand column of the inventory table is adjusted, the check_qty rule is fired; when the quantity falls below 100, the check_qty rule executes the reorder database procedure. The reorder procedure raises the stock_low database event.
The stock monitoring application issues the get dbevent statement to check the database event queue and extract any database events (for which it is registered) that have been raised.
For each database event detected, the stock monitoring application issues an inquire_sql statement to retrieve information about the database event. If it is the stock_low database event, the stock monitoring application generates a purchase order for the part.

The following diagram illustrates the process:

Database Event Statements

Database events use the following SQL statements:

create dbevent
raise dbevent
register dbevent
get dbevent
remove dbevent
drop dbevent
inquire_sql
set_sql
grant...on dbevent
help permit on dbevent

Create a Database Event

To create a database event, use the create dbevent statement:

create dbevent event_name

event_name
Is a unique database event name and a valid object name.

Database events for which appropriate permissions have been granted (raise or register) can be raised by all applications connected to the database, and received by all applications connected to the database and registered to receive the database event.

If a database event is created from within a transaction and the transaction is rolled back, creation of the database event is also rolled back.

Raise a Database Event

To raise a database event, use the raise dbevent statement:

raise dbevent event_name [event_text] [with [no] share]

The raise dbevent statement can be issued from interactive or embedded SQL applications, or from within a database procedure, including procedures that execute as the result of a rule firing. When the raise dbevent statement is issued, the DBMS Server sends a database event message to all applications that are registered to receive event_name. If no applications are registered to receive a database event, raising the database event has no effect.

A session can raise any database event that is owned by the effective user of the session, and any database event owned by another user who has granted the raise privilege to the effective user, group, role, or public.

The optional event_text parameter is a string (maximum 256 characters) that can be used to pass information to receiving applications. For example, you can use event_text to pass the name of the application that raised the database event, or to pass diagnostic information.

The [no]share parameter specifies whether the DBMS Server issues database event messages to all applications registered for the database event, or only to the application that raised the database event (or, if the database event was raised as the result of a rule firing, issued the query that raised the database event). If share or omit this parameter are specified, the DBMS Server notifies all registered applications when the database event is raised. If noshare is specified, the DBMS Server notifies only the application that issued the query that raised the database event (assuming the program was also registered to receive the database event).

If a transaction issues the raise statement, and the transaction is subsequently rolled back, database event queues are not affected by the rollback: the raised database event remains queued to all sessions that registered for the database event.

Register Applications to Receive a Database Event

To register an application to receive database events, use the register dbevent statement:

event_name
Is an existing database even.

Sessions must register for each database event to be received. A session can register for all database events that the session's effective user owns, and all database events for which the effective user, group, role, or public has been granted register privilege. For each database event, the registration is in effect until the session issues the remove dbevent statement or disconnects from the database.

The DBMS Server issues an error if:

A session attempts to register for a non-existent database event
A session attempts to register for a database event for which the session does not have register privilege
A session attempts to register twice for the same database event. If the register dbevent statement is issued from within a transaction that is subsequently rolled back, the registration is not rolled back.

The register dbevent statement can be issued from interactive or embedded SQL, or from within a database procedure.

Receive a Database Event

To receive a database event and its associated information, an application must perform two steps:

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

The get_dbevent statement gets the next database event, if any, from the queue of database events that have been raised and for which the application session has registered, as shown in the following illustration:

Get dbevent returns database events for the current session only; if an application runs multiple sessions, each session must register to receive the desired database events, and the application must switch sessions to receive database events queued for each session.

The optional with clause specifies whether your application waits for a database event to arrive in the queue. If get dbevent with wait is specified, the application waits indefinitely for a database event to arrive. If get dbevent with wait=wait_value is specified, the application waits the specified number of seconds for a database event to arrive. If no database event arrives in the specified time period, the get dbevent statement times out, and no database event is returned. If get dbevent with nowait is specified, the DBMS Server checks for a database event and returns immediately. The default is nowait.

The with wait clause cannot be specified if the get dbevent statement is issued in a select loop or user-defined error handler.

To obtain database event information, your application must issue the inquire_sql statement, and specify one or more of the following parameters:

dbeventname—The name of the database event (in lowercase letters). If there are no database events in the database event queue, the DBMS Server returns an empty string (or a string containing blanks, if your host language uses blank-padded strings).
dbeventowner—The username of the user that created the database event; returned in lowercase letters.
dbeventdatabase—The database in which the database event was raised; returned in lowercase letters.
dbeventtime—The date and time the database event was raised, in date format. The receiving host variable must be a string (minimum length of 25 characters).
dbeventtext—The text, if any, specified in the optional event_text parameter by the application that raised the database event. The receiving variable must be a 256character string. If the receiving variable is too
small, the text is truncated.

Process Database Events

Three methods can be used to process database events:

The get dbevent statement is used to explicitly consume each database event from the database event queue of the session. Typically, a loop is constructed to poll for database events and call routines that appropriately handle different database events. Get dbevent is a low-overhead statement: it polls the application's database event queue and not the server.
Trap database events using the whenever dbevent statement. To display database events and remove them from the database event queue, specify whenever dbevent sqlprint. To continue program execution without removing database events from the database event queue, specify whenever dbevent continue. To transfer control to a database event handling routine, specify whenever dbevent goto or whenever dbevent call. To obtain the database event information, the routine must issue the inquire_sql statement.
Trap database events to a handler routine, using set_sql dbeventhandler. To obtain the database event information, the routine must issue the inquire_sql statement.

Note: If your application terminates a select loop using the endselect statement, unread database events must be purged.

Database Events (dbevents) are received only during communication between the application and the DBMS Server while performing SQL query statements. When notification is received, the application programmer must ensure that all database events in the database events queue are processed by using the get dbevent loop, which is described below.

Get Dbevent Statement

The following example shows a loop that processes all database events in the database event queue. The loop terminates when there are no more database events in the queue.

loop

     exec sql get dbevent;
     exec sql inquire_sql (:event_name =
          dbeventname);
     if event_name = 'event_1'
          process event 1
     else
     if event_name = 'event_2'
          process event 2
     else
          ...
     endif
until event_name = ''

Whenever Dbevent Statement

To use the whenever dbevent statement, your application must include an SQLCA. When a database event is added to the database event queue, the sqlcode variable in the SQLCA is set to 710 (also the standalone SQLCODE variable is set to 710; SQLSTATE is not affected). However, if a query results in an error that resets sqlcode, the whenever statement does not trap the database event. The database event is still queued, and your error-handling code can use the get dbevent statement to check for queued database events.

To avoid inadvertently (and recursively) triggering the whenever mechanism from within a routine called as the result of a whenever dbevent statement, your database event-handling routine must turn off trapping:

main program:

exec sql whenever dbevent call event_handler;

...

event_handler:

/* turn off the whenever event trapping */
exec sql whenever dbevent continue;

exec sql inquire_sql(:evname=dbeventname...);

process events
return

User-Defined Database Event Handlers

To define your own database event-handling routine, use the exec sql set_sql(dbeventhandler) statement. This method traps database events as soon as they are added to the database event queue; the whenever method must wait for queries to complete before it can trap database events.

Remove a Database Event Registration

To remove a database event registration, use the remove dbevent statement:

remove dbevent event_name

event_name
Specifies a database event for which the application has previously registered.

After a database event registration is removed, the DBMS Server does not notify the application when the specified database event is raised. (Pending database event messages are not removed from the database event queue.) When attempting to remove a registration for a database event that was not registered, the DBMS Server issues an error.

Drop a Database Event

To drop a database event, use the drop dbevent statement:

drop dbevent event_name

where event_name is a valid and existing database event name. Only the user that created a database event can drop it.

After a database event is dropped, it cannot be raised, and applications cannot register to receive the database event. (Pending database event messages are not removed from the database event queue.)

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

Privileges and Database Events

The raise privilege is required to raise database events, and the register privilege is required to register for database events. To grant these privileges, use the grant statement:

grant raise on dbevent event_name to

grant register on dbevent event_name to

To revoke these privileges, use the revoke statement. To display the number for the raise or register privileges, use the help permit statement. To display the permits defined for a specific database event, use the following statement:

help permit on dbevent event_name{, event_name}

Trace Database Events

The following features enable your application to display and trace database events:

To enable or disable the display of database event trace information for an application when it raises a database event, set [no]printdbevents statement.
To enable the display of database events as they are raised by the application, specify set printdbevents. To disable the display of database events, specify set noprintdbevents.
To enable or disable the logging of raised database events to the installation log file, use the set [no]logdbevents statement:
To enable the logging of database events as they are raised by the application, specify set logdbevents. To disable the logging of database events, specify set nologdbevents.
To enable or disable the display of database events as they are received by an application, use the exec sql set_sql(dbeventdisplay = 1| 0 | variable)
Specify a value of 1 to enable the display of received database events, or 0 to disable the display of received database events. This feature can also be enabled by using II_EMBED_SET. For details about II_EMBED_SET, see the System Administrator Guide.
A routine can be created that traps all database events returned to an embedded SQL application. To enable or disable a database event-handling routine or function, your embedded SQL application must issue the exec sql set_sql(dbeventhandler = event_routine | 0) statement:

To trap database events to your database event-handling routine, specify event_routine as a pointer to your error-handling function. For information about specifying pointers to functions, see the Embedded SQL Companion Guide. Before using the set_sql statement to redirect database event handling, create the database event-handling routine, declare it, and link it with your application.