Using Easysoft Oracle ODBC driver XA support with IBM WebSphere MQ

Contents

Introduction

Why distributed transactions are needed

A transaction is a series of actions performed as a single operation in which either all of the actions are performed or none of them are. A transaction ends with a commit action that makes the changes permanent. If any of the changes cannot be committed, the transaction will roll back, reversing all the changes.

A distributed transaction is a transaction that may span multiple resources. For example, one or more databases or a database and a message queue. For the transaction to commit successfully, all of the individual resources must commit successfully; if any of them are unsuccessful, the transaction must roll back in all of the resources. For example, a distributed transaction might consist of a money transfer between two bank accounts, hosted by different banks, and so also on different databases. You would not want either transaction committed without a guarantee that both will complete successfully. Otherwise, data may be duplicated (if the insert completes and the delete fails) or lost (if the delete completes and the insert fails).

Whenever an application needs to access or update the data in multiple transactional resources therefore, it should use a distributed transaction. It is possible to use a separate transaction on each of the resources, but this approach is error-prone. If the transaction in one resource commits successfully, but another fails and must roll back, the first transaction can no longer be rolled back, so the state of the application becomes inconsistent. If one resource commits successfully but the system crashes before the other resource can commit successfully, the application again is inconsistent.

XA

The X/Open Distributed Transaction Processing (DTP) model defines an architecture for distributed transaction processing. In the DTP architecture, a coordinating transaction manager tells each resource how to process a transaction, based on its knowledge of all the resources participating in the transaction. Resources that normally manage their own transaction commit and recovery delegate this task to the transaction manager.

The architecture's XA specification provides an open standard that ensures interoperability across conformant transactional middleware and database products. These different resources are therefore able to participate together in a distributed transaction.

The DTP model includes three interrelated components:

The XA standard defines the two-phase commit protocol and the interface used for communication between a Transaction Manager and a Resource Manager. The two-phase commit protocol provides an all-or-nothing guarantee that all participants involved in the transaction either commit or roll back together. The entire transaction commits or the entire transaction rolls back, therefore.

The two-phase commit consists of a prepare phase and a commit phase. During the prepare phase, all participants in the transaction must agree to complete the changes required by the transaction. If any of the participants report a problem, the prepare phase will fail and the transaction will roll back. If the prepare phase is successful, phase two, the commit phase starts. During the commit phase, the Transaction Manager instructs all participants to commit the transaction.

The Easysoft Oracle ODBC driver

The Easysoft Oracle ODBC driver lets applications that support ODBC access Oracle databases. In the example described in this article, it enables a WebSphere MQ application to access an Oracle database. Because the Easysoft Oracle ODBC driver can be configured to enlist in an XA transaction, it enables the WebSphere MQ application to access an Oracle database as an XA resource in the context of a distributed transaction.

The ODBC auto-commit mode controls whether transactions in ODBC are automatically committed. Because this mode can be turned off, the Easysoft Oracle ODBC driver can participate in an XA transaction without affecting transaction processing. The decision to commit or rollback a transaction is left to the Transaction Manager.

Oracle XA switch file

To provide distributed transactions, WebSphere MQ needs a interface to the Oracle database server, this is done via the standard X/Open XA interface provided by the xa_switch_t structure in the Oracle client library. To provide WebSphere MQ access to this structure, a small library is referenced in the Resource Manager section in the qm.ini file for the target queue.

Make sure that the Oracle XA Switch file is built and linked against the Oracle client library (libclntsh) that the Easysoft Oracle ODBC driver is using. The source and makefile for this is provided in the WebSphere MQ samples directory (by default, /opt/mqm/samp/xatm), and can be built using the supplied makefile after setting the ORACLE_HOME variable to match the Oracle installation. For example:

export ORACLE_HOME=/opt/oracle/product/9.2.0/lib
make -f xaswit.mak oraswit

This builds the file /var/mqm/exits/oraswit, which is later used in the WebSphere MQ Resource Manager setup.

WebSphere MQ configuration

The setup for WebSphere MQ to use the switch file built in the last section can be either done using the WebSphere MQ Explorer GUI application or by directly editing the qm.ini file. Each Queue Manager has its own qm.ini file, and for the sample queue created in the WebSphere MQ setup documents venus.queue.manager, the location of the file is:

/var/mqm/qmgrs/venus!queue!manager/qm.ini

GUI setup

In WebSphere MQ Explorer, right-click the venus.queue.manager, then select the Properties option. Once that displays, select the XA resource managers option from the list. Then choose Add to create a new resource manager entry. Provide a name for the resource, then add the path to the switch file created in the previous step (/var/mqm/exits/oraswit). The XAOpenString should be created using the guides provided in the Oracle documentation, and is discussed later. No XACloseString need be specified, and the ThreadOfControl should be set as required by the application.

File setup

Add a new Resource Manager entry to the qm.ini file. For example:

XAResourceManager:
    Name=Oracle1
    SwitchFile=/var/mqm/exits/oraswit
    XAOpenString=Oracle_XA+sqlnet=ninetwo.oracle+SesTm=35+Acc=P/system/manager+Threads=true+LogDir=/tmp/xalog+Dbgfl=15+DB=test1
    ThreadOfControl=THREAD

The name of the Resource Manager (Oracle1 in this example) must be unique, and the XAOpenString must match your system configuration as discussed later.

