Release Notes

| | |

|>>-MQPUBLISH--(------------------------------------------------->
| 
|>--+---------------------------------------------+--msg-data---->
|   '-publisher-service--,--+-------------------+-'
|                           '-service-policy--,-'
| 
|>--+---------------------------------+--)----------------------><
|   '-,--topic--+-------------------+-'
|               |              (1)  |
|               '-,--correl-id------'
| 
|

|Notes:

|The correl-id cannot be specified unless a service and a |policy are previously defined. |

|The schema is DB2MQ.

|The MQPUBLISH function publishes data to MQSeries. This function |requires the installation of either MQSeries Publish/Subscribe or MQSeries |Integrator. Please consult |www.ibm.com/software/MQSeries for further |details.

|The MQPUBLISH function publishes the data contained in msg-data to |the MQSeries publisher specified in publisher-service, and using the |quality of service policy defined by service-policy. An |optional topic for the message can be specified, and an optional user-defined |message correlation identifier may also be specified. The function |returns a value of '1' if successful or a '0' if |unsuccessful. |

|publisher-service: |A string containing the logical MQSeries destination where the message is |to be sent. If specified, the publisher-service must refer to |a publisher Service Point defined in the AMT.XML repository |file. A service point is a logical end-point from which a message is |sent or received. Service point definitions include the name of the |MQSeries Queue Manager and Queue. See the MQSeries Application |Messaging Interface for further details. If publisher-service |is not specified, then the DB2.DEFAULT.PUBLISHER will be |used. The maximum size of publisher-service is 48 |bytes.
|service-policy: |A string containing the MQSeries AMI Service Policy to be used in handling |of this message. If specified, the service-policy must refer |to a Policy defined in the AMT.XML repository file. A Service |Policy defines a set of quality of service options that should be applied to |this messaging operation. These options include message priority and |message persistence. See the MQSeries Application Messaging Interface |manual for further details. If service-policy is not |specified, then the default DB2.DEFAULT.POLICY will be |used. The maximum size of service-policy is 48 bytes.
|msg-data: |A string expression containing the data to be sent via MQSeries. |The maximum size if the string of type VARCHAR is 4000 bytes. If the |string is a CLOB, it can be up to 1MB in size.
|topic: |A string expression containing the topic for the message |publication. If no topic is specified, none will be associated with the |message. The maximum size of topic is 40 bytes. |Multiple topics can be specified in one string (up to 40 characters |long). Each topic must be separated by a colon. For example, |"t1:t2:the third topic" indicates that the message is associated |with all three topics: t1, t2, and "the third topic".
|correl-id: |An optional string expression containing a correlation identifier to be |associated with this message. The correl-id is often specified |in request and reply scenarios to associate requests with replies. If |not specified, no correlation id will be added to the message. The |maximum size of correl-id is 24 bytes. |

|Examples

|Example 1: This example publishes the string "Testing 123" to the |default publisher service (DB2.DEFAULT.PUBLISHER) using the |default policy (DB2.DEFAULT.POLICY). No correlation |identifier or topic is specified for the message.

|VALUES MQPUBLISH('Testing 123')

|Example 2: This example publishes the string "Testing 345" to the |publisher service "MYPUBLISHER" under the topic "TESTS". The default |policy is used and no correlation identifier is specified.

|VALUES MQPUBLISH('MYPUBLISHER','Testing 345', 'TESTS')

|Example 3: This example publishes the string "Testing 678" to the |publisher service "MYPUBLISHER" using the policy "MYPOLICY" with a correlation |identifier of "TEST1". The message is published with topic |"TESTS".

|VALUES MQPUBLISH('MYPUBLISHER','MYPOLICY','Testing 678','TESTS','TEST1')

|Example 4: This example publishes the string "Testing 901" to the |publisher service "MYPUBLISHER" under the topic "TESTS" using the default |policy (DB2.DEFAULT.POLICY) and no correlation |identifier.

|VALUES MQPUBLISH('Testing 901','TESTS')

|All examples return the value '1' if successful.

|42.3.1.2 MQREADCLOB

| | |

