MQREADALLCLOB

The MQREADALLCLOB function returns a table that contains the messages and message metadata from a specified MQSeries® location without removing the messages from the queue.

                                                                                 (1)     
>>-MQREADALLCLOB(-+----------------------------------------+-+-----------------+-----)-><
                  '-receive-service--+-------------------+-' '-+---+--num-rows-'         
                                     '-,--service-policy-'     '-,-'

Notes:

The comma is required before num-rows when any of the preceding arguments to the function are specified.

The schema is DB2MQ.

The MQREADALLCLOB function returns a table containing the messages and message meta-data from the MQSeries location that is specified by receive-service, using the quality-of-service policy that is defined in service-policy. Performing this operation does not remove the messages from the queue that is associated with receive-service.

receive-service: An expression that returns a value that is a built-in character string or graphic string data type that is not a LOB. The value of the expression must not be an empty string or a string with trailing blanks. The expression must have an actual length that is no greater than 48 bytes. The value of the expression must refer to a service point that is defined in the DB2MQ.MQSERVICE table. A service point is a logical end-point from which a message is sent or received. A service point definition includes the name of the MQSeries queue manager and the name of the queue. See MQSeries Application Messaging Interface for more details.
If receive-service is not specified or is the null value, DB2.DEFAULT.POLICY is used.
service-policy: An expression that returns a value that is a built-in character string or graphic string data type that is not a LOB. The value of the expression must not be an empty string or a string with trailing blanks. The expression must have an actual length that is no greater than 48 bytes. The value of the expression must refer to a service policy that is defined in the DB2MQ.MQPOLICY table. A service policy specifies a set of quality-of-service options that are to be applied to this messaging operation. These options include message priority and message persistence. See MQSeries Application Messaging Interface for more details.
If service-policy is not specified or is the null value, DB2.DEFAULT.POLICY is used.
num-rows: An expression that returns a value that is a SMALLINT or INTEGER data type whose value is a positive integer or zero. The value of the expression specifies the maximum number of messages to return.
If num-rows is not specified or if the value of the expression is zero, all available messages are returned.

The result of the function is a table with the format shown in the following table. All the columns in the table are nullable.

Table 1. Format of the resulting table for MQREADALLCLOB
Column name	Data type	Contains
MSG	CLOB(1M)	The contents of the MQSeries message
CORRELID	VARCHAR(24)	The correlation ID that is used to relate messages
TOPIC	VARCHAR(40)	The topic that the message was published with, if available
QNAME	VARCHAR(48)	The name of the queue from which the message was received
MSGID	CHAR(24)	The unique, MQSeries-assigned identifier for the message
MSGFORMAT	VARCHAR(8)	The format of the message, as defined by MQSeries

The CCSID of the result is the system CCSID that was in effect at the time that the MQSeries function was installed into DB2®.

Example 1: Read all the messages from the queue specified by the default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY).

   SELECT *
     FROM SYSIBM.SYSDUMMY1 (MQREADALLCLOB()) AS T;

The messages and all the metadata are returned as a table.

Example 2: Read all the messages from the queue specified by the service MYSERVICE, using the default policy (DB2.DEFAULT.POLICY).

   SELECT T.MSG, T.CORRELID
     FROM SYSIBM.SYSDUMMY1 (MQREADALLCLOB('MYSERVICE')) AS T;

Only the MSG and CORRELID columns are returned as a table.

Example 3: Read all the messages from the queue specified by the service MYSERVICE, using the default policy (DB2.DEFAULT.POLICY), with a correlation identifier of '1234'.

   SELECT *
     FROM SYSIBM.SYSDUMMY1 (MQREADALLCLOB('MYSERVICE')) AS T
		 WHERE T.CORRELID = '1234';

All columns are returned. End of change

Example 4: Read the first 10 messages from the queue specified by the default service (DB2.DEFAULT.SERVICE), using the default policy (DB2.DEFAULT.POLICY).

   SELECT *
     FROM SYSIBM.SYSDUMMY1 (MQREADALLCLOB('10')) AS T;

All columns are returned. End of change