This article is written for IBM® DB2® Universal Database^TM for Windows® and tested using V8.1.

Important: Read the disclaimer before reading this article.

Introduction

Are you migrating your database from Oracle to IBM® DB2® Universal Database^TM? Do you think you might miss some particular Oracle feature? DB2 UDB is such a flexible database that it is possible to fulfill many of your requirements. In this article, I will show you how I implemented one featured called alerts. Alerts are used to allow communication between multiple sessions.

Back to top

Overview of alerts

As shown in Figure 1, alerts are designed for communicating messages between sessions. Alert identification, session identification, messages are stored in a table (here it is alerts.dbms_alert_info). A message sent by one session and read by another session is controlled by a flag setting in the specified table. Parameters (like column length, host variable length, polling intervals, and so on) are designed with the Oracle-to-DB2 migration case in mind. You may wish to increase the size of the alert name and message length based on your requirements.

Back to top

Overview of the design

Here are the objects necessary to implement an alert feature similar to what Oracle has. All objects are included in the download for this article:

• A table to store alert information: alerts.dbms_alert_info
• A stored procedure to register the alert: alerts.register
• Stored procedures to remove the alert: alerts.remove and alerts.delete
• A stored procedure to send the alert message: alerts.signal
• Stored procedures to receive the alert message: alerts.waitone and alerts.waitany
• A UDF implementing delay: alerts.delay
• A UDF implementing session identifier: alerts.session_id

You can change the design I have to suit your particular requirements. A common schema, alerts, is used for all objects. The stored procedures are designed so that you can extend them to capture SQLCODE, SQLSTATE and ERROR_MESSAGE. Extensive exception handling is implemented in all stored procedures listed above, but is not used. You can uncomment the exception handling portion of the sample code and use it. They can also return appropriate return codes, so that necessary actions can be implemented at the host language. Cursors are implemented in all stored procedures, but you may wish to change to fullselect in some places. Public execute permission is granted for all alert objects. You should create all these objects using DBADM or SYSADM authority.

Storing alert information: alerts.dbms._alert_info

Objective: This table stores the alert identifier, session id, message, and a flag. The flag is the indicator for showing whether a message has been placed in the table by the owner session.

This table has four columns:

NAME
This column stores the name of the alert name and is the primary key. In Oracle, name is a 30-character variable length field, whereas here we are defining it as varchar(60). You can, of course, extend your naming pattern.
SID
This is the session identifier. A user-defined function, session_id(), is used to identify the session ID.
MESSAGE
The text of the alert message sent by a user session which needs to be communicated to any other session. It is defined with 1800 characters, but you can extend this if you want.
CHANGED
This is the indicator flag as describe earlier. The default is 'N'.

Authorization: The privileges granted to PUBLIC are : SELECT, INSERT, UPDATE, DELETE

 
CREATE TABLE ALERTS.DBMS_ALERT_INFO  ( 
NAME VARCHAR(60) NOT NULL , 
SID VARCHAR(50) NOT NULL, 
MESSAGE VARCHAR(1800) , 
CHANGED CHAR(1) NOT NULL WITH DEFAULT 'N', 
CONSTRAINT PK_DBMS_ALERT_INFO PRIMARY KEY (NAME), 
CONSTRAINT CC_DBMS_ALERT_INFO CHECK (CHANGED IN ('Y','N','y','n'))); 

Registering the alert: alerts.register

Objective: This stored procedure registers the alert name.

Dependency: session_id() UDF

Input parameter: Alert name (60 characters, maximum)

Output parameters: SQLCODE, SQLSTATE and ERROR_MESSAGE (for extended use)

Authorization: EXECUTE granted to PUBLIC

Functional behavior: This stored procedure registers the alert name and session ID. and stores that. It sets the MESSAGE field to null and the CHANGED field to 'N. On success it returns zero; otherwise -1.

Removing a registered alert name from the session that created it: alerts.remove

Objective: Remove a registered alert name from alerts.dbms_alert_info table.

Dependency: session_id() UDF

Input parameter: Alert name (60 characters maximum)

Output parameters: SQLCODE, SQLSTATE and ERROR_MESSAGE (for extended use)

Authorization: EXECUTE granted to PUBLIC

Functional behavior: A session uses this stored procedure to delete a registered alert. This stored procedure checks the existence of the alert name in the alerts.dbms_alert_info table. It also checks to see if the current session ID matches the session ID registered for the soon to be deleted alert. If it does not match, then the specified alert cannot be deleted. This way we ensure that a session can drop an alert it created. On success it returns zero; otherwise -1.