Restarting the queue

After the Resource Manager entry has been added, stop,x and then restart the queue manager. If there are any problems starting the resource manager and a LogDir is specified in the XAOpenString, the log file created will show the cause of the problem. We suggest that when first testing the configuration, use LogDir to check that all is working as expected. After testing has finished, logging can then be turned off if required.

ODBC driver setup

The Easysoft Oracle ODBC driver needs to be provided with information to allow it to take part in the distributed transaction. Add this information to your ODBC data source. More information about the odbc.ini file and the entries can be found in the documentation provided with the Easysoft Oracle ODBC driver. A sample entry to match the XA Connection String shown above is given in this example:

[ORACLE-XA]
Driver                     = ORACLE
Database                   = ninetwo.oracle
User                       = system
Password                   = manager
METADATA_ID                = 0
ENABLE_USER_CATALOG        = 1
ENABLE_SYNONYMS            = 1
XA_ENLIST                  = 1
XA_CONNECTION_STRING       = test1

The XA_ENLIST value is either set to 1 or 0. 1 turns on the XA features for the connection. When XA_ENLIST is set to 1, the connection can only be used with a Transaction Manager (in this case, WebSphere MQ). A normal ODBC application will fail, as shown in this example:

$ isql -v ORACLE-XA
[S1000][unixODBC][Easysoft][Oracle]Error obtaining XA environment
[ISQL]ERROR: Could not SQLConnect

This is normal, so if a connection is also required for non-XA use, create a matching ODBC data source with XA_ENLIST turned off.

The optional XA_CONNECTION_STRING entry lets the Oracle ODBC driver select which Resource Manager instance the connection should connect to. The value of this should match the database name provided in the DB entry in the XAConnectionString.

Once this entry is created, the connection should be ready for use with WebSphere MQ.

Using the XA enabled driver within MQ

Once configured, the Easysoft Oracle ODBC driver can be used from any WebSphere MQ application. There are three points to remember when writing applications.

The previous points are illustrated in the sample program, and the following snippets of code illustrate the general flow of control. For more details of the WebSphere MQ interface, consult the WebSphere MQ documentation.

/*
 * Start message queue processing
 */
MQCONN
MQOPEN
MQBEGIN

/*
 * Start ODBC connection
 */
SQLAllocEnv( &henv );
SQLAllocConnect( henv, &hdbc );
SQLConnect( hdbc, "ORACLE-XA", SQL_NTS, NULL, 0, NULL, 0 );

SQLSetConnectAttr( hdbc, SQL_ATTR_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF, 0 );

SQLAllocStmt( hdbc, &hstmt );

do
{
        MQGET

        /*
       * process the message and then execute any SQL required
       */
        SQLExecDirect( hstmt, msgBuf, msgLen );
}
while( there are more messages to process );

/*
 * Finish and close ODBC connection
 */
ret = SQLFreeStmt( hstmt, SQL_DROP );
ret = SQLDisconnect( hdbc );
ret = SQLFreeConnect( hdbc );
ret = SQLFreeEnv( henv );

/*
 * Commit the work
 */
MQCMIT

/*
 * Finish the queue processing
 */
MQCLOSE
MQDISC

The XA open String

The Transaction Manager (WebSphere MQ) uses XAConnectionString to police connections to the Resource Manager (Oracle). Its format is specified by the Resource Manager vendor, and the following details are only for use with Oracle. Full details of the string can be found in the Oracle documentation.

The string consists of the name of the Resource Manager followed by a number of key-value pairs separated by the + character:

Oracle_XA+sqlnet=ninetwo.oracle+SesTm=35+Acc=P/system/manager+Threads=true+LogDir=/tmp/xalog+Dbgfl=15+DB=test1

The first entry is the name of the Resource Manager, in this case Oracle_XA, followed by:

Logging and XA trace files

The Oracle XA library logs any error and tracing information to its trace file. This information supplements the XA error codes. For example, whether an xa_open failure is caused by an incorrect open string, failure to find the Oracle Server instance, or a logon authorisation failure.

The name of the trace file is:

xa_db_namedate.trc

where db_name is the database name you specified in the open string field DB=db_name, and date is the date when the information was logged to the trace file.

If you don't specify DB=db_name in the open string, it automatically defaults to the name NULL.

The xa_open string DbgFl

Normally, the XA trace file is only opened if an error is detected. DbgFl provides a tracing facility to record additional detail about the XA library. By default, its value is zero. It can be set to any combination of the following values. Note that they are independent, so to get printouts from two or more flags, each must be set.

  • 0x1 Trace the entry and exit to each procedure in the XA interface. This helps you find what XA calls the TP Monitor is making and what transaction identifier it is generating.
  • 0x2 Trace the entry to and exit from other non-public XA library routines. This is generally only of use to Oracle developers.
  • 0x4 Trace various other "interesting" calls made by the XA library, such as specific calls to the Oracle Call Interface. This is generally only of use to Oracle developers.

Trace file locations

The trace file can be placed in one of the following locations:

  • The trace file can be created in the LogDir directory as specified in the open string.
  • If you don't specify LogDir in the open string, the Oracle XA application attempts to create the trace file in the $ORACLE_HOME/rdbms/log directory, if it can determine where $ORACLE_HOME is located.
  • If the Oracle XA application can't determine where $ORACLE_HOME is located, the trace file is created in the current working directory.