Databases XA support evaluation

We try to maintain here a list of databases that BTM have been tested with. Also included:

• An overall evaluation of the database's XA support
• Special features and/or limitations
• A configuration example

XA only Only databases supporting XA have been included here.

Contents

Apache Derby

Supported version(s)

Tested against versions 10.3.1.4 and 10.2.2.0.

Overall quality

Good.

Settings

All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable allowLocalTransactions.
Derby seems to support Transaction Interleaving but not very well as it is slower and generates deadlocks.

Heuristics

I could not find how to list in-doubt transactions nor how to manually terminate them.

Example ResourceLoader configuration

Evaluation

XA support in the driver and the DB look good. Documentation is too light.

DB2

Supported version(s)

Tested against 9.1 Express C. Cannot get it to work yet. Any help is welcome.

Overall quality

Settings

Heuristics

Example ResourceLoader configuration

Evaluation

Informix

Supported version(s)

Tested against versions 10.00.TC3TL and 10.00.TC5I1 both with driver version 3.00.JC3.

Overall quality

Good.

Settings

All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable allowLocalTransactions.
Informix fully supports Transaction Interleaving so you should set deferConnectionRelease to false if you properly configured the datasource and the database: all tables must be configured with row-level locking and you have to update the statistics in the system catalogs that the optimizer uses to determine the lowest-cost query plan. This basically means executing these commands:

Heuristics

This gets the list of prepared transactions: onstat -G.

I could not find to to heuristically terminate the TX however. Documentation speaks about onmode -Z <address> but I get this error when I try:

Example ResourceLoader configuration

Evaluation

You have to create your databases with create database with log or else the XA datasource will refuse to connect to it. If you do not configure driver property ifxIFX_LOCK_MODE_WAIT to be different than 0, you will get this exception if you run multiple transactions concurrently:

See http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.jdbc.doc/jdbc55.htm.

Another cause for this problem is that by default XA transactions with the same GTRID don't share locks. Setting ifxIFX_XASPEC to Y allows those locks to be shared which not only helps solving locking problems but will also improve overall speed.

See http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0602sudhakar/index.html#tight.

Microsoft SQL Server

Supported version(s)

Tested against version 2005 Express with Microsoft JDBC driver version 1.2 CTP

Overall quality

Good.

Settings

All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable allowLocalTransactions.

Heuristics

XA transactions are controlled by Microsoft DTC. Run Control Panel > Administrative Tools > Component Services. Then choose Component Services > Computers > My Computer > Distributed Transaction Coordinator > Transaction List.

From there, you have a list of all in-doubt transactions. You can force termination or forget by right clicking on the entries.

Example ResourceLoader configuration

Evaluation

XA seems to work fine when everything is setup properly but this is no simple task.

You need to grab the Microsoft JDBC driver from http://msdn.microsoft.com/data/jdbc/. Version 1.1 does not work so grab version 1.2 CTP instead. You then have to copy the sqljdbc_xa.dll inside SQL Server's binn folder then execute xa_install.sql as user sa as documented.

Note to Windows XP users:
Windows XP lacks some internal components related to DTC as discussed here: http://forums.microsoft.com/msdn/showpost.aspx?postid=262525. Microsoft has fixed this issue and released a Hotfix: http://support.microsoft.com/kb/922668. Unfortunately you cannot freely download it, you must call Microsoft support to get it. Also, it refuses to install on any non-English version of Windows.
Windows 2000 and Windows Server 2003 are said to be unaffected by this problem.

Oracle

Supported version(s)

Tested against 9.2.0.1 and 10 XE both with Oracle driver 10.2.0.1.0.

Overall quality

good.

Settings

All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable allowLocalTransactions.

Heuristics

I could not heuristically terminate a prepared transaction with COMMIT FORCE / ROLLBACK FORCE. Could be a bug in my version of XE or me not understanding how to do it. I followed these instructions without success: http://www.akadia.com/services/ora_important_part_1.html#How%20to%20remove%20%ABin-doubt%BB%20transactions. There is no show-stopper except for this problem but I suspect it works fine in other versions.

To view current XA transactions, query system view SYS.DBA_2PC_PENDING.

Example ResourceLoader configuration

Evaluation

The driver and the DB support all the features of XA but sometimes in an exotic and non-standard way. Handling of XAResource.XA_RDONLY is quite bizarre. Error messages returned by the driver are poor at best and often non-existent. Documentation is low in quality and sparse.

You need these privileges to run XA transactions:

I could not find documentation about this anywhere, I had to spy the driver to figure that out.

Sybase ASE

Supported version(s)

Tested against versions 12.5.3 and 15 both with driver jConnect version 5.5.

Overall quality

Good.

Settings

All default settings are fine. Local transactions seems to mix well with global transactions thus you can enable allowLocalTransactions.

Heuristics

the sp_transactions stored procedure shows in-doubt transactions.

Terminate an in-doubt transaction with dbcc complete_xact ('<content of xactname column of sp_transactions>', '<commit/rollback>').

Forget a transaction with dbcc forget_xact ('<content of xactname column of sp_transactions>').

User must have sa_role role to call sp_transactions or dbcc, see: http://infocenter.sybase.com/help/topic/com.sybase.help.ase_12.5.1.dtm/html/dtm/dtm1.htm.

Example ResourceLoader configuration

Evaluation

XA support is handled by Distributed Transaction Manager that is not part of the default install (must be manually selected during the installation) and must be manually enabled in the DB before it can be used: http://manuals.sybase.com/onlinebooks/group-as/asg1250e/xainterf/@Generic__BookTextView/1079.

After too many unfinished transactions (not sure here) the database segments get full because of transaction logs. Symptoms are that connections get stuck and eventually even opening a new connection freezes the client process. You can clean that using these commands as user sa:

See: http://www.isug.com/Sybase_FAQ/ASE/section1.3.html#1.3.1.

There is a bug in the JDBC driver that messes up recovery: calling commit or rollback on a heurstically terminated transaction, an XAException is thrown with an invalid error code (0) instead of XA_HEUR<something>. Stack trace looks as follow in that case:

See http://search.sybase.com/kbx/changerequests?bug_id=417417 but don't follow their workaround advice, it does more harm than it does good.

Driver and DB seems to be reasonably well implemented and documented.