>>-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 18. 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.