Retrieving messages with WebSphere MQ

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

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.

The following SQL statement reads the message at the head of the queue that is specified by the default service and policy. The SQL statement returns a VARCHAR(32000) 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.

  VALUES MQREAD()

The following SQL statement causes the contents of a queue to be returned as a result set. 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.

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

The following statement only returns the messages. 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.

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

The following SQL statement receives (removes) the message at the head of the queue. The SELECT statement returns a VARCHAR(32000) string. Because this MQRECEIVE function runs under a transaction, 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.

  VALUES MQRECEIVE()
  COMMIT;

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

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