Introduction

The XA specification defines how applications use a transaction manager to coordinate a distributed transaction across multiple resource managers. Distributed transactions enable independent resources such as IBM® WebSphere® MQ and DB2® Universal Database to participate in a single unit of work that assures data integrity, even if one resource cannot commit or crashes while the transaction is in progress. A transaction will commit only if all resources can commit successfully -- otherwise it is rolled back.

WebSphere MQ provides support for XA distributed transactions, and WebSphere MQ Classes for Java™ let Java applications use WebSphere MQ to coordinate transactions involving DB2 and Oracle® databases. Database updates may be coordinated with messaging applications, with the transaction being committed only if both operations succeed. Applications can therefore use XA transactions to ensure that the transfer of data between databases and messages is carried out with no risk of data loss.

Configuration of WebSphere MQ for JDBC/XA transactions is straightforward, but requires a number of steps. This article shows you how to configure XA resources, and demonstrates how to use a simple Java application to drive an XA transaction involving WebSphere MQ and a database. Finally, the article takes you through common errors and their solutions.

Supported platforms and databases

WebSphere MQ V6 classes for Java supports XA transactions on the following platforms:

• Microsoft® Windows® (32-bit versions only)
• Windows 2000 Professional, Server, or Advanced Server (with Service Pack 4)
• Windows XP Professional SP1 or later
• Windows Server 2003 Standard Edition or Enterprise Edition
• IBM AIX®
• AIX V5.2 with Maintenance Level 3
• AIX V5.3
• Sun Solaris
• Solaris V8
• Solaris V9
• Solaris V10
• HP-UX 11i V2 (11.23)

The following databases are supported

• IBM DB2 Universal Database V8.2
• Oracle 9i, Release 2 with Patch Set 4 (V9.2.0.5)
• Oracle 10g, Release 1

Configuring WebSphere MQ

WebSphere MQ must be configured in three stages:

1. Prepare the XA switch files.
2. Configure the queue manager to load the switch file.
3. Configure the database.

Prepare the XA switch files

An XA switch file is a shared library that defines the names of XA functions implemented in the resource manager. On Windows, the switch libraries are precompiled and supplied in the install path\Java\lib\jdbc directory (the default install path is C:\Program Files\IBM\WebSphere MQ). The following libraries are supplied:

jdbcdb2.dll

Switch library for DB2 Universal Database V8.2. It has a dependency on db2app.dll, which must be on the system path.

jdbcora9.dll

Switch library for Oracle 9i Release 2. It has a dependency on oraclient9.dll, which must be on the system path.

jdbcora10.dll

Switch library for Oracle 10g Release 1. It has a dependency on oraclient10.dll, which must be on the system path.

In addition, the WebSphere MQ libraries must be on the system path. The WebSphere MQ Using Java manual states that there is also a dependency on jvm.dll, but this library is no longer required by WebSphere MQ V6.

On UNIX®, you must link object files to their corresponding database libraries before use. The required libraries are:

jdbcdb2.o

Switch library for DB2 Universal Database V8.2

jdbcora.o

Switch library for Oracle 9i Release 2 and Oracle 10g Release 1.

Both 32-bit and 64-bit versions of the object files are supplied, in the install path/java/lib/jdbc and install path/java/lib64/jdbc directories respectively. Makefiles to link the object files are also supplied. These use DB2_HOME and ORACLE_HOME to define where the database has been installed, which must be set appropriately. The WebSphere MQ libraries must also be on the system path. Finally, change to the appropriate jdbc directory (/lib/jdbc for 32-bit, /lib64/jdbc for 64-bit) and issue the command make db2 to build the DB2 switch library and make oracle to build the corresponding Oracle library. A 32-bit switch library will be copied to the /var/mqm/exits directory, while a 64-bit switch library will be copied to /var/mqm/exits64.

Configure the queue manager to load the switch file

