Using coordinated transactions with DB2 Type 2 JDBC and WebSphere Message Broker

This article is for Java developers who need to implement XA coordinated transactions in a WebSphere Message Broker V6 Java Compute Node accessing a DB2 database. Using the techniques and JDBC Type 2 driver configuration described in this article, you can implement XA transactions using standard JDBC instead of SQL ODBC, thus enabling you to implement a common Java data access layer that you can deploy in Message Broker and other Java environments, such as WebSphere Application Server.

Martin Boag (martin.boag@uk.ibm.com), Advisory Software Engineer, WebSphere Message Broker Test Team, IBM

Martin Boag is an Advisory Software Engineer on the WebSphere Message Broker Test Team at the IBM Hursley Software Lab in the UK, specializing in systems management and security. He has more than 20 years of experience in developing software products, with the last eight years spent working with Candle and then IBM on products providing application integration, J2EE monitoring, and Enterprise Service Bus facilities. You can contact Martin at martin.boag@uk.ibm.com.



Brian Stewart (bri@uk.ibm.com), Staff Software Engineer, WebSphere Message Broker Test Team, EMC

Brian Stewart is a software engineer with 12 years experience in software development at the IBM Software Lab in Hursley, United Kingdom. Since joining the lab as a graduate in 1998, he has worked with the CICS and WebSphere product families on distributed and z/OS platforms. He has worked on WebSphere Message Broker for the past six years, most recently specializing in pattern authoring, high availability of multi-instance brokers, Message Broker Explorer, and JDBC XA. You can contact Brian at bri@uk.ibm.com.



28 March 2007

Also available in Chinese

Introduction

IBM® WebSphere® Message Broker V6 gives message flow developers full access to the Java language for implementing message processing logic for routing, transformation, and enrichment, and logging and warehousing in-flight data, as an alternative to ESQL. Since this processing implemented in the Broker often includes updating both the in-flight message data from the relevant messaging transport and stored data in a database manager, developers exploiting the Java™ Compute Node will look to exploit JDBC as a standard Java approach to working with the database, and thus enable the possibility of implementing a common Java data access layer that an be deployed in Message Broker and other Java environments.

However, when the processing unit of work needs to be performed transactionally with coordination of messaging and database data by an XA resource manager, the techniques described in Interacting with databases using the JavaCompute node in the Message Broker information center require you to use either the provided database processing nodes or SQL instructions passed through to the Broker's ODBC layer, via the MbSQLStatement method in order to achieve XA resource coordination.

Using the techniques and configuration described in this article, the Broker can implement coordinated transactions in standard JDBC with WebSphere Message Broker V6 when working with a DB2® database using the Type 2 JDBC driver. This approach enables common data access layer code to be shared between applications running in the Broker and other standard Java environments, such as WebSphere Application Server. The approach also shows the use of a global connection factory and caching connections in thread local storage, which enables good performance in a Broker flow configured with multiple processing threads.

Supported platforms

The configuration described in this article has been tested with the following products:

  • WebSphere Message Broker V6.0.0.1
  • WebSphere MQ V6.0.2
  • DB2® Universal Database V8.2

on the following platforms:

  • Microsoft® Windows® (32-bit versions only):
    • Windows XP Professional SP1 or later
    • Windows Server 2003 Standard Edition or Enterprise Edition
  • IBM AIX® V5.3

Developing a message flow to use JDBC XA

Using the techniques and configuration described in this article, the Broker can implement coordinated XA transactions working with a DB2 database using the Type 2 JDBC driver. The JDBC XA support is achieved by using the facilities of WebSphere MQ to act as the global transaction coordinator on distributed platforms coordinating the Message Broker MQ operations and the JDBC operations. This is possible because for message flows configured with coordinated transactions, the message flow processing threads will perform a default enlistment with DB2 for transaction coordination before starting the flow unit of work. As noted in the Restrictions and Limitations section below, the message flow must access only the JDBC XA data source and MQ queues, and not other ODBC data sources or resource managers.

