Using Easysoft ODBC-Oracle Driver XA Support with IBM WebSphere MQ

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 ODBC-Oracle Driver

The Easysoft ODBC-Oracle Driver lets ODBC-enabled applications access Oracle® databases. In the example described in this article, it enables a WebSphere MQ application to access an Oracle® database. Because the Easysoft ODBC-Oracle 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 disabled, the Easysoft ODBC-Oracle 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 created that is then 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 ODBC-Oracle 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 will build 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

If the GUI WebSphere MQ Explorer is used, right click on the venus.queue.manager, then select the Properties option. Once that is displayed, select the "XA resource managers" option from the list. Then press 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

To create a new Resource Manager entry by editing the qm.ini file, using your choice of text editor, add a new XAResourceManager section. 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 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. It is suggested that when first testing the configuration, a LogDir be used to check that all is working as expected. After testing has finished, logging can then be disabled if required.

ODBC Driver Setup

The Easysoft ODBC-Oracle Driver needs to be provided with the information to allow it to take part in the distributed transaction. This information is added to the Data Source entry that will be used in the ODBC connection. More information about the odbc.ini file and the entries can be found in the documentation provided with the Easysoft ODBC-Oracle 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 entries that are of interest to us with respect to XA support are the ones named XA_ENLIST and XA_CONNECTION_STRING. The XA_ENLIST value is either set to 1 or 0, with 1 indicating that the connection should use the XA features. However once this is set for a entry, the connection can only be used with a Transaction Manager (in this case WebSphere MQ), so attempting to use the connection entry with 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 required for non XA use as well, create a matching entry without XA_ENLIST set.

The optional XA_CONNECTION_STRING entry is used to allow the driver to 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 ODBC-Oracle Driver can be used from any WebSphere MQ application. There are only three points to remember when writing applications.

The above steps can be seen 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 XAConnectionString used in the above examples is used by the Transaction Manager (WebSphere MQ) to allow connection 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, so the string given in the above example can be broken down as follows:

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 is useful in supplementing the XA error codes. For example, it can indicate whether an xa_open failure is caused by an incorrect open string, failure to find the Oracle® Server instance, or a logon authorization 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 is logged to the trace file.

If you do not specify DB=db_name in the open string, then it automatically defaults to the name NULL.

The xa_open String DbgFl

Normally, the XA trace file is opened only if an error is detected. The xa_open string 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 printout from two or more flags, each must be set.

Trace File Locations

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

Article Feedback

* Did this content help you?
* Please select one option based on your first choice:

(* Required Fields)


Oracle is a registered trademark of Oracle Corporation and/or its affiliates.