IBM BPM, V8.0.1, All platforms > Install IBM BPM > IBM BPM Standard > Install IBM BPM Standard > On AIX > Stand-alone environment > Typical installation and configuration > SQL Server database server > Create databases

Configure XA transactions

You must configure XA transactions after the Microsoft SQL Server database is installed and before you start the server. The SQL Server JDBC driver provides support for Java Platform, Enterprise Edition/JDBC 2.0 optional distributed transactions. JDBC connections obtained from the SQLServerXADataSource class can participate in standard distributed transaction processing environments such as Java Platform, Enterprise Edition (Java EE) application servers.

Failure to configure the XA transactions can result in the following error when the server starts: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'."..


Procedure

  1. The MS DTC service should be marked Automatic in Service Manager to make sure that it is running when the SQL Server service is started. To enable MS DTC for XA transactions, you must follow these steps:

    On Windows XP and Windows Server 2003:

    1. Select Control Panel > Administrative Tools > Component Services.

    2. Select Component Services > Computers and right-click My Computer, and select Properties.

    3. Click the MSDTC tab, and then click Security Configuration.

    4. Select the Enable XA Transactions check box, and then click OK. This will cause a MS DTC service restart.

    5. Click OK again to close the Properties window, and then close Component Services.
    6. Restart SQL Server to ensure that it syncs up with the MS DTC changes.

    On Windows Vista and Windows 7:

    1. Select Control Panel > Administrative Tools > Component Services.

    2. Select Component Services > Computers > My Computer > Distributed Transaction Coordinator.
    3. Right-click Local DTC and then select Properties.

    4. Click the Security tab on the Local DTC Properties window.

    5. Select the Enable XA Transactions check box, and click OK. This will restart the MS DTC service.

    6. Click OK again to close the Properties window, and then close Component Services.
    7. Restart SQL Server to ensure that it syncs up with the MS DTC changes.

  2. Configure the JDBC Distributed Transaction Components:
    1. Download "Microsoft SQL Server JDBC Drive 2.0" driver from the Microsoft Site using the URL from Resources section.
    2. Extract the archive to any folder.

    3. Copy the sqljdbc_xa.dll file from the JDBC unarchived directory to the Binn directory of SQL Server computer. If you are using XA transactions with a 32-bit SQL Server, use the sqljdbc_xa.dll file in the x86 folder, even if the SQL Server is installed on a x64 processor. If you are using XA transactions with a 64-bit SQL Server on the x64 processor, use the sqljdbc_xa.dll file in the x64 folder.

    4. Run the xa_install.sql database script on SQL Server . This script installs the extended stored procedures that are called by sqljdbc_xa.dll. These extended stored procedures implement distributed transaction and XA support for the Microsoft SQL Server JDBC Driver. You will need to run this script as an administrator of the SQL Server instance.

    5. To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role in the master database (for example, for lombardi user add master database in User mappings and check SqlJDBCXAUser role).

Create SQL Server databases