Using Java JDBC T2 XA in a Java Compute Node

In order to have the JDBC operations coordinated by the WebSphere MQ Transaction Processor, the JDBC data source must be created via the DriverManager Java interface of the DB2 JDBC Type 2 Drivers, as described in How DB2 applications connect to a data source using the DriverManager interface in the DB2 information center. The database connection must be obtained at the beginning of the evaluate() method of the Java Compute Node class for each unique Broker thread that executes the message flow. Once obtained, the connection should be cached and reused each time the same thread executes the evaluate() method to perform a unit of work. For more information, see Use of a global connection factory and thread level connection caching below.

As noted under Restrictions and Limitations, these JDBC connections must be used only in message flows that do not contain nodes that own an ODBC connection, such as Compute nodes and Database nodes. Here is a typical database connection technique:

	/**
	 * createConnection - Connect a thread to the JDBC data source
	 *
	 * @param DB2DB_Name
	 * @param DB2User_ID
	 * @param DB2User_PASSWORD
	 * @return Connection
	 * @throws DB2JdbcXaTestException
	 */
	private Connection createConnection(String DB2DB_Name,
	                                    String DB2User_ID,
	                                    String DB2User_PASSWORD)
	throws DB2JdbcXaTestException {
		Connection jdbcXaConn = null;
		try {
			// NB Must only use DB2 App type 2 classes
			Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance();
			// URL form is jdbc:db2:dbname
			String url = "jdbc:db2:" + DB2DB_Name;

			Properties props = new Properties();
			props.put(new String("user"), DB2User_ID);
			props.put(new String("password"), DB2User_PASSWORD);

			jdbcXaConn = DriverManager.getConnection(url, props);

		} catch (SQLException e) {
                  // TODO Handle failure
			System.err.println(e.toString());
			e.printStackTrace();
		} catch (Exception e) {
                  // TODO Handle failure
			System.err.println(e.toString());
			e.printStackTrace();
		}
		return jdbcXaConn;
		}

The Java code in the Java Compute Node class can then use the Connection object returned as any normal JDBC code. There is now no need to call commit or rollback on this connection, because it is performed as a global unit of work coordination by the MQ Transaction Processor when the Broker message flow internally invokes MQBEGIN and MQCOMMIT/MQROLLBACK as part of message flow processing. For message flows with "coordinated transaction" enabled, the MQ Transaction Processor will enlist/commit/rollback any work done on the Connection object within the unit of work. In fact, calling a local commit or rollback on the returned Connection object will cause an exception to be thrown by the DB2 JDBC drivers:

COM.ibm.db2.jdbc.DB2Exception: [...] SQL0926N SQL ROLLBACK invalid for application execution environment. SQLSTATE=2D521

Modifying the required properties on the message flow

The message flow that will access DB2 via a Java Compute Node using JDBC and which is to be coordinated with the MQ operations in the flow must be set in Message Broker to use transactions: on the flow MQInput node properties panel, Advanced tab, ensure that Transaction Mode is set to Yes or Automatic.

Modifying the required property on the BAR file

The Message Broker Archive (BAR) editor lets you edit properties for message flows. When adding the flow containing the Java Compute Node using DB2 JDBC XA, you must set the flow to use coordinated transactions:

  1. Click the Configure tab.
  2. Check the Coordinated Transactions box for each message flow that is to use the XA coordination facility.
  3. Save and deploy the BAR file.

If you delete the contents of the BAR file in the BAR File Editor, you will have to recheck Coordinated Transactions for the flow. The Java Compute Node class can programmatically validate that the flow has this required property set, to ensure that the business logic will be performed under coordinated transactional integrity, using the following code:

MbMessageFlow ourFlow = this.getMessageFlow();
flowName = ourFlow.getName();

