MQREADALL table function
The MQREADALL 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 MQREADALL 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 | VARCHAR(4000) | 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 |
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 (MQREADALL()) AS T;
The messages and all the metadata are returned as a table.
- Example 2
- Read all the messages from the beginning 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 (MQREADALL (’MYSERVICE’)) AS T;
- Example 3
- Read all the messages from the queue specified by the default service (Db2.DEFAULT.SERVICE), using the default policy (Db2.DEFAULT.POLICY). Only messages with a CORRELID of '1234' are returned.
All columns are returned.SELECT * FROM TABLE (MQREADALL(10)) AS T WHERE T.CORRELID = ’1234’;
- Example 4
- Retrieve the first 10 messages from the beginning of the queue specified by the default service (Db2.DEFAULT.SERVICE), using the default policy (Db2.DEFAULT.POLICY).
The first 10 messages and all the columns are returned as a table.SELECT * FROM TABLE (MQREADALL(10)) AS T;