Once the switch files are ready, you must configure the queue manager to use them. Here are the parameters you need to define:

Name

A unique string that identifies the resource manager. It may be up to 31 characters in length. This field must be provided.

Switch File

Name of switch library to use (for example, jdbcdb2.dll). Do not use the full path.

XAOpenString

A String containing data to be passed into the database's xa_open function. This parameter is product-specific, and generally contains authentication and configuration information.

XACloseString

A String containing data to be passed into the database's xa_close function. This parameter is also product-specific, and is usually empty.

Thread of Control

Set to PROCESS (the default), or to THREAD. The default PROCESS option forces the queue manager to serialize XA function calls so that only one call per process is made at any time. The THREAD option offers improved performance if the database can manage concurrent calls. This setting must match any thread settings in the XAOpenString

When using DB2, the XAOpenString is a comma-separated set of name,value pairs taking the form:

db=database_alias {, optional name=value pairs}

The optional fields include a username, uid=username, a password pwd=password and a thread of control parameter toc={t,p}. In addition, the XA open string may override the transaction processing monitor (TPM) defined in the database manager configuration parameter tp_mon_name by adding tpm=monitor name to the XA open string. For details on the DB2 format, see "xa_open string formats" in the DB2 information center. A suitable XAOpenString would be:

db=testdb,uid=testuser,pwd=password,toc=p,tpm=mq 

When using Oracle, the XAOpenString is a plus-separated String of the form:

Oracle_XA + required fields + optional fields