// Enforce XA processing - validate flow has been
// deployed with Coordinated Transaction property set
if (!ourFlow.isCoordinatedTransaction()) {
	// TODO Flag deployment properties error via throw MbUserException
}

Use and configuration for AIX

This section details the setup and use of DB2 JDBC "App" Type 2 XA support in WebSphere Message Broker for the supported AIX platforms. For more information on this process, see Configuring global coordination with DB2 using a 64-bit queue manager in the Message Broker information center.

Step 1. Create a 64-bit DB2 instance

The XA Transaction coordination with WebSphere MQ V6 requires that the data source be located on a 64-bit instance of DB2.

Step 2. Set the connection method for DB2 to be TCP

On AIX, DB2 is limited to ten shared memory segments, which is likely to restrict the number of DB2 JDBC connections that you can make, and in turn result in the DB2 error message SQL1224N when the Java Compute nodes connect to DB2 via JDBC. To overcome this error, the system needs to be configured to connect to DB2 via TCP. For information on this setup procedure, see DB2 error message SQL1224N is issued when you connect to DB2 in the Message Broker information center. Follow the steps, and ensure that you use the the configured database names and their aliases correctly in the steps below. Also, ensure that the DB2 TP_MON_NAME property is not set to MQ. That setting is required only on Windows (for more details, see Step 1 under "Usage and Configuration for Windows platform" below).

Step 3. Install the DB2 XA switch load library (32-bit and 64-bit)

In this configuration, the MQ Transaction Processor needs a set of libraries installed that enables it to make the XA coordination calls into DB2. You do this by providing a "switch load library" file in the MQ installation tree. The source code and a build script is provided for these files in MQ, But Message Broker provides pre-built files that can be used if the system has DB2 installed in the default location. To install these pre-built DB2 switch load libraries:

  • 32-bit switch load library; Copy /opt/IBM/mqsi/6.0/sample/xatm/db2swit to /var/mqm/exits/db2swit.
  • 64-bit switch load library: Copy /opt/IBM/mqsi/6.0/sample/xatm/db2swit64 to /var/mqm/exits64/db2swit (This command also renames the file to remove the 64 when installed to exits64.)

If DB2 is not installed in the default location, you must build the switch load files from the source provided in MQ:

  1. Log on as root
  2. If DB2 is not installed in the default location, then export DB2_HOME=/<alternate location>/sqllib.
  3. Change directory to /usr/mqm/samp/xatm.
  4. Issue the following command to build the libraries and install them in the db2swit and db2swit64 directories: make -f xaswit.mak db2swit

Step 4. Configure the ODBC .ini files for databases (32-bit and 64-bit)

Since Message Broker is a 32-bit process and the MQ Queue Manager is a 64-bit process, you must provide both 32-bit and 64-bit data source driver information in ODBC .ini files. Create your ODBC .ini files in a location of your choice, such as your broker service user home directory.  Doing so enables each broker service user ID on the system to use its own data source definitions.

The first step is to create and edit a 32-bit ODBC .ini file using the template at /var/mqsi/odbc/.odbc.ini. The Message Broker database alias must be included in this file. In the example below, database WBRKBDB has an alias of WBRKBDBT for TCP connectivity. The example also shows an ODBC-accessed database that has ODBC data source name (DSN) ODBCDB, with an alias of ODBCDBT for TCP connectivity:

[ODBC Data Sources]

WBRKBDBT=IBM DB2 ODBC Driver
ODBCDBT=IBM DB2 ODBC Driver

[WBRKBDBT]
Driver=/usr/opt/db2_08_01/lib/libdb2.a
Description=WBRKBDB Database alias
Database=WBRKBDBT

[ODBCDBT]
Driver=/usr/opt/db2_08_01/lib/libdb2.a
Description=ODBCDB Database alias
Database=ODBCDBT

