MQRECEIVEALLCLOB
The MQRECEIVEALLCLOB function returns a table that contains the messages and message metadata from a specified MQSeries® location and removes the messages from the queue.
The schema is DB2MQ.
The MQRECEIVEALLCLOB function returns a table containing the messages and message metadata 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 removes 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.POLICYis 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.POLICYis used. - correl-id
- An expression that returns a value that is a built-in character
string or graphic string data type that is not a LOB. The expression
must have an actual length that is no greater than 24 bytes. The value
of the expression specifies the correlation identifier that is associated
with this message. A correlation identifier is often specified in
request-and-reply scenarios to associate requests with replies. Only
those messages with a matching correlation identifier are returned.
A fixed length string with trailing blanks is considered a valid value. However, when the correl-id is specified on another request such as MQSEND, the correl-id must be specified the same to be recognized as a match. For example, specifying a value of 'test' for correl-id for this function does not match a correl-id value of 'test ' (with trailing blanks) specified earlier on an MQSEND request.
If correl-id is not specified, is an empty string, or is the null value, a correlation identifier is not used, and the message at the beginning of the queue is returned.
- 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 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.
SELECT *
FROM SYSIBM.SYSDUMMY1 (MQRECEIVEALLCLOB()) AS T;The
messages and all the metadata are returned as a table, and the messages
are removed. SELECT T.MSG, T.CORRELID
FROM SYSIBM.SYSDUMMY1 (MQRECEIVEALLCLOB('MYSERVICE')) AS T;Only
the MSG and CORRELID columns are returned as a table, and the messages
removed from the queue. SELECT *
FROM SYSIBM.SYSDUMMY1 (MQRECEIVEALLCLOB('MYSERVICE','MYPOLICY','1234')) AS T;All
columns are returned, and the messages removed from the queue. SELECT *
FROM SYSIBM.SYSDUMMY1 (MQRECEIVEALLCLOB(10)) AS T;All
columns are returned, and the messages removed from the queue.