JDBC 1 DataSource versus XADataSource

• XADataSource is a feature of the JDBC 2.0 Optional Package (formerly Standard Extension API).
• i-net SPRINTA supports the JDBC 2.0 Core API, only.
• i-net OPTA supports the JDBC 2.0 Core API and the JDBC 2.0 Optional Package.
• i-net MERLIA supports the JDBC 3.0 API. The JDBC 3.0 API includes the JDBC 2.0 Optional Package.

You need an XADataSource if you want to execute a Distributed Transaction. You need a Distributed Transaction if you have a transaction with 2 or more different (not equal) DataSources.

• You can use all transaction features of the application server (at least theoretically).
• You need fewer physical connections if you use a real XADataSource like DTCDataSource.

• An XADataSource always involves a performance overhead.
• The risk of a deadlock is higher. Particularly if you use an emulation like the XDataSource.
• You need more physical connections if you use an emulation XADataSource like XDataSource.

• The best solution is to use the DTCDataSource with the SQL Server 2000. The DTCDataSource works in conjunction with the MSDTC. You need to install the Resource Manager Proxy once per SQL Server instance. The SQL Server 7.0 does not implement the full XA standard. Problems can occur with some Application servers in certain programming situations.
• The next solution is to use the XDataSource with access to the master database. This works with all SQL Server versions but it is only a simulation. Real recover is not supported. You should have access to the master database (tables sysglobaltransactions and sysglobaltransactionsold).
• Another solution is to use the XDataSource without access to the master database (not preferred, though!). In this case you can have deadlocks if you use 2 connections on the same database objects in one transaction. In most of these cases you can use a JDBC 1 DataSource.

You can use all the features of your driver. If you have a JDBC 2.0 driver then you can use the JDBC 2.0 API without Distributed Transactions. That is to say the connection was created with the DriverManager. This is a part of the JDBC 1 API. Therefore, you can also use a JDBC 1 driver with this configuration.