[ODBC]
Trace=0
TraceFile=/var/mqsi/odbc/odbctrace.out
TraceDll=/opt/IBM/mqsi/6.0/merant/lib/odbctrac.so
InstallDir=/opt/IBM/mqsi/6.0/merant
UseCursorLib=0
IANAAppCodePage=4

Next, create the 64-bit ODBC .ini file: .odbc64.ini, if it does not already exist, as discussed in odbc64.ini sample file in the Message Broker information center. The Message Broker database alias must be included in this file. In the example below, database WBRKBDB has the alias WBRKBDBT for TCP connectivity. The example also shows an ODBC-accessed database that has ODBC DSN ODBCDB, with the alias ODBCDBT for TCP connectivity.

  1. If necessary, create a file named .odbc64.ini by copying /var/mqsi/odbc/.odbc.ini and renaming it.
  2. Edit .odbc64.ini to change the driver to the 64-bit library (libdb2Wrapper64.so), and replace the [ODBC] content as shown below:
    [ODBC Data Sources]
    
    WBRKBDBT=IBM DB2 ODBC Driver
    ODBCDBT=IBM DB2 ODBC Driver
    
    [WBRKBDBT]
    Driver=libdb2Wrapper64.so
    Description=WBRKBDB Database alias
    Database=WBRKBDBT
    
    [ODBCDBT]
    Driver=libdb2Wrapper64.so
    Description=ODBCDB Database alias
    Database=ODBCDBT
    
    [ODBC]
    Trace=0
    TraceFile=/var/mqsi/odbc/odbctrace.out
    TraceDll=/opt/IBM/mqsi/6.0/DD64/lib/odbctrac.so
    InstallDir=/opt/IBM/mqsi/6.0/DD64
    UseCursorLib=0
    IANAAppCodePage=4
    UNICODE=UTF-8
  3. Finally, ensure that the ODBC .ini files have the correct ownership and permissions. As root, perform the following command on the files:
    chown mqm:mqbrkrs .odbc.ini .odbc64.ini
    chmod 664 .odbc.ini .odbc64.ini

Step 5. Set required environment variables

For any console session that you use, ensure that the following environment variables are set:

  1. $ODBCINI points to the .odbc.ini file created in the previous step: export ODBCINI64=/<path>/.odbc64.ini
  2. $ODBCINI64 points to the .odbc64.ini file created in the previous step: export ODBCINI64=/<path>/.odbc64.ini
  3. MQSI_LIBPATH64 includes the regular 64-bit DB2 database libraries. Assuming the instance name/owner is db2inst1, the export command would look like this: export MQSI_LIBPATH64=/db2data/db2inst1/sqllib/lib64:$LD_LIBRARY_PATH.

Do not change the product mqsiprofile environment setup script directly, because if you apply maintenance, it may be overwritten. However, mqsiprofile can call any additional user-written scripts for you, if you place any files named *.sh in the location
<Workpath>/common/profiles
where <Workpath> is defined via the environment variable MQSI_WORKPATH. For more details, see Command environment: Linux® and UNIX® systems in the Message Broker information center. This step is required only when you use 64-bit execution groups, but it is good practice to complete the configuration in case a 64-bit execution group is added at a later stage.

Step 6. Configure the Message Broker queue manager to use XA for DB2 JDBC

WebSphere MQ needs to be told about the databases that it will coordinate with via its Transaction Processor. You do this by configuring an XA resource configuration record for the database that defines the resource type, for example, by using the switch load files that you have just prepared to enable communications.

