| DB2 Version 7 adds a number of new built-in scalar and table functions for use with IBM's message queuing software, MQSeries. These functions enable MQSeries messages to be received and sent. The MQSeries scalar functions are | MQREAD | Accepts two parameters; returns a message (as VARCHAR(4000) ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression. | MQREADCLOB | Accepts two parameters; returns a message (as a CLOB ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression. | NOTE When performing either the MQREAD or MQREADCLOB function the operation does not remove the message from the queue specified in the first expression. Additionally, for both functions, if no messages are available a NULL is returned. MQRECEIVE | Same as MQREAD , except the operation will remove the messages from the queue. | MQRECEIVECLOB | Same as MQREADCLOB , except the operation will remove the messages from the queue. | NOTE When performing either the MQRECEIVE or MQRECEIVECLOB function, the operation will remove the message from the queue specified in the first expression. Additionally, for both functions, if no messages are available a NULL is returned. MQSEND | This function is used to send messages to an MQSeries queue. It returns a value of 1 if successful; if unsuccessful . It accepts three (possibly, four) parameters. The data contained in the first expression will be sent to the MQSeries location specified in the second expression, using the quality of service policy defined in the third expression. A user defined by the message correlation identifier may be specified as an optional fourth expression. | Using these scalar functions you can easily read, retrieve, and send information from and to MQSeries message queues. The scalar functions operate one message at a time. At times, though, you might want to operate on multiple MQSeries messages. This requires table functions, and DB2 Version 7 supplies several of these as well. The MQSeries table functions are MQREADALL | Returns all of the messages (as VARCHAR ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression. An optional third parameter can be used to limit the number of rows to return. | MQREADALLCLOB | Returns all of the messages (as CLOB ) from the MQSeries location specified in the first expression, using the quality of service policy defined in the second expression. An optional third parameter can be used to limit the number of rows to return. | MQRECEIVEALL | Same as MQREADALL except the operation will remove the messages from the queue. | MQRECEIVECLOBALL | Same as MQREADALLCLOB except the operation will remove the messages from the queue. | NOTE When performing any of the MQSeries functions that read or receive data, an operation returns a table with the following columns : -
MSG ” Contains the contents of the MQSeries message, either a VARCHAR(4000) or CLOB based on which function was used. -
CORRELID ” Correlation ID used to relate messages ” VARCHAR(24) . -
TOPIC ” The topic that the message was published with, if available ” VARCHAR(40) . -
QNAME ” The queue name where the message was received ” VARCHAR(48) . -
MSGID ” The assigned MQSeries unique identifier for this message ” CHAR(24) . -
MSGFORMAT ” The format (typically MQSTR) of the message, as defined by MQSeries ” VARCHAR(8) . |