Removing a registered alert from any session: alerts.delete

Objective: Remove a registered alert name from alerts.dbms_alert_info table

Dependency: None

Input parameter: Alert name (60 characters, maximum)

Output parameters: SQLCODE, SQLSTATE and ERROR_MESSAGE (for extended use)

Authorization: EXECUTE granted to PUBLIC

Functional behavior: This stored procedure functions exactly the same as alerts.remove except that any session can delete the specified alert name from the alerts.dbms_alert_info table.

Sending an alert message: alerts.signal

Objective: Send an alert message to a registered alert.

Dependency: None

Input parameters: Alert name (60 characters, maximum), alert message (1800 characters, maximum)

Output parameters: SQLCODE, SQLSTATE and ERROR_MESSAGE (for extended use)

Authorization: EXECUTE granted to PUBLIC

Functional behavior: This stored procedure first checks that the specified alert name exists and that its flag is set to 'N'. This flag means that this session can send an alert message to a registered alert because no other session has sent or blocked that particular registered alert. On success it returns zero; otherwise -1.

Wait for and read a specific alert: alerts.waitone

Objective: Stored procedure to wait for and read an alert message from a specified alert name.

Dependency: delay() UDF

Input parameter: Alert name (60 characters maximum)

Output parameters: SQLCODE, SQLSTATE and ERROR_MESSAGE (for extended use) and alert message and timeout status.

Authorization: EXECUTE granted to PUBLIC

Functional behavior: This stored procedure first checks that the specified alert exists and that its flag is set to 'Y'. This flag means that this session can receive an alert message from a registered alert because some other session has sent a message and this session is looking for a message from that session.

The procedure waits for a alert message. If there is no message for that alert, it waits for a predefined interval time, then checks again. The default polling interval is 5 seconds, but if the timeout input parameter is set to less than 5 seconds or it is not a multiple of 5 seconds , then the polling interval is set to 1 second. After reading the message, this procedure clears the message field and resets the flag to 'N' so that it is not blocked for other sessions. Other sessions can now send messages using that registered alert. On success it returns zero; otherwise -1.

Wait for and read any alert message: alerts.waitany

Objective: Stored procedure to wait for and read an alert message from any alert name.

Dependency: delay() UDF

Input parameter: timeout value, in seconds.

Output parameters: SQLCODE, SQLSTATE and ERROR_MESSAGE (for extended use) and alert name from where message has come, the message and the timeout status.

Authorization: EXECUTE granted to PUBLIC

Functional behavior: The difference between the waitone and waitany procedures is that waitany looks for the message from any alert name and returns the name of that alert as an output parameter. Waitone looks for the message from a specified alert name.

Another difference between these two procedures is the polling interval. For waitany, the default polling interval is set to 1 second and increases exponentially in each polling cycle. If the timeout time interval falls within a particular polling cycle, this procedure is terminated with a timeout status before starting that polling cycle. On success it returns zero; otherwise, -1.

Delaying execution: alerts.delay

Objective: DB2 UDB does not have any system calls like 'delay' or 'sleep' by which we can suspend the execution for a specified time. This UDF implements a delay in execution by making a system call 'sleep' implemented in Java^TM.

Dependency: None

Input parameter: Delay time, in seconds.

Output parameters: 0 (success), -1 (failure)

Authorization: EXECUTE granted to PUBLIC

Functional behavior: This function implements delay using an OS call, which is more efficient than any other type of implementation.

Catalog statement:

 
CREATE FUNCTION delay(INTEGER) 
RETURNS INTEGER 
EXTERNAL NAME 'delayUDFjar:delayUDF.DELAY(INTEGER)' 
LANGUAGE JAVA 
PARAMETER STYLE JAVA 
NOT DETERMINISTIC 
NO SQL 
FENCED 
RETURNS NULL ON NULL INPUT 
EXTERNAL ACTION; 

Capturing the session identifier: alerts.session_id

Objective: User-defined function to capture session identifier.

Dependency: None

Input parameter: Delay time, in seconds.

Output parameters: 0 (success), -1 (failure)

Authorization: EXECUTE granted to PUBLIC

Functional behavior: This function captures the session identifier stored in SQLUDF_DBINFO internal structure.

Catalog statement:

 
CREATE FUNCTION SESSION_ID() RETURNS CHAR(34) 
EXTERNAL NAME 'sessionUDF!SESSION_ID' 
FENCED 
LANGUAGE C 
PARAMETER STYLE DB2SQL 
NOT VARIANT 
NO SQL 
NO EXTERNAL ACTION 
DBINFO; 