These instructions are for DB2 and assume that WebSphere MQ and WebSphere Message Broker are installed in the default locations. The database name is shown as <jdbc database TCP alias name>, and it needs to be substituted along with the user ID and password to match your environment. Each XA resource that is defined must have a unique database name that matches the name used when the relevant code makes its connection to the data source:

  1. Stop the Broker and any applications accessing the Broker's queue manager.
  2. Stop the Broker's queue manager.
  3. Edit the following file: /var/mqm/qmgrs/<queue manager name>/qm.ini
  4. Add the following stanza to the file for DB2 JDBC App Type 2 XA:
    XAResourceManager:
    Name=<jdbc database TCP alias name>
    SwitchFile=db2swit
    XAOpenString=db=<database name>,uid=<userid>,pwd=<password>,toc=t
    ThreadOfControl=THREAD
  5. Restart the Broker's queue manager.
  6. Check for errors in: /var/mqm/qmgrs/<queue manager name>/errors.
  7. Check for errors in: /var/mqm/errors
  8. Check for errors in the system log: /var/adm/syslog/user.log. If the log is not located here, then look at the bottom of /etc/syslog.conf to find where it is written to).
  9. As a true test that XA is working okay on the queue manager, you could use a false password in the XAOpenString for one of the switch files above, and check to see that an error occurs in the queue manager logs when you attempt to restart it. Remember to correct it afterwards.

Step 7. Modify the Broker library paths for 32-bit

Before starting Message Broker, and after running the db2profile, these steps must be completed so that the Broker can find the 32-bit library files for DB2 as necessary. These steps assumes that the instance name/owner is db2inst1:

	export LD_LIBRARY_PATH=/db2data/db2inst1/sqllib/lib32:$LD_LIBRARY_PATH
export LIBPATH=/db2data/db2inst1/sqllib/lib32:$LIBPATH

Use and configuration for Windows

This section details the setup and use of DB2 JDBC "App" Type 2 XA support in WebSphere Message Broker for the supported Windows platforms.

Step 1. Update TP monitor name for DB2 to use WebSphere MQ

The DB2 instance needs to be told that WebSphere MQ will provide the Transaction Processor. Make the following configuration changes to whichever DB2 instance will provide the database that will be accessed from the Message Broker Java Compute nodes via DB2 JDBC XA connections.

In a DB2 command window, enter the following commands to update the TP monitor name to WebSphere MQ. This one-time setup is required only on Windows.

db2 list applications
db2 update dbm cfg using TP_MON_NAME MQ
db2 get dbm cfg | more
db2stop
db2start

Step 2. Configure the queue manager to provide XA resource management for required databases

WebSphere MQ needs to be told about the DB2 databases that it will be coordinating via its Transaction Processor. You do this by configuring an XA resource configuration record for the database that defines the resource type. DB2 provides a necessary switch load file that enables communications. These instructions assume that WebSphere MQ and WebSphere Message Broker are installed in the default location on the C drive. The database name is shown as <dbname< and you need to modify it to match your environment. Each XA resource defined must have a unique database name that matches the name used when the relevant code makes its connection to the data source.

  1. Stop the Broker and any applications accessing the Broker queue manager.
  2. Stop the Broker queue manager.
  3. For DB2 JDBC Type 2 "App" Interface, copy the db2swit.dll from C:\Program Files\IBM\MQSI\6.0\sample\xatm\db2swit.dll to C:\Program Files\IBM\WebSphere MQ\exits.
  4. In MQ Explorer, right-click on the Broker queue manager and select Properties.
  5. Click 'A resource managers => Add and fill out the form as follows:
  6. Restart the queue manager. If the queue manager fails to start via the GUI, use the command-line instead.
  7. Check for errors in: C:\Program Files\IBM\WebSphere MQ\Qmgrs\<queue manager name>\errors.
  8. Check for errors in: C:\Program Files\IBM\WebSphere MQ\errors.
  9. Check for errors in the Windows Event Log.
  10. As a true test that XA is working okay on the queue manager, you could use a false password in the XAOpenString for one of the switch files above, and check to see that an error occurs in the queue manager logs when you attempt to restart it. Remember to correct it afterwards.

Using a global connection factory and thread-level connection caching

