"Hey Pete! Give me a call when that package arrives!" Do you wish you had the same functionality in your database? Traditionally, an application had to inspect the database to detect that a specific change had occurred. In high-volume, high-throughput applications, for example, reordering fresh supplies for a fast-food restaurant, the application must inspect the database at very frequent intervals, say once every half an hour. Other applications may require even more frequent inspections and faster reaction times, which can put quite a processing load on the DBMS, as well as increase the network traffic. Organizations sometimes avoid this overhead by running background processes to detect the required conditions. Although these processes can reduce the overhead during peak hours, the applications cannot react immediately to the database changes.
A database event alerter is the mechanism used to notify applications immediately when a specified event occurs, such as a change in the database. In this month's column I illustrate some scenarios in which event alerters can be useful, and I describe how database event alerters work as well as how they are implemented in some DBMSs.
Synchronous notification. With synchronous notification, the application waits until a particular event has occurred before it continues processing. The event is usually a change to the data in the database -- for example, as performed through a SQL INSERT, UPDATE, or DELETE operation.
Section managers, for example, must schedule tasks for their employees. The managers run a simple application that displays the list of unfinished tasks scheduled for the employees in their section. The list should be automatically updated when a new task is scheduled or a task is completed. An event alerter can be used to prevent the application from entering a "busy waiting" state, where it must repeatedly inspect the database until the task list has changed.
Asynchronous notification. With asynchronous notification, the application does not wait for a particular event to occur. It continues with normal processing but is notified when an event of interest occurs. For example, the employees in the production section may also use a list of outstanding tasks. However, they can use other parts of the application to provide them with details about their tasks. When a new task is scheduled for an employee, it should be incorporated into his or her list, but it should not affect other parts of the application. An event alerter can be used to prevent the application from polling, whereby it regularly inspects the database to detect whether an event of interest has occurred.
Interrupt notification. With interrupt notification, the application continues processing but is interrupted when an event of interest occurs. The application should then service the interrupt before continuing. In this case, the event is a change to the data in the database. An event alerter can be used to prevent the application from polling.
Time-based notification. Time-based notification is similar to interrupt notification. The application continues processing but is interrupted when a specific time-based event occurs. The event can be either that a particular time period has expired or that a particular instance of time has arrived. An example of a time-based event involves a time instance in which each task has a scheduled completion date and time. An hour before that scheduled time arrives, the employee tending to the task is warned by a message on his or her terminal.
Event alerters can also be used to prevent the application from "busy timing," which means that the application must regularly inspect a large part of the database to determine whether a particular time instance has materialized or whether a particular time period has expired.
Event definition. Every event that is handled by the database must be defined. The definition names the event and designates it as an event of interest. It does not indicate when the event should be activated, nor which applications should be notified. The event is typically defined by a database administrator (DBA) using the system's database definition facilities.
Event activation. The event-activation mechanism is used to specify when the event occurs or -- in other words -- when the event is raised. A DBA typically creates this mechanism and specifies, through the database definition facilities, the timing when the conditions of the event should be checked, the actual event conditions to be checked, and the name of the event that should be raised if these conditions are met. An event is considered active when all of the conditions specifying the event are met. This means that all of the applications interested in the event should be notified.
Event registration. In most cases, the applications accessing a database will not be notified automatically when events occur. The applications must explicitly indicate which events they are interested in, by issuing an event registration command to the database.
Event notification. An application that has to receive one or more events must indicate how it should be notified -- namely, whether it is waiting synchronously for the event to occur, whether it should be notified asynchronously, or whether it should be interrupted. An application specifies its own notification mode by issuing a command to the database.
Event retrieval. When an application is notified of an event, it must retrieve the event details. The notification usually only informs the application that one of the events in which it is interested has materialized. The application retrieves the event from the event queue to inspect its contents. The contents indicate the type of event and other details specified by the event-activation mechanism.
An Oracle event is usually activated from a trigger. The trigger executes the DBMS_ALERT.SIGNAL function. All of the sessions that have registered interest in the named alert are notified. If the interested sessions are currently waiting, they are awakened. If the interested sessions are not currently waiting, then they are notified the next time they perform a wait call. The Oracle event alerters are transaction-based, which means that the applications waiting for the event will only be notified when the transaction raising the event commits. Note that Oracle triggers can fire before or after the associated table is accessed.
An Oracle application can register for multiple events. It registers for an event by calling the DBMS_ALERT.REGISTER procedure. The name of the alert is the IN parameter. The application can deregister from an event by calling the DBMS_ALERT.REMOVE procedure. If a session dies without removing the alert, that alert is eventually (but not immediately) cleaned up.
An Oracle application can wait for a specific named event or it can wait for any event to occur. In both cases, the application blocks until the event occurs; it cannot do event inspection and continue processing. An application can call the DBMS_ALERT.WAITANY procedure to wait for any of the alerts for which it has registered. An application can wait for a specific event by calling the DBMS_ALERT.WAITONE procedure.
The details of the event that woke up an Oracle application are automatically returned to the application in the out parameters of the DBMS_ALERT.WAITANY or the DBMS_ALERT.WAITONE procedure calls. It does not have to retrieve the event details explicitly with a subsequent call.
Of the scenarios I just described, Oracle can only support synchronous notification.
An OpenIngres event is defined using the CREATE DBEVENT.SQL statement; a defined event is removed using the DROP DBEVENT statement. The GRANT RAISE and GRANT REGISTER statements are used to grant the necessary rights to users to enable them to use the events.
A CA-OpenIngres event is usually activated by a rule and procedure combination. A rule fires after the designated table is accessed as specified. The rule calls a stored procedure to perform the required actions. The rule specifies the timing of the checking of the event's condition and the procedure that should do the checking. The procedure performs the actual checking and activates the event using the RAISE DBEVENT statement. This statement specifies the name of the event and can also include an event message of up to 256 characters. When an event is activated, it is placed on the event queues of all of the applications that registered an interest in it. In CA-OpenIngres, the applications are notified when the event is raised, regardless of transaction boundaries. It is therefore possible for an application to receive an event from a transaction that is subsequently rolled back. Therefore, when your application uses the CA-OpenIngres event alerters, you must check that the transaction was actually committed before you process the event.
Each CA-OpenIngres application that registers for events has a single event queue in the database. All of the events are kept in arrival order in this queue. A CA-OpenIngres application registers for an event by issuing the REGISTER DBEVENT SQL command. It can deregister by issuing the REMOVE DBEVENT command.
A CA-OpenIngres application can be notified of any event, if it has issued the GET DBEVENT WITH [NO] WAIT command. The effect of the WITH WAIT option of the GET DBEVENT command is that the application waits ("sleeps") until the event is raised. The effect of the WITH NOWAIT option of the GET DBEVENT command is that the application merely inspects the event to see if it has been raised; the application continues processing, regardless of the state of the event. An anticipated event can also interrupt an embedded SQL application. The application can issue the WHENEVER SQLEVENT command, which is similar to the error-handling technique used in embedded SQL applications. An application developed in Application-by-Forms (ABF) or in CA-OpenRoad can be interrupted by any event if it has an ON DBEVENT activation block in its code.
A CA-OpenIngres application will be notified of any event arriving in its event queue. The application retrieves the event details through the INQUIRE_SQL command, where it queries the values of the event name and the event text. The application usually must check that the event it received is valid. The CA-OpenIngres event alerters can support synchronous, asynchronous, and some forms of interrupt notification.
The SQLBase events can be raised either at a specific instance in time or periodically. The periodic events can also be started at a specific time. These events are defined using the CREATE EVENT SQLBase SQL statement, with the EVERY clause to specify the repeat interval for the periodic events. The events must be removed using the DROP EVENT SQLBase SQL statement. The only way to terminate a periodic event is to drop it.
A SQLBase event only becomes active when the creating transaction commits. However, a periodic events starts counting from its creation time.
A trigger or stored procedure can raise an event by executing a POST_EVENT InterBase SQL statement, which specifies the event name. An application registers interest in database events by executing an EVENT INIT InterBase SQL statement. This statement associates an application request handle with one or more database event names. The application waits for a specific event request by issuing an EVENT WAIT InterBase SQL statement. The application is then blocked until the named event request occurs. If the event request was associated with more than one database event, the application must interrogate a predefined array to determine which of the database events caused the event request to be raised.
When these alert events occur, users can be notified via the Command Center, as a Panic dialog box sent to a specified console, or the alert can fire the execution a specified program on the same platform as the database server. The latter option lets users react to these events through specialized application programs.
However, when you spawn a task from the SQL Executive, you cannot pass parameters to the task, not even the event message or the event type. The spawned task cannot receive any data from the application event log. This gives you the functionality to detect that something of interest has happened, but you cannot determine the exact details of the event. For example, you can detect that a product's stock has been depleted, but you cannot detect which product it is, even if the product is identified in the event message. If you require this level of detail, your application must make use of the Windows NT API to query the application event log explicitly.
However, the existing database event alerters are quite useful in client/server applications that have requirements for synchronous, asynchronous, and interrupt notification. The database event alerters eliminate polling and busy-waiting processing loops, and they eliminate unnecessary repeated queries to check for changes in the database. In these types of systems, the use of database event alerters can significantly reduce application code and complexity, as well as save considerable CPU time and I/O resources.
What did you think of this article? Send a letter to the editor.