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