As noted above, when a Message Broker flow containing a Java Compute Node exploiting this JDBC access method needs to process multiple transactions concurrently for scalability, it will be deployed with additional instances to provide a thread pool. That is, the additionaInstances property will be set in the BAR File Editor to define the number of threads available in the pool. In this situation, you must ensure that the JDBC connection handle obtained from the JDBC Type 2 driver is obtained once only and then reused on subsequent transactions that are processed by each available thread in the pool. For more information, see Configurable message flow properties in the Message Broker information center. The solution presented here addresses the requirement in two stages:

  1. A singleton connection factory is provided to ensure that each thread in the pool is connected only once. The factory class keeps a static map of the threads that have been connected and can either return the existing connection handle, or create and store the connection handle when called by a thread.
  2. We show how to exploit a Java ThreadLocal to provide a per thread cache of the connection handle from the connection factory, in order to avoid the overhead of locking on the singleton each time the individual threads process a new transaction.

The reason we need this two-level implementation is to facilitate closing the connections. When a Broker message flow is deleted, or the owning execution group is ended, the Broker does not invoke a termination method on every thread in the pool that has been active. Thus if we only had the ThreadLocal cache of the database connection handles, we would not be able to initiate a close of all open handles. The underlying singleton connection factory lets you use its onDelete() method to provide the point at which you can close all open database handles and ensure that the database manager can be cleanly quiesced.

The connection factory singleton could have been extended with a Runnable interface to provide a connection close on inactivity timeout. But we have not yet implemented this in the code.

Firstly we will look at the connection factory singleton class DB2JdbcXaThreadConnectionFactory A single public method is provided to enable the threads obtain the connection handle to be used on that thread:

/**
 * Retrieve an existing connection for this thread to this DB2 instance or
 * create and store a new connection
 *
 * @param DB2DB_Name
 * @param DB2User_ID
 * @param DB2User_PASSWORD
 * @return
 * @throws DB2JdbcXaTestException
 */
public static Connection getConnection(String DB2DB_Name,
		String DB2User_ID, String DB2User_PASSWORD)
		throws DB2JdbcXaTestException {
	DB2JdbcXaThreadConnectionFactory instance = getInstance();
	return instance.getCreateConnection(DB2DB_Name, DB2User_ID,
			DB2User_PASSWORD);
}

The getInstance() method uses the standard singleton pattern to retrieve or create the DB2JdbcXaThreadConnectionFactory class instance. The getCreateConnection() method uses a static hash table to determine if either an existing connection can be returned, or needs to be created.

/**
 * Retrieve an existing connection for this thread to this DB2 instance or
 * create and store a new connection
 *
 * @param DB2DB_Name
 * @param user_ID
 * @param user_PASSWORD
 * @return Connection
 * @throws DB2JdbcXaTestException
 */
private Connection getCreateConnection(String DB2DB_Name, String user_ID,
		String user_PASSWORD) throws DB2JdbcXaTestException {
	Connection connObj = null;
	StringBuffer connectionKeyStrBuff = new StringBuffer();
	connectionKeyStrBuff.append(Thread.currentThread().getName());
	connectionKeyStrBuff.append('#');
	connectionKeyStrBuff.append(DB2DB_Name);
	connectionKeyStrBuff.append('#');
	connectionKeyStrBuff.append(user_ID);
	String connectionKeyString = connectionKeyStrBuff.toString();
	synchronized (connections) {
		connObj = (Connection) connections.get(connectionKeyString);
		if (connObj == null) {
			if (isDebugEnabled) {
				System.out.println("Create Connection: "
						+ connectionKeyString);
			}
			connObj = createConnection(DB2DB_Name, user_ID, user_PASSWORD);
			connections.put(connectionKeyString, connObj);
		}
	}
	return connObj;
}

If a connection does not yet exist for this database and thread, then the createConnection() method is invoked to make the connection.

