Home > Articles

Using DB2 Functions

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

MQSeries Built-in Functions

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; 0 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).
  • + Share This
  • 🔖 Save To Your Account