MQREADALLCLOB table function
The MQREADALLCLOB function returns a table that contains the messages and message metadata from a specified IBM® MQ location without removing the messages from the queue.
The schema is DB2MQ.
The MQREADALLCLOB function returns a table containing the messages and message meta-data from the IBM MQ 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 IBM MQ queue
manager and the name of the queue. See IBM MQ
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 IBM MQ 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.
Column name | Data type | Contains |
---|---|---|
MSG | CLOB(1M) | The contents of the IBM MQ 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 message identifier assigned by IBM MQ |
MSGFORMAT | VARCHAR(8) | The format of the message, as defined by IBM MQ |
The CCSID of the result is the system CCSID that was in effect at the time that the IBM MQ function was installed into Db2.
Examples
- 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 TABLE (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 TABLE (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 TABLE (MQREADALLCLOB('MYSERVICE')) AS T WHERE T.CORRELID = '1234';
All columns are returned.
- 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 TABLE (MQREADALLCLOB('10')) AS T;
All columns are returned.