Next, consider how this connection factory singleton class DB2JdbcXaThreadConnectionFactory is used from a Java Compute Node and cached into a Java ThreadLocal. For a discussion on the use of ThreadLocal, see Exploiting ThreadLocal to enhance scalability. In the class DB2JDBCXaJavaComputeBase, implement the Broker Java Compute node by extending MbJavaComputeNode. In this class, you define the class ThreadLocalConnection to extend ThreadLocal and implement the initialValue() method to obtain a connection handle from our connection factory singleton class DB2JdbcXaThreadConnectionFactory:

// JCN Class object instance thread cache of the global DB2 instance
// connection
// obtained for each thread that executes the JCN in this flow
private class ThreadLocalConnection extends ThreadLocal {
    public Object initialValue() {
        Connection jdbcXaConn = null;
        try {
            if (isDebugEnabled) {
                System.out.println("Obtain thread local connection for Flow "
                                                 + flowName);
        }
            jdbcXaConn = DB2JdbcXaThreadConnectionFactory.getConnection(
               DB2DB_Name, DB2User_ID, DB2User_PASSWORD);
        } catch (DB2JdbcXaTestException e) {
           System.err.println("Failed getConnection(): " + e.toString());
           e.printStackTrace();
           noXaConnCause = e;
        }
           return jdbcXaConn;
    }
}

Thus we can now simply obtain the database connection handle to use during each message processing invocation of the Java Compute Node DB2JDBCXaJavaComputeBase class evaluate() method via the ThreadLocal object get() method, which will invoke the initialValue() method on the first call in each thread from the pool:

/**
 * Obtain the DB2 JDBC Connection either from the local class threadlocal or
 * from the global DB2JdbcXaThreadConnectionFactory
 *
 * @return Connection
 * @throws DB2JdbcXaTestException
 */
public Connection getConnection() throws DB2JdbcXaTestException {
	Connection xaConn = (Connection) conn.get();
	if (xaConn == null) {
		throw noXaConnCause;
	}
	return xaConn;
}

Putting it all together

The download file provided with this article is a project interchange file for the Message Broker toolkit that provides a simple message flow as follows:

