Using Easysoft ODBC-Oracle Driver XA Support with IBM WebSphere MQ
- Introduction
- Oracle® XA Switch File
- WebSphere MQ Configuration
- ODBC Driver Setup
- Using the XA Enabled Driver within MQ
- The XA Open String
- Logging and XA Trace Files
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:
- An Application Program that defines transaction boundaries and specifies actions that constitute a transaction.
- Resource Managers such as databases or file systems that provide access to shared resources.
- A Transaction Manager that assigns identifiers to transactions, monitors their progress, and takes responsibility for transaction completion and failure recovery.
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 ODBC connection must be created AFTER the WebSphere transaction is started, so the connection can take part in the XA process. This means the SQLConnect or SQLDriverConnect must be after the MQBEGIN.
- The ODBC connection must be closed before the transaction is committed with the MQCMIT, any additional work after the MQCMIT will not be under the Transaction Manager's control.
- ODBC AUTOCOMMIT must be turned off, using the SQLSetConnectAttr API with the SQL_ATTR_AUTOCOMMIT attribute. The application should not attempt to commit or rollback any changes made via ODBC. The work done is committed by the Transaction Manager not by ODBC.
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:
- SqlNet=connection_string
This provides the Net8 database link used to logon to the Oracle® system.
- SesTm=number
Specifies the maximum length of time a transaction can be inactive before it is automatically aborted by the system.
- Acc=P/user/password
Specifies user access information.
- Threads=boolean_value
Specifies whether the application is multi-threaded. The default value is False.
- LogDir=/path/to/directory
Specifies the directory on a local machine where the Oracle® XA library error and tracing information may be logged. The default is $ORACLE_HOME/rdbms/log if ORACLE_HOME is set, otherwise it is the current directory.
- Dbgfl=number
This allows the level of detail provided in the log file to be controlled. Normally a log file is only created on error, by setting this, successful operations can also be logged.
- DB=db_name
Specifies the database name. If set, this should match the ODBC configuration setting XA_CONNECTION_STRING.
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.
0x1
Trace the entry and exit to each procedure in the XA interface. This can be useful in seeing exactly 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 of use only 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 of use only 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 do not specify LogDir in the open string, then 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 cannot determine where $ORACLE_HOME is located, then the trace file is created in the current working directory.