|>>-MQPUBLISH--(-------------------------------------------------> | |>--+---------------------------------------------+--msg-data----> | '-publisher-service--,--+-------------------+-' | '-service-policy--,-' | |>--+---------------------------------+--)---------------------->< | '-,--topic--+-------------------+-' | | (1) | | '-,--correl-id------' | |
|Notes:
|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. |
|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.
|>>-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. |
|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.
|>>-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. |
|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.
|>>-MQSEND--(--+----------------------------------------+--------> | '-send-service--,--+-------------------+-' | '-service-policy--,-' | |>--msg-data--+-------------------+--)-------------------------->< | | (1) | | '-,--correl-id------' | |
|Notes:
|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. |
|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.
|>>-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: |
|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
|>>-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: |
|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| |
|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 17. 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.