DB2 Version 9.7 for Linux, UNIX, and Windows

MQRECEIVEALL table function

The MQRECEIVEALL table function returns a table containing the messages and message metadata from the MQSeries® location specified by receive-service, using the quality of service policy service-policy. Performing this operation removes the messages from the queue associated with receive-service.

Syntax


>>-MQRECEIVEALL--(---------------------------------------------->

>--+----------------------------------------------------------+-->
   '-receive-service--+-------------------------------------+-'   
                      '-,--service-policy--+--------------+-'     
                                           '-,--correl-id-'       

>--+-----------------+--)--------------------------------------><
   '-+---+--num-rows-'      
     '-,-'

The schema is DB2MQ for non-transactional message queuing functions, and DB2MQ1C for one-phase commit transactional MQ functions.

Table function parameters

receive-service: A string containing the logical MQSeries destination from which the message is received. If specified, the receive-service must refer to a service point defined in the DB2MQ.MQSERVICE table. A service point is a logical end-point from which a message is sent or received. Service point definitions include the name of the MQSeries Queue Manager and Queue. If receive-service is not specified, then the DB2.DEFAULT.SERVICE will be used. The maximum size of receive-service is 48 bytes.
service-policy: A string containing the MQSeries Service Policy used in the handling of this message. If specified, the service-policy refers to a Policy defined in the DB2MQ.MQPOLICY table. A service policy defines a set of quality of service options that should be applied to this messaging operation. These options include message priority and message persistence. If service-policy is not specified, then the default DB2.DEFAULT.POLICY will be used. The maximum size of service-policy is 48 bytes.
correl-id: An optional string containing a correlation identifier associated with this message. The correl-id is often specified in request and reply scenarios to associate requests with replies. If not specified, no correlation id is specified. The maximum size of correl-id is 24 bytes.
If a correl-id is specified, all the messages with a matching correlation identifier are returned and removed from the queue. If correl-id is not specified, the message at the head of the queue is returned.
num-rows: A positive integer containing the maximum number of messages to be returned by the function.
If num-rows is specified, then a maximum of num-rows messages will be returned. If num-rows is not specified, then all available messages will be returned.

Authorization

One of the following authorities is required to execute the function:

EXECUTE privilege on the function
DATAACCESS authority
DBADM authority
SQLADM authority

Examples

Example 1: This example receives all the messages from the queue specified by the default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY). The messages and all the metadata are returned as a table.

SELECT * FROM table (MQRECEIVEALL()) AS T

Example 2: This example receives all the messages from the head of the queue specified by the service MYSERVICE, using the default policy (DB2.DEFAULT.POLICY). Only the MSG and CORRELID columns are returned.

SELECT T.MSG, T.CORRELID FROM table (MQRECEIVEALL('MYSERVICE')) AS T

Example 3: This example receives all of the message from the head of the queue specified by the service "MYSERVICE", using the policy "MYPOLICY". Only messages with a CORRELID of '1234' are returned. Only the MSG and CORRELID columns are returned.

SELECT T.MSG, T.CORRELID FROM table 
   (MQRECEIVEALL('MYSERVICE','MYPOLICY','1234')) AS T

Example 4: This example receives the first 10 messages from the head of the queue specified by the default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY). All columns are returned.

SELECT * FROM table (MQRECEIVEALL(10)) AS T

Information returned

Table 1. Information returned by the MQRECEIVEALL table function
Column name	Data type	Description
MSG	VARCHAR(32000)	Contains the contents of the MQSeries message.
CORRELID	VARCHAR(24)	Contains a correlation ID that can be used to identify messages. You can select a message from the queue using this identifier. In the case of a request and response scenario, the correlation ID enables you to associate a response with a particular request.
TOPIC	VARCHAR(40)	Contains the topic with which the message was published, if available.
QNAME	VARCHAR(48)	Contains the name of the queue where the message was received.
MSGID	CHAR(24)	Contains the assigned unique MQSeries identifier for this message.
MSGFORMAT	VARCHAR(8)	Contains the format of the message, as defined by MQSeries. Typical strings have an MQSTR format.