The message flow includes a Java Compute Node that uses the techniques presented to enable a simple insert into a database table. To run this sample:

  1. Download the ZIP file and import it to your Message Broker Toolkit as a Project Interchange using File => Import => Project Interchange.
  2. Open the message file MB_JCN_JDBCXA.msgflow in the project MB_JCN_JDBCXA and the Java class JavaComputeJdbcXa, which implements the Java Compute Node. Review the these, noting what the JavaComputeJdbcXa.evaluate() method does:
    • Ensures that the message flow is validated (see doValidation())
    • Simply copies input message to output
    • Obtains the JDBC connection (see getConnection())
    • Inserts a row in the EMPLOYEE table of the DB2 sample database SAMPLE see insertEmployee())
    • Performs a test that proves the connection is really XA
  3. Prepare you database system for coordinated JDBC transactions as discussed above. If you deploy and run the flow without having set up for XA coordination, Message Broker will issue the error:
    "BIP2633" Warning when starting transaction coordinated by WebSphere MQ;
    MQBEGIN failed: MQCC=1; MQRC=2122; node 'MB_JCN_JDBCXA.MQInput.

    where the MQRC is:
    C:\Program Files\IBM\MQSI\6.0.0.3>mqrc 2122
    	2122  0x0000084a  MQRC_PARTICIPANT_NOT_AVAILABLE
    C:\Program Files\IBM\MQSI\6.0.0.3>
  4. Create the DB2 SAMPLE database with the EMPLOYEE table in your database. The file Database\employee.ddl in the project MB_JCN_JDBCXA is also provided.
    db2 => CREATE TABLE "EMPLOYEE"  ("EMPNO" CHAR(6) NOT NULL , "FIRSTNME" VARCHAR(12) NOT
    NULL ,"MIDINIT" CHAR(1) NOT NULL ,  "LASTNAME" VARCHAR(15) NOT NULL ,  "EDLEVEL" SMALLINT
    NOT NULL  )
    DB20000I  The SQL command completed successfully.
    db2 => commit work
    DB20000I  The SQL command completed successfully.
    db2 => connect reset
    DB20000I  The SQL command completed successfully.
  5. Create the Input and Output MQ queues on the Broker Queue Manager for the flow. The file Queues\queues.mqc in the project MB_JCN_JDBCXA is provided:
    def ql(MB_JCN_JDBCXA_IN) bothresh(3) replace
    def ql(MB_JCN_JDBCXA_OUT) replace
  6. Create a BAR file containing the MB_JCN_JDBCXA message flow. Then set the Coordinated Transaction and Additional Instances properties:

    If you deploy the flow without having set Coordinated Transaction, the Java code validation with throw a user exception and cause any message input to be rejected:

    Java exception: 'com.ibm.broker.plugin.MbUserException'; thrown from class name:
    'com.ibm.mbJcnJdbcXa.JavaComputeJdbcXa', method name: 'doValidation', file:
    'JavaComputeJdbcXa.java', line: '128'; trace text: 'Incorrect setup Flow is not
    setup for coordinated Transaction';  resource bundle:
    'com.ibm.mbJcnJdbcXa.JcnJdbcXaMessages'; key: 'DB2JDBCXATEST_FAILURE';

    Then set the following user-defined properties, which provide the Java code with details of the database name and access credentials for your deployment environment:

  7. Deploy the created BAR file to the broker and check for any error messages.
  8. Place a test message containing any valid XML on the flow input queue, MB_JCN_JDBCXA_IN.
  9. Confirm that the message is processed and copied to the output queue MB_JCN_JDBCXA_OUT and that a new row has been created in the SAMPLE database table EMPLOYEE:
    db2 => SELECT * FROM EMPLOYEE
    
    EMPNO  FIRSTNME     MIDINIT LASTNAME        EDLEVEL
    ------ ------------ ------- --------------- -------
    4131  MSGBRK       M       JDBCXA               22
    
    1 record(s) selected.
    
    db2 =>>

    If a new row has not been created, then examine the system event logs for the cause. If one was created, the transaction was performed with XA coordination, because the Java code performs validation of the setup by ensuring that a local rollback is rejected.

Restrictions and limitations

  • The coordinated connection must be obtained using the DriverManager interface with the DB2 "App" JDBC Type 2 Driver -- instantiating an instance of the class COM.ibm.db2.jdbc.app.DB2Driver. All connections made in this way in the flow will be coordinated based on the setting of the Coordinated Transaction setting made on the flow.
  • Only distributed platforms can be used in this configuration -- this facility is not possible on z/OS®. Only Windows and AIX have been tested.
  • Mixed JDBC / ODBC Message flows cannot be run in this configuration.
  • Once an XA Resource has been registered on a Message Broker thread, it can't be re-registered. If you try and redeploy a flow, you may get an MQRC 2195 error. The solution is to simply remove deployed children from the execution group, then redeploy the BAR file.
  • The DB2 type 2 "App" JDBC classes that must be used in this configuration do not implement any connection pooling, so a JDBC connection is held for each Message Broker thread that runs a flow with a Java Compute node that sets up a connection to the JDBC XA source.

Conclusion

The article has showed you how to implement XA coordinated transactions in a WebSphere Message Broker V6 Java Compute Node accessing a DB2 database, enabling you to code in standard JDBC and thus making possible a common Java data access layer that you can deploy in Message Broker and other Java environments, such as WebSphere Application Server. The article provided a sample project that brings the solution together and can be deployed to validate its successful operation.


Download

DescriptionNameSize
Code samples in zip formatDB2_JDBC_XA_support.zip11 KB

Resources

Comments

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 WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere, Information Management
ArticleID=204765
ArticleTitle=Using coordinated transactions with DB2 Type 2 JDBC and WebSphere Message Broker
publish-date=03282007