Retrieving messages with IBM MQ

With IBM MQ, programs can read or receive messages. Both reading and receiving operations return the message at the start of the queue. However, the reading operation does not remove the message from the queue, whereas the receiving operation does.

About this task

A message that is retrieved using a receive operation can be retrieved only once, whereas a message that is retrieved using a read operation allows the same message to be retrieved many times.

Examples

The following examples use the DB2MQ2N schema for two-phase commit, with the default service Db2.DEFAULT.SERVICE and the default policy Db2.DEFAULT.POLICY.

Example

The following SQL SELECT statement reads the message at the head of the queue that is specified by the default service and policy:

SELECT DB2MQ2N.MQREAD() 
  FROM SYSIBM.SYSDUMMY1;

The MQREAD function is invoked once because SYSIBM.SYSDUMMY1 has only one row. The SELECT statement returns a VARCHAR(4000) string. If no messages are available to be read, a null value is returned. Because MQREAD does not change the queue, you do not need to use a COMMIT statement.

Example

The following SQL SELECT statement causes the contents of a queue to be materialized as a Db2 table:

SELECT T.* 
  FROM TABLE(DB2MQ2N.MQREADALL()) T;

The result table T of the table function consists of all the messages in the queue, which is defined by the default service, and the metadata about those messages. The first column of the materialized result table is the message itself, and the remaining columns contain the metadata. The SELECT statement returns both the messages and the metadata.To return only the messages, issue the following statement:

SELECT T.MSG 
  FROM TABLE(DB2MQ2N.MQREADALL()) T;

The result table T of the table function consists of all the messages in the queue, which is defined by the default service, and the metadata about those messages. This SELECT statement returns only the messages.

Example

The following SQL SELECT statement receives (removes) the message at the head of the queue:

SELECT DB2MQ2N.MQRECEIVE() 
  FROM SYSIBM.SYSDUMMY1;
COMMIT;

The MQRECEIVE function is invoked once because SYSIBM.SYSDUMMY1 has only one row. The SELECT statement returns a VARCHAR(4000) string. Because this MQRECEIVE function uses two-phase commit, the COMMIT statement ensures that the message is removed from the queue. If no messages are available to be retrieved, a null value is returned, and the queue does not change.

Example

Assume that you have a MESSAGES table with a single VARCHAR(2000) column. The following SQL INSERT statement inserts all of the messages from the default service queue into the MESSAGES table in your Db2 database:

INSERT INTO MESSAGES
  SELECT T.MSG 
    FROM TABLE(DB2MQ2N.MQRECEIVEALL()) T;
COMMIT;

The result table T of the table function consists of all the messages in the default service queue and the metadata about those messages. The SELECT statement returns only the messages. The INSERT statement stores the messages into a table in your database.