Implementing Inter-session Alerts in DB2 Universal Database

If you're migrating from Oracle, or even if you're not, the ability to send alert messages between sessions is a useful feature to have. Our author shows you how to use DB2 extensibility features, such as stored procedures and user-defined functions, to implement inter-session alerts.

Share:

Sudipta Mukherjee, Lead Technical Consultant, Data Management, Solution Partnership Center, IBM India Limited

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.



13 March 2003

This article is written for IBM® DB2® Universal DatabaseTM 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 DatabaseTM? 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.


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.

Figure 1. Alerts send messages between sessions
Flow of messages between sessions

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:

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 JavaTM.

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;

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


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.

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.


Acknowledgements

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


Download

DescriptionNameSize
Code samplealert.zip22 KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=14235
ArticleTitle=Implementing Inter-session Alerts in DB2 Universal Database
publish-date=03132003