where required fields are Acc=P/user/password (or =P// if a username and password are not supplied), and SesTm=timeout, which defines the session timeout after which the transaction is rolled back. Important optional parameters include a database name, DB=database name, and the thread of control parameter, threads={true,false}, which specifies if an application is multi-threaded. For details on the Oracle format, see "Defining the xa_open String" in the Oracle9i Application Developer's Guide. A suitable XAOpenString would be:

Oracle_XA+Acc=P/testuser/secret+SesTm=35+DB=TestDB+threads=true

Oracle can support ThreadOfControl=THREAD, and for performance reasons, it is recommended. However, the DB2 switch load file supplied with WebSphere MQ V6 is not threadsafe, and therefore you must specify ThreadOfControl=PROCESS when using DB2.

Microsoft Windows

On Windows, you can do the queue manager configuration with the WebSphere MQ Explorer. First, start the Explorer using the strmqcfg command. Right-click on the queue manager to be configured and select Properties. Then select XA resource managers and click Add. For this example, a DB2 installation with database name of testdb, username testuser, and password secret is assumed. ThreadOfControl is set to PROCESS and the name is set to ExampleDB. After entering these values, the WebSphere MQ Explorer window should look like this:

Click OK => Apply. Restart the queue manager so the changes will take effect.

UNIX platforms

On UNIX platforms, you must manually edit the qm.ini file, which is in /var/mqm/qmgrs/qmname/, where qmname is the name of the queue manager being accessed. Add the following stanza to the file:

XAResourceManager:
  Name=ExampleDB
  SwitchFile= < jdbcdb2 or jdbcora >
  XAOpenString= < xa open string >
  ThreadOfControl= < THREAD | PROCESS >

where the SwitchFile, XAOpenString, and ThreadOfControl parameters have suitable values for the database being used. No XACloseString is required, and it does not need to be added to the stanza. Restart the queue manager so this change will take effect.

You can use multiple stanzas to register multiple datasources on Windows and AIX. For example, you can add:

XAResourceManager:
  Name=DB2DB
  SwitchFile=jdbcdb2
  XAOpenString=db=testdb, uid=testuser, pwd=secret, toc=p
  ThreadOfControl=PROCESS

XAResourceManager:
  Name=OracleDB
  SwitchFile=jdbcora
  XAOpenString=+Acc=P/testuser/secret+SesTm=35+DB=testdb+threads=true
  ThreadOfControl=THREAD

On HP-UX and Solaris, multiple XAResourceManager stanzas are not supported.

Configure the database

Finally, you may need to configure the database.

DB2 configuration

If no username and password is specified in XAOpenString, the user under which the queue manager is running is used by DB2 to authenticate the xa_open call. If this user does not have database privileges, the call will fail. To grant permission for a user to connect, use the following commands

db2 CONNECT TO dbname
db2 GRANT CONNECT ON DATABASE TO USER username

In addition, on Windows, you must define TP_MON_NAME. The command

db2 UPDATE DBM CFG USING TP_MON_NAME MQ

defines mqmax.dll as the library that DB2 uses to call the queue manager. WebSphere MQ documentation suggests using TP_MON_NAME MQMAX. This format is acceptable, but DB2 documentation suggests that TP_MON_NAME MQ is preferred. This value can be overridden using the tpm parameter in the XAOpenString.

Oracle configuration

When using Oracle 9i, you may need to run two scripts, initxa.sql and initjvm.sql, to configure the database to participate in XA transactions. For more information, see the IBM developerWorks article Configuring and using XA distributed transactions in WebSphere Studio.

In addition, the Oracle user name specified in the XA open String must have permission to access the DBA_PENDING_TRANSACTIONS table used internally by the Oracle database to manage transaction recovery. To allow this, as SYSOPER or SYSDBA, run the command:

GRANT SELECT ON DBA_PENDING_TRANSACTIONS TO USERNAME

where USERNAME is the user named in the XA open String. Specifying the username as PUBLIC grants access to all users.

Writing a Java application

The following section describes how to write an application using WebSphere MQ Classes for Java to perform transactional work involving WebSphere MQ and a JDBC/XA database. WebSphere MQ Classes for Java can coordinate XA transactions only when connecting directly to WebSphere MQ using the Java Native Interface (JNI). It is the default for the MQQueueManager class, or you can set it explicitly by setting MQC.TRANSPORT_PROPERTY to MQC.TRANSPORT_MQSERIES_BINDINGS:

// create a properties Hashtable
Hashtable properties = new Hashtable();
//set TRANSPORT_PROPERTY to TRANSPORT_MQSERIES_BINDINGS
properties.put(MQC.TRANSPORT_PROPERTY, MQC.TRANSPORT_MQSERIES_BINDINGS);
// create the MQQueueManager
theQueueManager = new MQQueueManager("ExampleQM", properties);

Applications connecting to a queue manager via a SVRCONN channel (with MQC.TRANSPORT_PROPERTY to MQC.TRANSPORT_MQSERIES_CLIENT), will fail with MQRC_ENVIRONMENT_ERROR when MQQueueManager.begin() is invoked.

JDBC drivers

WebSphere MQ Classes for Java use the Java Database Connectivity (JDBC) API to obtain a connection to the database. JDBC drivers can be of four types; WebSphere MQ supports only drivers of Types 2 and 4. Type 2 drivers provide a Java wrapper around a native API, and require binary code to be installed on each client. Type 4 drivers are pure Java. Typically, you use Type 2 drivers when the application is running on the same machine as the database, and Type 4 when running remotely. For full details of the available JDBC drivers, see the DB2 and Oracle documentation:

• The DB2 JDBC drivers are described in "Java, JDBC, and SQL data types" in the DB2 information center.
• The Oracle JDBC drivers are described in "Oracle JDBC in general" in Oracle JDBC Frequently Asked Questions.

Once suitable drivers have been selected and installed, the application should use an implementation of the javax.sql.XADataSource interface to access the database. For example, with the DB2 Type 4 JDBC drivers, this would be:

com.ibm.db2.jcc.DB2XADataSource

Example

This simple example uses a database to archive messages as they are sent. The database is called TESTDB and has a table named EXAMPLE containing two columns named SENDTIME and MESSAGE. The first is of type LONG, and records the time when the message was sent, while the second is of type CHARACTER(64) and contains the message body. If the message send fails, then the database update should be rolled back; likewise, if the database update fails, the message should not be delivered. We therefore do the update as part of a distributed transaction.

The following example assumes that a user with name testuser and password secret exists, and has permission to write to the database table.

To access the database, applications must import java.sql.*, along with the appropriate XADataSource package (com.ibm.db2.jcc in this example). You can download a full listing of the example application. Central aspects are described in the code fragments below (for clarity, exception handling and System.out.println(...) calls are not shown).

Creating an MQQueueManager

MQQueueManager objects are created as normal. However, you must create them with THREAD_AFFINITY_PROPERTY set to true in the properties Hashtable. By default, WebSphere MQ Classes for Java will use the MQCNO_HANDLE_SHARE_NO_BLOCK option when connecting to the queue manager. This option lets multiple threads share one connection, but is not compatible with a subsequent MQBEGIN call used to start the distributed transaction. The thread affinity property must therefore be set to prevent shared connections from being used.

To create a MQQueueManager object:

// create a properties Hashtable
Hashtable properties = new Hashtable();

// set the THREAD_AFFINITY_PROPERTY to TRUE
properties.put(MQC.THREAD_AFFINITY_PROPERTY, new Boolean(true));

// add other properties if required
properties.put(MQC.TRANSPORT_PROPERTY, MQC.TRANSPORT_MQSERIES_BINDINGS);

// create the MQQueueManager
theQueueManager = new MQQueueManager("ExampleQM", properties);

Creating and registering an XADataSource

The next step is to create and register a java.sql.XADataSource source. To create the datasource using DB2:

// create an XADataSource
DB2XADataSource xads = new DB2XADataSource();

// configure the DataSource (note that these configuration
// parameters will vary, depending on the database and type of
// JDBC drivers being used)
xads.setDatabaseName(DATABASE_NAME);
xads.setUser(USER_NAME);
xads.setPassword(PASSWORD);

and for Oracle:

// create an XADataSource
OracleXADataSource xads = new OracleXADataSource();

// configure the DataSource (note that these configuration
// parameters will vary, depending on the database and type of
// JDBC drivers being used)
xads.setURL(DATABASE_URL);
xads.setUser(USER_NAME);
xads.setPassword(PASSWORD);

The exact form of the URL depends on the location of the database and the type of driver being used. For example:

jdbc:oracle:thin:@localhost:1521:testdb

This datasource is passed to MQQueueManager to create a java.sql.XAConnection:

// get a connection
theDatabaseConnection = theQueueManager.getJDBCConnection(xads);
// disable JDBC autocommit
theDatabaseConnection.setAutoCommit(false);

Doing work in a transaction

The queue manager is now ready to begin the transaction theQueueManager.begin();

Update the database table:

// create a java.sql.PreparedStatement to use
PreparedStatement stmt = theDatabaseConnection.prepareStatement("INSERT INTO EXAMPLE VALUES (?,?)");
// update the time
long time = System.currentTimeMillis();
stmt.setLong(1, time);
// create the message
String msg = "A test message sent on "+ new Date(time);
stmt.setString(2,msg);
// execute the statement
stmt.execute();
// and close it
stmt.close();

Finally, send the message. If it is sent successfully, the transaction can be committed; otherwise it must be rolled back:

try {
    // send a message
    sendMessage(msg); // throws exception if the send fails
    // send ok, now commit the transaction
    theQueueManager.commit();
}
catch (MQException me) {
    // send failed, so roll back the transaction
    theQueueManager.backout();
}
finally {
    // disconnect from the queue manager
    theQueueManager.disconnect();
    // close the connection
    theDatabaseConnection.close();
}

When the full example is run, you should see output like this:

Initializing queue manager and database connection
  Created MQQueueManager for ExampleQM
  Creating a XADataSource for TESTDB
  Getting a Connection to TESTDB from MQQueueManager ExampleQM
  Connected to:
   driver name: IBM DB2 JDBC Universal Driver Architecture
   product    : DB2/NT, version SQL08020
   URL        : jdbc:db2:TESTDB
Initialization complete
Beginning transaction
  Executing database INSERT
  Database updated successfully
  Sending a message to queue:SYSTEM.DEFAULT.LOCAL.QUEUE
  Message sent successfully, committing the transaction
  Transaction committed successfully
  Disconnecting resources
Complete!

You can verify success by checking that the database has been updated as expected:

Alternatively, you can use WebSphere MQ Explorer to verify that a message has been placed on the selected queue. If an error occurs (or if the commit() call in the example is changed to backout()), then neither the database update nor the message put will be visible.

It is not strictly necessary for the WebSphere MQ client to send messages before committing the transaction; the unit of work could simply copy data from Oracle to DB2 with an XA transaction managed by WebSphere MQ. However, this would not be a common scenario.

Troubleshooting

The most common problem is the MQException:

MQJE001: Completion Code 0, Reason 2121

This reason code is MQRC_NO_EXTERNAL_PARTICIPANTS, and is thrown when MQQueueManager.begin() is called. It indicates that no participating resource managers have been registered with the queue manager. This exception can occur if you fail to set THREAD_AFFINITY_PROPERTY to TRUE in the MQQueueManager properties Hashtable. In this case, XA resources cannot be registered, because the connection defaults to MQCNO_HANDLE_SHARE_NO_BLOCK.

If you set THREAD_AFFINITY_PROPERTY correctly, this exception can also be caused by problems with the XAResourceManager stanza, such as an invalid switch library name or incorrect XAOpenString. Information in the WebSphere MQ queue manager logs may help identify the source of the error. For example:

12/12/2005 10:33:39 - Process(5444.5) User(ritchie) Program(amqzlaa0.exe)
AMQ7605: The XA resource manager ExampleDB has returned 
an unexpected return code -5, when called for xa_open.

In this case, SwitchFile was incorrectly specified: the queue manager was able to load the file defined in the stanza, but it failed when xa_open was invoked by MQQueueManager.begin() because it was not the correct switch library for the database being used. If no information is found in the logs, then careful checking of the defined SwitchFile and XAOpenString parameters should identify the cause.

Another exception you may see when MQQueueManager.begin() is called is:

MQJE001: Completion Code 2, Reason 2195

This is an internal mapping from reason code MQRC_PARTICIPANT_NOT_AVAILABLE. For information about the failure, see the queue manager logs. For example:

12/12/2005 10:25:06 - Process(1536.1) User(ritchie) Program(amqzxma0.exe) 

AMQ7604: The XA resource manager 'ExampleDB' was not available when called for xa_open.
The queue manager is continuing without this resource manager.

Like MQRC_NO_EXTERNAL_PARTICIPANTS, this error may result from a problem with the XAOpenString in the XAResourceManager stanza (for example, an incorrect username or password). It may also be caused if the database is shut down between the initial MQQueueManager.getJDBCConnection(...) and the MQQueueManager.begin() call. When multiple XAResourceManager stanzas are defined, this exception may be thrown instead of MQRC_NO_EXTERNAL_PARTICIPANTS if at least one of the defined resources was successfully started.

Problems with the XAResourceManager stanza will also occur when the queue manager is started with the strmqm command:

C:\strmqm ExampleQM
WebSphere MQ queue manager 'ExampleQM' starting.
AMQ7626: XA resource manager initialization failure. Refer to the error log for more information.

The error log shows the cause of the failure:

12/12/2005 09:02:04 - Process(3124.1) User(ritchie) Program(amqzxma0.exe)
AMQ6175: The system could not dynamically load the library
C:\Program Files\IBM\WebSphere MQ\exits\jdbcora10.dll.

12/12/2005 09:02:04 - Process(3124.1) User(ritchie) Program(amqzxma0.exe)
AMQ7622: WebSphere MQ could not load the XA switch load file
for resource manager 'ExampleDB'.

In this case, the required switch file jdbcora10.dll had not been copied to C:\Program Files\IBM\WebSphere MQ\exits\.

If the application tries to connect using client transport (with MQC.TRANSPORT_PROPERTY set to MQC.TRANSPORT_MQSERIES_CLIENT), then the following exception will occur when MQQueueManager.getJDBCConnection(...) is called:

MQJE001: Completion Code 2, Reason 2012

This reason code is MQRC_ENVIRONMENT_ERROR, and it is thrown because distributed transactions are not supported with client transport.

Database errors will usually be seen as javax.sql.SQLExceptions. For example:

executing : INSERT INTO EXAMPLE VALUES (...)
java.sql.SQLException: ORA-00942: table or view does not exist

In this case, the table EXAMPLE had not been created when the application was run.

If you use DB2 and close the JDBC/XA connection before the application disconnects from the queue manager, the following exception may occur:

com.ibm.db2.jcc.b.SqlException: java.sql.Connection.close() requested
while a transaction is in progress on the connection. The transaction
remains active, and the connection cannot be closed.

The solution is to disconnect from the queue manager before attempting to close the JDBC XAConnection.

Conclusion

This article has shown you how to configure WebSphere MQ, DB2, and Oracle to participate in XA distributed transactions managed by a WebSphere MQ Queue Manager. The article used a simple Java application that uses WebSphere MQ Classes for Java and JDBC to show how to access these resources, and then described some common problems and their resolutions.

Back to top

Download

Description	Name	Size	Download method
Code samples in zip format	XAExample.zip	3 KB	FTP|HTTP

Information about download methods

Resources

• Introduction to Java application support
  Describes DB2 driver support for client applications and applets that are written in Java using JDBC.
  
  
• Overview of DB2 and Java Database Connectivity (JDBC)
  Describes the different types of JDBC drivers available for DB2.
  
  
• DB2 xa_open string format
  Describes options available on the DB2 xa_open String.
  
  
• Oracle JDBC frequently asked questions
  Common questions and answers about Oracle JDBC drivers.
  
  
• Defining the Oracle xa_open String
  Describes format of the Oracle xa_open String
  
  
• Configuring and using XA distributed transactions in WebSphere Studio
  Shows the Java exceptions that occur when the Oracle database is not configured to support XA, and how to fix these problems.
  
  
• WebSphere Business Integration products page
  For both business and technical users, a handy overview of all WebSphere Business Integration products
  
  
• WebSphere MQ product page
  Product descriptions, product news, training information, support information, and more.
  
  
• WebSphere MQ documentation library
  WebSphere MQ manuals in PDF format.
  
  
• WebSphere MQ V6 Information Center
  A single Eclipse-based Web interface for all WebSphere MQ V6 documentation.
  
  
• developerWorks WebSphere Business Integration zone
  For developers, access to WebSphere Business Integration how-to articles, downloads, tutorials, education, product info, and more.
  
  
• developerWorks Java zone
  Access to a wealth of free technical information and resources for Java developers.
  
  
• Trial downloads for IBM software products
  No-charge trial downloads for selected IBM® DB2®, Lotus®, Rational®, Tivoli®, and WebSphere® products.
  
  
• Most popular WebSphere trial downloads
  No-charge trial downloads for key WebSphere products.
  
  
• Safari Bookshelf: e-library designed for developers
  Complete search and download access to thousands of technical books for a one-time subscription fee. Free trial for new subscribers.
  
  
• WebSphere forums
  Product-specific forums where you can ask questions and share your opinions with other WebSphere users.
  
  
• developerWorks blogs
  Ongoing, free-form columns by software experts, to which you can add your comments. Check out Grady Booch's blog on software architecture.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Configuring WebSphere MQ
• Writing a Java application
• Conclusion
• Download
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into WebSphere on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers