Ask the Experts: Dan Wolfson on DB2 and WebSphere MQ Integration

Dan Wolfson answers questions from readers about using WebSphere MQ functions in DB2, such as availability on z/OS, when to use two-phase commit on z/OS, what type of help is available when parsing the message, and message size limitations, among other things.


Dan Wolfson, Senior Technical Staff Member, IBM, Software Group

Dan Wolfson is a Senior Technical Staff Member and manager of the IBM Database Technology Institute for e-Business. With more than 15 years of experience in distributed computing, Dan's interests have ranged broadly across databases, messaging, and transaction systems. Dan is currently the lead architect for information integration.

01 October 2002

© 2002 International Business Machines Corporation. All rights reserved.

Dan Wolfson's previous articles for the DB2® Developer Domain, Using MQSeries® from DB2 Applications, and DB2 MQ XML Functions: Using MQSeries and XML Extender from DB2 Applications, have generated a lot of response from our readers. Here are some of the questions Dan has received:

DB2DD Reader: Is there any message size limitation when messages are sent to MQSeries (WebSphere® MQ) via the DB2 interface? Currently, when messages are sent via the CICS® interface, one is limited to the 32K ceiling of the COMMAREA. This gives us a segmentation problem - and this may solve it.

Dan Wolfson: The first set of functions we delivered supported a 4000-byte maximum message size. However there is an additional, newer set of functions that support 1MB CLOBs. Information about these functions can be found in the release notes for DB2 UDB V7 Fixpack 4. The XML MQSeries functions have also been extended to support CLOB values. For details, see the release notes for DB2 XML Extender Fixpack 4.

DB2DD Reader: Do the MQ Series extensions that enable WebSphere MQ (MQSeries) messaging to integrate with database applications apply to DB2 for OS/390® and z/OSTM?

Dan Wolfson: When I initially wrote that article, the functions (like MQSEND, MQRECEIVE, MQPUBLISH, MQSUBSCRIBE, and so on) were available only on DB2 Version 7.2 of Windows®, AIX®, Solaris®, and HP/UX®. They are of course also available on those platforms in V8. I'm happy to report that several of the functions have been ported to DB2 on z/OS and OS/390 Version 7, including MQREAD, MQREADCLOB, MQRECEIVE, MQRECEIVECLOB, MQSEND, MQREADALL, MQREADALLCOB, MQRECEIVEALL, MQRECEIVEALLCLOB. Notice that the CLOB functions, which provide the capability to have message sizes larger than 4000 bytes, are also available on this platform. These functions are available with PTF UQ71197 and the DB2 for OS/390 manuals are updated, specifically the V7 Application Programming and SQL Guide, SQL Reference, and Installation Guide.

The 390 implementation also supports transactional integration (because of the RRS support on that platform). The first release for 390 will not include all of the XML integration features since we are trying to get the base functions out as quickly as we can. We plan to follow the first shipment with a second delivery covering the functions related to XML Extender, and the other pub/sub functions as quickly as we can.

DB2DD Reader: When would I use one-phase commit as opposed to two-phase commit?

Dan Wolfson: Currently, MQSeries functions support two-phase commit only on DB2 Universal Database for OS/390 and z/OS. For DB2 for OS/390 or x/OS, there are UDF versions that support one-phase commit and two-phase commit. Here are some issues you might want to consider when choosing one or the other:

MQSeries functions that support single-phase commit: When your application uses single-phase commit, DB2 COMMIT or ROLLBACK operations are independent of MQSeries operations. If a DB2 transaction is rolled back, the messages that have been sent to a queue within the current unit of work are not discarded. This scenario is useful in the case of application error. You might want to use MQSeries messaging to notify a system programmer that an application error has occurred. The application issues a ROLLBACK after the error occurs to roll back the DB2 work, but the message is still delivered to the queue that contains the error messages.

MQSeries function that support two-phase commit: If your application uses two-phase commit, RRS controls the commit process. If a DB2 transaction is rolled back, the messages that have been sent to a queue within the current unit of work are discarded. For example, assume that a sales transaction that is recorded in a DB2 database causes an MQSeries message to be sent to a queue. The message in turn causes your inventory system to order replacement merchandise. That message should be discarded if the transaction representing the sale is rolled back. In the two-phase commit environment, if you want to force MQSeries messages to be added to or deleted from the message queue, you need to issue a COMMIT in your DB2 application program.

DB2DD Reader: Why would I use these functions as opposed to writing my own MQ application?

Dan Wolfson: I think the primary reason is that you can easily combine queueing and database operations in the same SQL statement. So you can use a single simple SQL statement to publish the contents of a column or table as a message (select mqsend(lastname) from employee) or insert the contents of a queue into a table (insert into t values (select * from table (mqreceiveall()) t) ). You can embed messaging statements in triggers..etc. In addition, client applications can utilize this functionality without needing MQ on their machines; MQ only needs to be installed at the DB2 server.

Also, obviously, there's the ease of system integration effort and the performance enhancement you can achieve when MQ UDFs are used vs. writing your own MQ applications. The interface to MQ UDFs is simple to understand and configure, and the use of available UDFs whenever possible is a common practice.

DB2DD Reader: What is involved in setting up the AMI configuations?

Dan Wolfson: This generally includes setting up the enviornment variable AMT_DATA_PATH, the AMI host file amthost.xml, as well as the repository file amt.xml. The MQ UDF's enable utility program shipped with DB2 UDB (enable_MQFunctions) will perform a default configuration for you once you have the AMT_DATA_PATH set up to indicate where you want the host file and repository file kept in your AMI configurations. You can also customize your own configuration once you have a better understanding of how AMI works.

For DB2 z/OS or OS/390, you need to copy and customize the DSNAMT and DSNAMTHT files which are located in prefix.SDSNSAMP.

DB2DD Reader: What is available to help with parsing the message?

Dan Wolfson: For DB2 on distributed platforms, we have a set of MQ UDFs and UDB Stored Procedures (SPs) shipped as part of the DB2 XML Extender that handles XML type messages. These SPs provide support to parse XML messages in the queue and store the elements' values to a set of predefined DB2 tables. (The reverse operation that constructs XML messages from tables is also availble.) The mapping is based on the DAD file that is provided as input parameter to the SPs. Please refer to the the XML extender documentation for more detailed information.

There is also support in the Development Center to generate table functions that automatically parse positionally defined or delimited messages.

DB2DD Reader: Can we use the basic DB2 and Websphere MQ Integration to pass an SQL update statement to DB2 from MQ, or do we need MQSI to do this?

Dan Wolfson: You need MQSI to do this. There is some additional functionality planned that will simplify this interaction, but for the moment you would either have to use MQSI or write your own application.


developerWorks: Sign in

Required fields are indicated with an asterisk (*).

Need an IBM ID?
Forgot your IBM ID?

Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name

The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.


All information submitted is secure.

Dig deeper into Information management on developerWorks

Zone=Information Management
ArticleTitle=Ask the Experts: Dan Wolfson on DB2 and WebSphere MQ Integration