Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

« Previous Version 2 Next »

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

Icon

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.

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 2005

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.

  • No labels