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

Introduction

In my previous article, Implementing Inter-session Alerts in DB2 Universal Database, you saw how to implement a communication channel using an alert between two database sessions. This article presents another implementation of communication between two database sessions using message pipes. Both alerts and pipes can function in either asynchronous or synchronous mode. The difference between the two, is that pipes can transfer a packet of an individual message from one session to another, which is not possible using alerts. An alert is mostly used for taking some action based on an event in another session. Using pipes, you can actually transfer a packet of messages, which consist of different data types, from one session to another. This implementation is similar to Oracle DBMS_PIPE package with some changes. It is very useful if you are migrating your application from Oracle to DB2 Universal Database.

This implementation of a pipe package contains:

• 3 catalog tables
• 2 user-defined functions
• 28 SQL stored procedures

Back to top

How it works

There should be two participating sessions. One session (assume A) prepares the message packet and sends it to the database catalog. Another session (assume B) reads the message packet sent by session A and closes the pipe.

Session A does the following:

1. Creates a named pipe with or without size limit and of type public or private.
2. Packs a message into a named or temporary pipe as open if it exists in that session. If the named pipe does not exist, then it creates a temporary public type pipe with an unlimited size and packs the message. If the pipe is of type private, then all messages in that pipe are encrypted.
3. Continues packing if the message packet consists of multiple messages.
4. Sends the message packet. If this pipe is a temporary one, then a name is given for that temporary pipe.

Session B does the following:

1. Receives the named pipe and opens it.
2. Checks the data type of the message. Checking and reading messages are done using FIFO (First In First Out) method. After checking the data type, it informs about the same to the calling application.
3. Calling application then unpacks that message and stores it in an appropriate variable (data type is very important). If the pipe is of type private, then the messages are decrypted and then read. If there is no more messages in that pipe, the pipe is closed; otherwise step 2 & 3 continue repeatedly.

In addition to the above functional behavior, there are three more features available for pipe administration:

• A named pipe, which is closed, can be removed by the user who has created the pipe.
• A named pipe, which is closed, can be removed by the database administrator, regardless of the user who has created the pipe.
• A named pipe, which is closed, can be cleaned and reused by the user who has created the pipe.

Back to top

Design overview

Three types of objects were implemented in DB2 Universal Database to support the pipe feature; all are available in the download for this article.

Catalog Tables

• DBMS_PIPE.PIPES - stores pipe definition
• DBMS_PIPE.PIPEMEMBERS - stores messages in plain string format
• DBMS_PIPE.PIPEMEMBERS_ENCRYPT - stores messages in encrypted string format

User-defined functions

• DBMS_PIPE.DELAY - provides delay in seconds
• DBMS_PIPE.SESSION_ID - captures session ID from database manager

Note: Source code for these two functions are provided in the article, Implementing Inter-session Alerts in DB2 Universal Database. The same functions are used here, but in a compiled format.

SQL stored procedures

• DBMS_PIPE.CREATE_PIPE - create named pipe, 3 Nos, overloaded
• DBMS_PIPE.PACK_MESSAGES_xxxx - pack messages into the pipe, 9 Nos, for different data types
• DBMS_PIPE.SEND_MESSAGE - send pipe for processing, 2 Nos, overloaded
• DBMS_PIPE.RECEIVE_MESSAGE - retrieve pipe for reading
• DBMS_PIPE.NEXT_ITEM_TYPE - check first packed and unchecked message data type
• DBMS_PIPE.UNPACK_MESSAGES_x - read message and close pipe, 9 Nos, for different data types
• DBMS_PIPE.PURGE - cleans messages in the pipe and reset the pipe state to PACKING
• DBMS_PIPE.REMOVE_PIPE - removes a pipe from the system by creator
• DBMS_PIPE.DELETE_PIPE - removes a pipe by administrator

DBMS_PIPE.PIPES catalog table has 8 fields:

• PID - Pipe id
• PNAME - Pipe name
• PTYPE - Type of the pipe, (Y - Private, N - Public)
• POWNER - Pipe creator's name
• PSTATUS - Status of the pipe (P - Packing, S - Sent, O - Open, R - Reading, C - Closed)
• PNDATA - No. Of messages in the pipe
• PSIZE - Size limit of the pipe (in bytes)
• PTEMPNAME - Temporary name of the pipe (set to session id)

DBMS_PIPE.PIPEMEMBERS catalog table has 6 fields:

• SLNO - a sequence number
• PID - pipe id (linked to PIPES.PID using foreign key)
• MID - member/message id in the pipe
• MTID - member/message data type id (as mentioned below)
• NDIF - state of the member/message (N - Not checked, C - Checked, R - Read)
• DATA - member/message data (in plain string format)

DBMS_PIPE.PIPEMEMBERS_ENCRYPT catalog table has the same fields, but it stores data in the DATA field in encrypted string format.

There are four utilities provided in the download. Execute them as SYSADM and from COMMAND WINDOW, not from an MS-DOS window:

• pipesetup.bat - compiles all stored procedures and registers all above mentioned objects.
• getroutines.bat - exports all SQL stored procedures into sar files in binary form.
• putroutines.bat - imports all SQL stored procedures from sar files without recompiling them.
• cleanup.bat - cleans up the entire pipe system implementation from the database system.

Note: VC++ v5.0/v6.0 or CYGWIN (emulated Linux in Windows) GCC and Java compiler need to be available in the server machine to compile SQL stored procedures. If you do not have VC++ compiler, you can use CYGWIN's GCC compiler to compile the same. For more information, read the article Compiling DB2 UDB Stored Procedures with GCC on Windows.

Back to top

Differences, advantages and limitations

