Release-Informationen

42.3 MQSeries Information

42.3.1 Scalar Functions

42.3.1.1 MQPUBLISH

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

Anmerkungen:

1. 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------'
 
 

Anmerkungen:

1. 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:

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 (MQRECEIVEALLCLOB()) 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 (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
  

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 (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:

Tabelle 16. MQSeries Functions that support the CLOB data type

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

¹

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.