Back to top

Installing the alert sample

Prerequisites: Microsoft VC++ v6.0 and JDK v1.3.1. Both compiler paths must be enabled in the OS environment settings.

1. Unzip the file alert.zip in a folder, such as C:\temp\alert
2. Open the alertsetup.bat batch file and edit the following two lines with appropriate values.
   • In DB2HOMEPATH=C:\SQLLIB, change the SQLLIB path appropriately.
   • In the following line, change the path to C:\temp\alert\delayUDF.jar:
     

     DB2 CALL SQLJ.INSTALL_JAR('file:C:\Documents and Settings\sudipta\My Documents\db2\KnowledgeBase\article\alert\delayUDF.jar','delayUDFjar')

     
     
3. Run the batch file in the DB2 Command Window:
   alertsetup.bat <db_name> <userid for SYSADM/DBADM> <password for SYSADM/DBADM>

   For example: C:\temp\alert>alertsetup.bat SAMPLE TEST TEST01

Back to top

Testing procedures for DB2 UDB Version 8

1. Open two DB2 COMMAND WINDOW sessions (command window with the DB2 environment enabled). Let's denote one session as Session A and the other as Session B.
2. In both sessions, connect to the SAMPLE database.
3. In session A, execute:
   db2 call alerts.register('abc')
4. In session A, execute:
   db2 call alerts.signal('abc','Hello World from Session A.....')
5. In session B, execute:
   db2 call alerts.waitone('abc',?, ?, 30)
6. In session A, execute:
   db2 call alerts.register('xyz')
7. In session A execute:
   db2 call alerts.signal('xyz','Message through alert xyz....')
8. In session B execute:
   db2 call alerts.waitany(?,?,?,30)
9. In session B, execute:
   db2 call alerts.remove('abc')
   This statement will fail because session B is not the creator of the alert named 'abc'.
10. In session A, execute the same command:
    db2 call alerts.remove('abc')
    This will be successful.
11. In session B, execute:
    db2 call alerts.delete('xyz')
    This will succeed, because alerts.delete is designed to delete any alert registered from any session.
12. Execute db2 terminate in both CLP sessions to terminate the connections.

Back to top

Disclaimer

This article contains sample code. IBM grants you ("Licensee") a non-exclusive, royalty free, license to use this sample code. However, the sample code is provided as-is and without any warranties, whether EXPRESS OR IMPLIED, INCLUDING ANY IMPLIED WARRANTY OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE OR NON-INFRINGEMENT. IBM AND ITS LICENSORS SHALL NOT BE LIABLE FOR ANY DAMAGES SUFFERED BY LICENSEE THAT RESULT FROM YOUR USE OF THE SOFTWARE. IN NO EVENT WILL IBM OR ITS LICENSORS BE LIABLE FOR ANY LOST REVENUE, PROFIT OR DATA, OR FOR DIRECT, INDIRECT, SPECIAL, CONSEQUENTIAL, INCIDENTAL OR PUNITIVE DAMAGES, HOWEVER CAUSED AND REGARDLESS OF THE THEORY OF LIABILITY, ARISING OUT OF THE USE OF OR INABILITY TO USE SOFTWARE, EVEN IF IBM HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES.

Back to top

Acknowledgements

The author would like to pay special thanks to his colleagues Sumit Sharma, Paul Yip and Kaushal Veluri for their valuable suggestions.

Back to top

Download

Information about download methods

About the author

Sudipta Mukherjee is a lead technical consultant for Data Management at Solution Partnership Canter (SPC), Developer Relations (DR), IBM India. He is involved in DB2 enablement of ISV applications and also teaches workshops at the Solution Partnership Center in Bangalore. Sudipta earned his Bachelor's degree in Electronics Engineering and Telecommunications from the Regional Institute of Technology (Regional Engineering College, Jamshedpur, India). He is an IBM-certified solution expert in DB2 UDB v7 administration and application development. He is also winner of the Asia Pacific DR Vice President's Knowledge Advantage Award for his contribution to ISV support and DB2 business in India. You can reach him at msudipta@in.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Overview of alerts
• Overview of the design
• Installing the alert sample
• Testing procedures for DB2 UDB Version 8
• Disclaimer
• Acknowledgements
• Download
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

New Offerings

Speed innovation with an affordable, dynamic infrastructure for software delivery

Special offers