• The pipe feature is implemented using three catalog tables and not using any shared memory. So, they are more stable irrespective of the memory available. This implementation is more reliable since there is no direct shared memory access and all data manipulation is done by database server.
• There is a limitation of message size. One single message can be up to 32631 bytes. But the size of the message packet (i.e. pipe size) does not have any size limitation against Oracle pipe size, which is 4 KB maximum.
• While packing and unpacking messages, different stored procedures are needed since they can not be overloaded.
• Messages of LONG or LOB data types can not be packed in the pipe. However, a method is provided to do so. You can implement the same.
• This feature can also be implemented in a multi-partitioned environment. Choose PID in all tables as the partitioning key so that all information for a pipe will be stored in the same database partition.

Back to top

Error and return codes

The routines (user-defined functions and stored procedures) provided as part of the pipe implementation package can return any error code defined by the DB2 Universal Database documentation, on any such error occurrence. In addition, the following codes are returned by routines.

• 0 is returned for all successful cases except for the NEXT_ITEM_TYPE stored procedure, which returns a positive 5 digit numeric value on success. Each numeric value represents one data type:

  Numeric valuhe	Data type
  99388	time
  99384	date
  99392	timestamp
  99452	char (max. 254 characters)
  99448	varchar (max. 32631 characters)
  99500	smallint
  99496	int
  99492	bigint
  99484	double

• -1 is returned by user-defined functions on any error.

The above stored procedures returns some user-defined error codes in addition to the following DB2 Universal Database server error codes.

Back to top

Installation procedures

There are two ways this 'pipe' feature can be implemented in DB2 Universal Database:

1. Using the utility ....\pipe\sourceform\pipesetup.bat.
   Execute this as follows:

   Pipesetup.bat <database-name> <SYSADM userid> <SYSADM password>

   
   

   Example:

   pipesetup.bat sample TEST TEST01

   
   
   
   
2. Using the utility ....\pipe\compiledform\putroutines.bat.
   Execute this as follows:

   Putroutines.bat <database-name> <SYSADM userid> <SYSADM password>

   
   

   Example:

   putroutines.bat sample TEST TEST01

   
   

Before executing either one of the above utilities, do the following:

1. Open the utility in a text editor such as notepad.
2. Change the line:
   set DB2HOMEPATH=C:\SQLLIB
   to point to the correct SQLLIB path in your system.
3. Change the line:
   DB2 -o- CALL SQLJ.INSTALL_JAR('file:C:\Documents and Settings\sudipta
\My Documents\db2\KnowledgeBase\article5_inprogress\pipe\sourceform
\delayUDF.jar','delayUDFjar');
   to point to the correct location where the delayUDF.jar file is found.

Back to top

Testing procedure

To test the pipe system:

1. Open two DB2 COMMAND WINDOW sessions (command window with the DB2 environment enabled). Let's name one session Session A and the other Session B. Connect to the sample database using SYSADM userid (if you do not have a sample database, use the db2sampl command to create it). If you have not created the 'pipe' system, create one using the information described above. If you are running this multiple times, then you do not need to compile the stored procedures again and again. Before running the test again, delete the pipe details by executing the command:

   db2 delete from dbms_pipe.pipes where pname='ABC'

   
   .
2. In session A:
   1. Go to the .....\pipe\testscripts directory.
   2. Execute the command:

      db2 -td@ -f send_packet.sql

      
      This command compiles a stored procedure which creates a pipe, packs two messages, and sends it.
   3. Execute the command:

      db2 call dbms_pipe.send_packet()

      
      You should see a 0 (zero) return status.
   4. Select tables dbms_pipe.pipes and dbms_pipe.pipemembers to see the details.
3. In session B:
   1. Go to the .....\pipe\testscripts directory.
   2. Execute the command db2 -td@ -f read_packet.sql. This command compiles a stored procedure which receives a pipe, checks the data types, unpacks two messages, and closes the pipe.
   3. Execute the command db2 call dbms_pipe.read_packet(?, ?). You should see a -999010 return status (because the pipe is closed after retrieving the last message, i.e. DATA2) along with the data for DATA1(time) and DATA2(date).
   4. Select tables dbms_pipe.pipes and dbms_pipe.pipemembers to see the details.

Important notes:

• The entire implementation is using DB2 UDB error messaging methods/codes for any misbehavior either due to SQL error or unexpected problems in the DB2 UDB system. In addition to this, customized error codes and return codes are provided as a part of the functional behavior of pipes.
• Any error (logical functional error or SQL error or DB2 UDB system error) is captured as a return code (numeric) from stored procedures or functions. Logical errors for pipes are captured using -98XX and -99XX error code series, for which DB2 UDB does not have any implementation. Only the stored procedure dbms_pipe.next_item_type returns a five digit numeric code for checking data types and takes action using dbms_pipe.unpack_message_x stored procedure. In this case, DB2 UDB does not provide any error message implementation using 99XXX series, so there is no question of conflict within error messaging.
• Definitions of error codes are embedded in each source code file. You have the flexibility to choose your own error code while implementing.

Back to top

Download

Information about download methods

About the author

Sudipta Mukherjee is an AP lead technical consultant for Data Management at the IBM Solution Partnership Canter (SPC), Asia Pacific SPCs. He is the author of the article Implementing Inter-session Alerts in DB2 Universal Database and an IBM certified solution expert in DB2 UDB v7 and v8 administration and application development. You can reach him at msudipta@in.ibm.com.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• How it works
• Design overview
• Differences, advantages and limitations
• Error and return codes
• Installation procedures
• Testing procedure
• Download
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: Create an electronic form solution with DB2 pureXML and Lotus Forms
• Community: DB2
• Buy: DB2 Enterprise

Dig deeper into Information Management on developerWorks

Try IBM PureSystems. No charge.

Experience today!

---

Get started with the cloud-based trial and pattern development kit.

Special offers