Database Event Statements
The SQL statements required to define and use database events are as follows:
• CREATE DBEVENT
• RAISE DBEVENT
• REGISTER DBEVENT
• GET DBEVENT
• REMOVE DBEVENT
• DROP DBEVENT
• INQUIRE_SQL
• SET_SQL
Creating a Database Event
To create a database event, use the CREATE DBEVENT statement:
CREATE DBEVENT event_name
where event_name is a unique database event name and a valid object name.
Database events 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.
Raising a Database Event
To raise a database event, use the RAISE DBEVENT statement:
RAISE DBEVENT event_name [event_text]
The RAISE DBEVENT statement can be issued from interactive or embedded SQL applications. When the RAISE DBEVENT statement is issued, the DBMS 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.
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.
Registering to Receive a Database Event
To register an application to receive database events, use the REGISTER DBEVENT statement:
REGISTER DBEVENT event_name
where event_name is an existing database event. Sessions must register for each database event to be received. For each database event, the registration is in effect until the session issues the REMOVE DBEVENT statement or disconnects from the database.
The DBMS issues an error if a session attempts to register for a database event for which the session does not have register privilege.
The REGISTER DBEVENT statement can be issued from interactive or embedded SQL program.
How a Database Event is Received
To receive a database event and its associated information, an application must perform two steps:
1. Remove the next database event from the session's database event queue (using GET DBEVENT or, implicitly, using WHENEVER DBEVENT or SET_SQL DBEVENTHANDLER).
2. 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, the GET DBEVENT statement times out, and no database event is returned. If GET DBEVENT WITH NOWAIT is specified, the DBMS 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 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.
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 256-character string. If the receiving variable is too small, the text is truncated.
Methods for Processing Database Events
Three methods can be used to process database events:
• Use the GET DBEVENT statement to explicitly consume each database event from the database event queue of the session. Typically, a loop will be constructed that polls for database events and calls 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 bevent 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 may be purged. Note that 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 should ensure that all database events in the database events queue are processed by using the GET DBEVENT loop, which is described below.
Using GET DBEVENT
To get a database event registration, use the GET DBEVENT statement:
EXEC SQL GET DBEVENT [WITH NOWAIT | WAIT [= wait_value]];
To specify whether the GET DBEVENT statement waits for database events or checks the queue and returns immediately, specify the WITH [NO]WAIT clause. By default, GET DBEVENT checks and returns immediately.
If WITH WAIT is specified, GET DBEVENT waits indefinitely for the next database event to arrive. If WITH WAIT = wait_value is specified, GET DBEVENT returns when a database event arrives or when wait_value seconds have passed, whichever occurs first.
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 = ''
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 = ''
Using WHENEVER DBEVENT
To specify an action to occur whenever a DBEvent is raised, use the WHENEVER statement:
EXEC SQL WHENEVER DBEVENT action;
The action can be one of the following: CONTINUE, STOP, or GO TO label.
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 (as will the standalone SQLCODE variable; SQLSTATE is not affected). However, if a query results in an error that resets sqlcode, the WHENEVER statement will not trap the database event. The database event will still be 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 should 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
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
Using 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. For more information, see the Set_sql section in the chapter “SQL Statements.”
Removing a Database Event Registration
To remove a database event registration, use the REMOVE DBEVENT statement:
REMOVE DBEVENT event_name
where event_name specifies a database event for which the application has previously registered. After a database event registration is removed, the DBMS will 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 issues an error.
Dropping 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 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.
To enable or disable the display of database events as they are received by an application, use the following statement:
EXEC SQL SET_SQL(DBEVENTDISPLAY = 1 | 0)
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 your System Administrator Guide.
A routine can be created that will trap 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 following SET_SQL statement:
EXEC SQL SET_SQL(DBEVENTHANDLER = event_routine | 0)
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 your host language 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.
Last modified date: 01/30/2023