DB2 Version 9.7 for Linux, UNIX, and Windows

DBMS_PIPE module

The DBMS_PIPE module provides a set of routines for sending messages through a pipe within or between sessions that are connected to databases within the same DB2® instance.

The schema for this module is SYSIBMADM.

The DBMS_PIPE module includes the following system-defined routines.

Table 1. System-defined routines available in the DBMS_PIPE module
Routine name	Description
CREATE_PIPE function	Explicitly creates a private or public pipe.
NEXT_ITEM_TYPE function	Determines the data type of the next item in a received message.
PACK_MESSAGE function	Puts an item in the session's local message buffer.
PACK_MESSAGE_RAW procedure	Puts an item of type RAW in the session's local message buffer.
PURGE procedure	Removes unreceived messages in the specified pipe.
RECEIVE_MESSAGE function	Gets a message from the specified pipe.
REMOVE_PIPE function	Deletes an explicitly created pipe.
RESET_BUFFER procedure	Resets the local message buffer.
SEND_MESSAGE procedure	Sends a message on the specified pipe.
UNIQUE_SESSION_NAME function	Returns a unique session name.
UNPACK_MESSAGE procedures	Retrieves the next data item from a message and assigns it to a variable.

Usage notes

Pipes are created either implicitly or explicitly during procedure calls. An implicit pipe is created when a procedure call contains a reference to a pipe name that does not exist. For example, if a pipe named "mailbox" is passed to the SEND_MESSAGE procedure and that pipe does not already exist, a new pipe named "mailbox" is created. An explicit pipe is created by calling the CREATE_PIPE function and specifying the name of the pipe.

Pipes can be private or public. A private pipe can only be accessed by the user who created the pipe. Even an administrator cannot access a private pipe that was created by another user. A public pipe can be accessed by any user who has access to the DBMS_PIPE module. To specify the access level for a pipe, use the CREATE_PIPE function and specify a value for the private parameter: "false" specifies that the pipe is public; "true" specifies that the pipe is private. If no value is specified, the default is to create a private pipe. All implicit pipes are private.

To send a message through a pipe, call the PACK_MESSAGE function to put individual data items (lines) in a local message buffer that is unique to the current session. Then, call the SEND_MESSAGE procedure to send the message through the pipe.

To receive a message, call the RECEIVE_MESSAGE function to get a message from the specified pipe. The message is written to the receiving session's local message buffer. Then, call the UNPACK_MESSAGE procedure to retrieve the next data item from the local message buffer and assign it to a specified program variable. If a pipe contains multiple messages, the RECEIVE_MESSAGE function gets the messages in FIFO (first-in-first-out) order.

Each session maintains separate message buffers for messages that are created by the PACK_MESSAGE function and messages that are retrieved by the RECEIVE_MESSAGE function. The separate message buffers allow you to build and receive messages in the same session. However, when consecutive calls are made to the RECEIVE_MESSAGE function, only the message from the last RECEIVE_MESSAGE call is preserved in the local message buffer.

Example

In connection 1, create a pipe that is named pipe1. Put a message in the session's local message buffer, and send the message through pipe1.

BEGIN
  DECLARE status INT;
  SET status = DBMS_PIPE.CREATE_PIPE( 'pipe1' );
  SET status = DBMS_PIPE.PACK_MESSAGE('message1');
  SET status = DBMS_PIPE.SEND_MESSAGE( 'pipe1' );
END@

In connection 2, receive the message, unpack it, and display it to standard output.

SET SERVEROUTPUT ON@

BEGIN
  DECLARE status   INT;
  DECLARE int1     INTEGER;
  DECLARE date1    DATE;
  DECLARE raw1     BLOB(100);
  DECLARE varchar1 VARCHAR(100);
  DECLARE itemType INTEGER;
  
  SET status = DBMS_PIPE.RECEIVE_MESSAGE( 'pipe1' );
  IF( status = 0 ) THEN
    SET itemType = DBMS_PIPE.NEXT_ITEM_TYPE();
    CASE itemType
      WHEN 6 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_INT( int1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'int1: ' || int1 );
      WHEN 9 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_CHAR( varchar1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'varchar1: ' || varchar1 );
      WHEN 12 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_DATE( date1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'date1:' || date1 );
      WHEN 23 THEN
        CALL DBMS_PIPE.UNPACK_MESSAGE_RAW( raw1 );
        CALL DBMS_OUTPUT.PUT_LINE( 'raw1: ' || VARCHAR(raw1) );
      ELSE
        CALL DBMS_OUTPUT.PUT_LINE( 'Unexpected value' );
    END CASE;
  END IF;
  SET status = DBMS_PIPE.REMOVE_PIPE( 'pipe1' );
END@

This example results in the following output:

  varchar1: message1