DB2 Version 9.7 for Linux, UNIX, and Windows

DBMS_ALERT module

The DBMS_ALERT module provides a set of procedures for registering for alerts, sending alerts, and receiving alerts.

Alerts are stored in SYSTOOLS.DBMS_ALERT_INFO, which is created in the SYSTOOLSPACE when you first reference this module for each database.

The schema for this module is SYSIBMADM.

The DBMS_ALERT module includes the following system-defined routines.

Table 1. System-defined routines available in the DBMS_ALERT module
Routine name	Description
REGISTER procedure	Registers the current session to receive a specified alert.
REMOVE procedure	Removes registration for a specified alert.
REMOVEALL procedure	Removes registration for all alerts.
SIGNAL procedure	Signals the occurrence of a specified alert.
SET_DEFAULTS procedure	Sets the polling interval for the WAITONE and WAITANY procedures.
WAITANY procedure	Waits for any registered alert to occur.
WAITONE procedure	Waits for a specified alert to occur.

Usage notes

The procedures in the DBMS_ALERT module are useful when you want to send an alert for a specific event. For example, you might want to send an alert when a trigger is activated as the result of changes to one or more tables.

The DBMS_ALERT module requires that the database configuration parameter CUR_COMMIT is set to ON

Example

When a trigger, TRIG1, is activated, send an alert from connection 1 to connection 2 . First, create the table and the trigger.

CREATE TABLE T1 (C1 INT)@

CREATE TRIGGER TRIG1
AFTER INSERT ON T1
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN ATOMIC
CALL DBMS_ALERT.SIGNAL( 'trig1', NEW.C1 );
END@

From connection 1, issue an INSERT statement.

INSERT INTO T1 values (10)@
-- Commit to send messages to the listeners (required in early program)
CALL DBMS_ALERT.COMMIT()@

From connection 2, register to receive the alert called trig1 and wait for the alert.

CALL DBMS_ALERT.REGISTER('trig1')@
CALL DBMS_ALERT.WAITONE('trig1', ?, ?, 5)@

This example results in the following output:

  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : -

  Parameter Name  : STATUS
  Parameter Value : 1

  Return Status = 0