|>>-MQREADCLOB--(--+----------------------------------------+---->
|                  '-receive-service--+-------------------+-'
|                                     '-,--service-policy-'
| 
|>--)-----------------------------------------------------------><
| 
|

|The schema is DB2MQ.

|The MQREADCLOB function returns a message from the MQSeries location |specified by receive-service, using the quality of service policy |defined in service-policy. Executing this operation does not |remove the message from the queue associated with receive-service, |but instead returns the message at the head of the queue. The return |value is a CLOB of 1MB maximum length, containing the message. If no |messages are available to be returned, a NULL is returned. |

|receive-service: |A string containing the logical MQSeries destination from where the |message is to be received. If specified, the receive-service |must refer to a Service Point defined in the AMT.XML repository |file. A service point is a logical end-point from where a message is |sent or received. Service points definitions include the name of the |MQSeries Queue Manager and Queue. See the MQSeries Application |Messaging Interface for further details. If receive-service is |not specified, then the DB2.DEFAULT.SERVICE will be used. |The maximum size of receive-service is 48 bytes.
|service-policy: |A string containing the MQSeries AMI Service Policy used in handling this |message. If specified, the service-policy must refer to a |Policy defined in the AMT.XML repository file. A Service Policy |defines a set of quality of service options that should be applied to this |messaging operation. These options include message priority and message |persistence. See the MQSeries Application Messaging Interface manual |for further details. If service-policy is not specified, then |the default DB2.DEFAULT.POLICY will be used. The maximum |size of service-policy is 48 bytes. |

|Examples:

|Example 1: This example reads the message at the head of the queue |specified by the default service (DB2.DEFAULT.SERVICE), using |the default policy (DB2.DEFAULT.POLICY).

|VALUES MQREADCLOB()

|Example 2: This example reads the message at the head of the queue |specified by the service "MYSERVICE" using the default policy |(DB2.DEFAULT.POLICY).

|VALUES MQREADCLOB('MYSERVICE')

|Example 3: This example reads the message at the head of the queue |specified by the service "MYSERVICE", and using the policy "MYPOLICY".

|VALUES MQREADCLOB('MYSERVICE','MYPOLICY')

|All of these examples return the contents of the message as a CLOB with a |maximum size of 1MB, if successful. If no messages are available, then |a NULL is returned.

|42.3.1.3 MQRECEIVECLOB

| | |

|>>-MQRECEIVECLOB------------------------------------------------>
| 
|>--(--+----------------------------------------------------------+--)-><
|      '-receive-service--+-------------------------------------+-'
|                         '-,--service-policy--+--------------+-'
|                                              '-,--correl-id-'
| 
|

|The schema is DB2MQ.

|The MQRECEIVECLOB function returns a message from the MQSeries location |specified by receive-service, using the quality of service policy |service-policy. Performing this operation removes the message |from the queue associated with receive-service. If the |correl-id is specified, then the first message with a matching |correlation identifier will be returned. If correl-id is not |specified, then the message at the head of the queue will be returned. |The return value is a CLOB with a maximum length of 1MB containing the |message. If no messages are available to be returned, a NULL is |returned. |

|receive-service: |A string containing the logical MQSeries destination from which the |message is received. If specified, the receive-service must |refer to a Service Point defined in the AMT.XML repository file. |A service point is a logical end-point from which a message is sent or |received. Service points definitions include the name of the MQSeries |Queue Manager and Queue. See the MQSeries Application Messaging |Interface for further details. If receive-service is not |specified, then the DB2.DEFAULT.SERVICE is used. The |maximum size of receive-service is 48 bytes.
|service-policy: |A string containing the MQSeries AMI Service Policy to be used in the |handling of this message. If specified, the service-policy |must refer to a Policy defined in the AMT.XML repository file |¹ |. If service-policy is not specified, then |the default DB2.DEFAULT.POLICY is used. The maximum size |of service-policy is 48 bytes.
|correl-id: |A string containing an optional correlation identifier to be associated |with this message. The correl-id is often specified in request |and reply scenarios to associate requests with replies. If not |specified, no correlation id will be used. The maximum size of |correl-id is 24 bytes. |

|Examples:

|Example 1: This example receives the message at the head of the queue |specified by the default service (DB2.DEFAULT.SERVICE), using |the default policy (DB2.DEFAULT.POLICY).

|VALUES MQRECEIVECLOB()

|Example 2: This example receives the message at the head of the queue |specified by the service "MYSERVICE" using the default policy |(DB2.DEFAULT.POLICY).

|VALUES MQRECEIVECLOB('MYSERVICE')

|Example 3: This example receives the message at the head of the queue |specified by the service "MYSERVICE" using the policy "MYPOLICY".

|VALUES MQRECEIVECLOB('MYSERVICE','MYPOLICY')

|Example 4: This example receives the first message with a correlation |id that matches '1234' from the head of the queue specified by the |service "MYSERVICE" using the policy "MYPOLICY".

|VALUES MQRECEIVECLOB('MYSERVICE',MYPOLICY','1234')

|All these examples return the contents of the message as a CLOB with a |maximum size of 1MB, if successful. If no messages are available, a |NULL will be returned.

|42.3.1.4 MQSEND

| | |

|>>-MQSEND--(--+----------------------------------------+-------->
|              '-send-service--,--+-------------------+-'
|                                 '-service-policy--,-'
| 
|>--msg-data--+-------------------+--)--------------------------><
|             |              (1)  |
|             '-,--correl-id------'
| 
|

|Notes:

|The correl-id cannot be specified unless a service and a |policy are previously defined. |

|The schema is DB2MQ.

|The MQSEND function sends the data contained in msg-data to the |MQSeries location specified by send-service, using the quality of |service policy defined by service-policy. An optional user |defined message correlation identifier may be specified by |correl-id. The function returns a value of '1' if |successful or a '0' if unsuccessful. |

|msg-data: |A string expression containing the data to be sent via MQSeries. |The maximum size is 4000 bytes if the data is of type VARCHAR, and 1MB if the |data is of type CLOB.
|send-service: |A string containing the logical MQSeries destination where the message is |to be sent. If specified, the send-service refers to a service |point defined in the AMT.XML repository file. A service point is |a logical end-point from which a message may be sent or received. |Service point definitions include the name of the MQSeries Queue Manager and |Queue. See the MQSeries Application Messaging Interface manual for |further details. If send-service is not specified, then the |value of DB2.DEFAULT.SERVICE is used. The maximum size of |send-service is 48 bytes.
|service-policy: |A string containing the MQSeries AMI Service Policy used in handling of |this message. If specified, the service-policy must refer to a |service policy defined in the AMT XML repository file. A Service Policy |defines a set of quality of service options that should be applied to this |messaging operation. These options include message priority and message |persistence. See the MQSeries Application Messaging Interface manual |for further details. If service-policy is not specified, then |a default value of DB2.DEFAULT.POLICY will be used. The |maximum size of service-policy is 48 bytes.
|correl-id: |An optional string containing a correlation identifier associated with |this message. The correl-id is often specified in request and |reply scenarios to associate requests with replies. If not specified, |no correlation id will be sent. The maximum size of correl-id |is 24 bytes. |

|Examples:

|Example 1: This example sends the string "Testing 123" to the default |service (DB2.DEFAULT.SERVICE), using the default policy |(DB2.DEFAULT.POLICY), with no correlation identifier.

|VALUES MQSEND('Testing 123')

|Example 2: This example sends the string "Testing 345" to the service |"MYSERVICE", using the policy "MYPOLICY", with no correlation |identifier.

|VALUES MQSEND('MYSERVICE','MYPOLICY','Testing 345')

|Example 3: This example sends the string "Testing 678" to the service |"MYSERVICE", using the policy "MYPOLICY", with correlation identifier |"TEST3".

|VALUES MQSEND('MYSERVICE','MYPOLICY','Testing 678','TEST3')

|Example 4: This example sends the string "Testing 901" to the service |"MYSERVICE", using the default policy (DB2.DEFAULT.POLICY), and |no correlation identifier.

|VALUES MQSEND('MYSERVICE','Testing 901')

|All examples return a scalar value of '1' if |successful.

42.3.2 Table Functions

|42.3.2.1 MQREADALLCLOB

| | |

|>>-MQREADALLCLOB--(--------------------------------------------->
| 
|>--+----------------------------------------+--+----------+----->
|   '-receive-service--+-------------------+-'  '-num-rows-'
|                      '-,--service-policy-'
| 
|>--)-----------------------------------------------------------><
| 
|

|The schema is DB2MQ.

|The MQREADALLCLOB function returns a table containing the messages and |message metadata from the MQSeries location specified by |receive-service, using the quality of service policy |service-policy. Performing this operation does not remove the |messages from the queue associated with receive-service.

|If num-rows is specified, then a maximum of num-rows |messages will be returned. If num-rows is not specified, then |all available messages will be returned. The table returned contains |the following columns: |

|MSG - a CLOB column containing the contents of the MQSeries |message.
|CORRELID - a VARCHAR(24) column holding a correlation ID used to relate |messages.
|TOPIC - a VARCHAR(40) column holding the topic that the message was |published with, if available.
|QNAME - a VARCHAR(48) column holding the queue name where the message was |received.
|MSGID - a CHAR(24) column holding the assigned MQSeries unique identifier |for this message.
|MSGFORMAT - a VARCHAR(8) column holding the format of the message, as |defined by MQSeries. Typical strings have an MQSTR format. |

|receive-service: |A string containing the logical MQSeries destination from which the |message is read. If specified, the receive-service must refer |to a service point defined in the AMT.XML repository file. A |service point is a logical end-point from which a message is sent or |received. Service point definitions include the name of the MQSeries |Queue Manager and Queue. See the MQSeries Application Messaging |Interface for further details. If receive-service is not |specified, then the DB2.DEFAULT.SERVICE will be used. The |maximum size of receive-service is 48 bytes.
|service-policy: |A string containing the MQSeries AMI Service Policy used in the handling |of this message. If specified, the service-policy refers to a |Policy defined in the AMT XML repository file. A service policy defines |a set of quality of service options that should be applied to this messaging |operation. These options include message priority and message |persistence. See the MQSeries Application Messaging Interface manual |for further details. If service-policy is not specified, then |the default DB2.DEFAULT.POLICY will be used. The maximum |size of service-policy is 48 bytes.
|num-rows: |A positive integer containing the maximum number of messages to be |returned by the function. |

|Examples:

|Example 1: This example receives all the messages from the queue |specified by the default service (DB2.DEFAULT.SERVICE), using |the default policy (DB2.DEFAULT.POLICY). The messages and |all the metadata are returned as a table.

|SELECT *
|   FROM table (MQREADALLCLOB()) T

|Example 2: This example receives all the messages from the head 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 (MQREADALLCLOB('MYSERVICE')) T

|Example 3: This example reads the head of 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 (MQREADALLCLOB()) T
|   WHERE T.CORRELID = '1234'

|Example 4: This example receives the first 10 messages from the head |of the queue specified by the default service |(DB2.DEFAULT.SERVICE), using the default policy |(DB2.DEFAULT.POLICY). All columns are returned.

|SELECT *
|   FROM table (MQREADALLCLOB(10)) T

|42.3.2.2 MQRECEIVEALLCLOB

| | |

|>>-MQRECEIVEALLCLOB--(------------------------------------------>
| 
|>--+----------------------------------------------------------+-->
|   '-receive-service--+-------------------------------------+-'
|                      '-,--service-policy--+--------------+-'
|                                           '-,--correl-id-'
| 
|>--+-----------------+--)--------------------------------------><
|   '-+---+--num-rows-'
|     '-,-'
| 
|

|The schema is DB2MQ.

|The MQRECEIVEALLCLOB function returns a table containing the messages and |message metadata from the MQSeries location specified by |receive-service, using the quality of service policy |service-policy. Performing this operation removes the messages |from the queue associated with receive-service.

|If a correl-id is specified, then only those messages with a |matching correlation identifier will be returned. If correl-id |is not specified, then the message at the head of the queue will be |returned.

|If num-rows is specified, then a maximum of num-rows |messages will be returned. If num-rows is not specified, then |all available messages are returned. The table returned contains the |following columns: |

|MSG - a CLOB column containing the contents of the MQSeries |message.
|CORRELID - a VARCHAR(24) column holding a correlation ID used to relate |messages.
|TOPIC - a VARCHAR(40) column holding the topic that the message was |published with, if available.
|QNAME - a VARCHAR(48) column holding the queue name where the message was |received.
|MSGID - a CHAR(24) column holding the assigned MQSeries unique identifier |for this message.
|MSGFORMAT - a VARCHAR(8) column holding the format of the message, as |defined by MQSeries. Typical strings have an MQSTR format. |

|receive-service: |A string containing the logical MQSeries destination from which the |message is received. If specified, the receive-service must |refer to a service point defined in the AMT.XML repository file. |A service point is a logical end-point from which a message is sent or |received. Service point definitions include the name of the MQSeries |Queue Manager and Queue. See the MQSeries Application Messaging |Interface manual for further details. If receive-service is |not specified, then the DB2.DEFAULT.SERVICE will be used. |The maximum size of receive-service is 48 bytes.
|service-policy: |A string containing the MQSeries AMI Service Policy used in the handling |of this message. If specified, the service-policy refers to a |Policy defined in the AMT XML repository file. A service policy defines |a set of quality of service options that should be applied to this messaging |operation. These options include message priority and message |persistence. See the MQSeries Application Messaging Interface manual |for further details. If service-policy is not specified, then |the default DB2.DEFAULT.POLICY will be used. The maximum |size of service-policy is 48 bytes.
|correl-id: |An optional string containing a correlation identifier associated with |this message. The correl-id is often specified in request and |reply scenarios to associate requests with replies. If not specified, |no correlation id is specified. The maximum size of correl-id is 24 |bytes.
|num-rows: |A positive integer containing the maximum number of messages to be |returned by the function. |

|Examples:

|SELECT *
|   FROM table (MQRECEIVEALLCLOB()) T

|SELECT T.MSG, T.CORRELID
|   FROM table (MQRECEIVEALLCLOB('MYSERVICE')) T

|Example 3: This example receives all of the message from the head of |the queue specified by the service "MYSERVICE", using the policy |"MYPOLICY". Only messages with a CORRELID of '1234' are |returned. Only the MSG and CORRELID columns are returned.

|SELECT T.MSG, T.CORRELID
|   FROM table (MQRECEIVEALLCLOB('MYSERVICE','MYPOLICY','1234')) T
|

|SELECT *
|   FROM table (MQRECEIVEALLCLOB(10)) T

| |

|42.3.3 CLOB data now supported in MQSeries functions

|The MQSeries functions (those defined in the DB2MQ schema) now include |functionality that allow them to be used with CLOB data in addition to VARCHAR |data. In some cases, a new function now exists to handle the CLOB data |type, in others, the already existing function now handles both CLOB and |VARCHAR data. In either case, the syntax of the CLOB function is |identical to that of its VARCHAR equivalent. The functions that support |the use of CLOB data, and their equivalent VARCHAR functions, are listed in |the following table:

|Table 16. MQSeries Functions that support the CLOB data type

Function to use for VARCHAR data	Function to use for CLOB data
MQPUBLISH	MQPUBLISH
MQREAD	MQREADCLOB
MQRECEIVE	MQRECEIVECLOB
MQSEND	MQSEND
MQREADALL	MQREADALLCLOB
MQRECEIVEALL	MQRECEIVEALLCLOB

|For information on enabling the MQSeries functions (those defined in |the DB2MQ schema), see MQSeries.

Footnotes:

¹: A Service Policy defines a set of quality of service options that should be applied to this messaging operation. These options include message priority and message persistence. See the MQSeries Application Messaging Interface manual for further details.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

Release Notes

42.3 MQSeries Information

42.3.1 Scalar Functions

|42.3.1.1 MQPUBLISH

|42.3.1.2 MQREADCLOB

|42.3.1.3 MQRECEIVECLOB

|42.3.1.4 MQSEND

42.3.2 Table Functions

|42.3.2.1 MQREADALLCLOB

|42.3.2.2 MQRECEIVEALLCLOB

|42.3.3 CLOB data now supported in MQSeries functions