Developing a distributed WS-AT transaction using an IBM BPM Advanced Integration Service, Part 1: Introduction and setting up the databases

Leveraging the capabilities of the Advanced Integration Services (AIS) in IBM® Business Process Manager V8.0.1 Advanced, this series describes the implementation of a distributed Web Services Atomic Transaction (WS-AT) scenario that involves two databases (Microsoft® SQL Server and IBM DB2®) installed on two different machines and accessed via two web services: one implemented in .NET and another implemented in JEE. The implementation illustrates the automatic rollback capabilities offered by the SCA-based management in an AIS in collaboration with the WS-AT protocol.

Carlo Randone (carlo_randone@it.ibm.com), Certified IT Architect , IBM

Carlo Randone photoCarlo Randone is a Certified IBM IT Architect and Open Group Master Certified IT Architect in IBM Global Business Services, Italy. Carlo has a deep knowledge of different development platforms and middleware on heterogeneous environments and operating systems. He worked for several years as a Certified Trainer and Solution Developer for a Microsoft® Certified Partner.

Since joining IBM in 2000, Carlo's main job interests are related to SOA and BPM, and their related software engineering methodologies and enabling platforms, and Enterprise Architecture planning and design. He enjoys collecting documentation and hardware pieces related to the historical development of IT, and to support this hobby he is a member of the Charles Babbage Institute.



Marco Antonioni (marco_antonioni@it.ibm.com), Certified IT Architect, IBM

Marco Antonioni photoMarco Antonioni is a Certified IT Architect and Open Group Master Certified IT Architect in IBM Software Group, Italy. Marco has a deep knowledge of WebSphere platforms (WebSphere Application Server, WebSphere Process Server, IBM Business Process Manager, IBM Operational Decision Manager, IBM Business Monitor, WebSphere Service Registry and Repository, and WebSphere Message Broker). Since joining IBM in 2000, his main role has been as a BPM Solution Architect in IBM Software Services for WebSphere.



28 August 2013

Also available in Chinese

Introduction

According to the IBM developerWorks article The benefits of using IBM Business Process Manager Advanced, IBM Business Process Manager Standard takes a fairly optimistic view of the processing environment and therefore does not provide significant out-of-the-box capabilities for addressing database transactional issues when they occur. Fortunately, however, these types of advanced capabilities are addressed in the IBM Business Process Manager Advanced (hereafter IBM BPM) environment, which provides many connectivity capabilities and adopts a rather more pessimistic view to complement the more optimistic view of IBM Business Process Manager Standard. This series shows how IBM BPM Advanced, leveraging an Advanced Integration Services (AIS) implementation, can manage an ACID global distributed transaction between a couple of different database engines (Microsoft SQL Server and IBM DB2) using a couple of web services and the Web Services Atomic Transaction (WS-AT) protocol. The implementation also shows the automatic commit and rollback capabilities of the SCA-based implementation in BPM Advanced, leveraging WS-AT. You can implement this type of management by configuring interfaces of the web services involved in the transaction (the Charge and Credit web services in the example), without having to do any special custom code implementation.

The transactional scenario presented in this series is similar to the one presented in the developerWorks four-part series Developing a transactional Advanced Integration Service with IBM Business Process Manager, but in this new scenario the two databases are not directly accessed using Java™ components in a BPEL microflow, but the data management is mediated by a couple of transactional web services, one implemented in .NET to access SQL Server, and the other implemented in Java to access DB2. These web services are invoked (in a global transaction) by an enterprise service bus (ESB) mediation module defined as the implementation of a BPM Advanced Integration Service.

The core of the implementation presented in this series is a transactional AIS. An AIS is used to call a service implemented in IBM Integration Designer from a BPM business process definition (BPD). This kind of integration pattern is described in the IBM developerWorks article: Linking business processes and enterprise services together using IBM Business Process Manager Advanced.

Web services

Web services can be used to expose in a platform-independent mode functional capabilities and features. In particular, in the proposed scenario described here, a couple of web services (implemented with different technologies) were used to "mediate" the access to a couple of different databases.

In a scenario like the one described here, one team, for example, skilled on Windows and .NET, can develop a Windows-based web service to access a SQL Server database, and another different team (not necessarily at the same time) can develop a similar web service to access a DB2 database, but using a JEE approach like JAX-WS and the WebSphere Application Server. The two teams can work in different organization and, as will be demonstrated, on very different technological platforms.

From the point of view of the technical integration developer, the option to leverage in IBM Business Process Manager the powerful features of the SCA programming model is an opportunity not only to reuse good skills, but also to support the high-level design and development of business-oriented services with strong and reliable technical "base services" designed and developed with IBM Integration Designer.

Note that the two web services used in this series may also be invoked directly by the BPMN process at the level of IBM Process Designer (disabling the transactional policies), but in this case the transactions carried out by one and the other (on their databases) would not be considered part of the same transactional context. The work to make the two services (and the related involved databases) within the same context of global transaction is instead made ​​possible by the implementation as part of an SCA-based Advanced Integration Service implemented with the IBM Integration Designer. In particular, in the scenario described here, the choice was to use an ESB mediation, but it would be also possible to use a BPEL microflow (in a way similar to the one presented in the previous series of articles "Developing a transactional Advanced Integration Service with IBM Business Process Manager").


Series overview

This five-part series covers the following major tasks to implement the distributed transaction scenario, and provides sample execution cases to illustrate the function of the scenario:

  1. Create the databases and configure the data sources (Part 1)
  2. Configure the transactional support and implement the two transactional web services (for the Credit and Debit operations, respectively) (Part 2)
  3. Define the BPMN process in Process Designer (Part 3)
  4. Implement the AIS in Integration Designer, based on an SCA mediation (ESB) flow, with the invocation of the two transactional web services (Part 4)
  5. Review execution cases (Part 5)

The remainder of this article provides an overview of the scenario and walks you through preliminary set-up, as well as the creation and configuration of the databases.


Scenario implementation details

The scenario described in this article can be applied in many different cases and contexts, but the implementation described here was implemented on the following products and versions:

Machine A (a Windows 7 computer named ADMINIB-PHG662G) with the following configuration:

  • OS: Microsoft Windows 7 Professional 64-bit (with Service Pack 1).
  • DB: Microsoft SQL Server 2008 Express Edition (X64) (with Service Pack 3), version 10.0.5500.0.
  • On the SQL Server, a Bank1 database.
  • .NET Framework 4.0 version 4.0.30319.1, as runtime framework
  • Internet Information Services (IIS) 7.5, as web server and application server (together with the .NET framework)
  • The implementation of a Charge .NET WCF (Windows Communication Foundation) web service, developed to manage the charge operation on the Bank1 SQL Server database.
  • Visual Studio 2010 as the Integrated Development Environment (IDE) for the .NET web service implementation in C# (C-Sharp) language.

Machine B is a Windows Server 2008 server named IBMBPM with the following configuration:

  • OS: Microsoft Windows Server 2008 R2 64-bit (with Service Pack 1)
  • DB: IBM DB2 9.7 for Windows (DB2 v9.7.400.501, Fix pack 4, Build level s110330, IP23238)
  • BPM: IBM Business Process Manager Advanced Version 8.0.1.0
    • IBM WebSphere Application Server Network Deployment 8.0.0.5
    • IBM Integration Designer 8.0.1.1
    • IBM Process Designer 8.0.1.0
  • On the DB2 Server, a Bank2 database.
  • The implementation of a Credit JAX-WS web service to manage the credit operation on Bank2 DB2 database.

Note: For the BPM 8.0.1 Advanced used in this scenario, the last series of fixes available as of May 2013 was applied. The exact version of the IBM BPM used is 8.0.1000.20121102_2136.

The created .NET-based web service on Windows machine A will be hosted by Windows Internet Information Services (IIS), which you must enable before use. The following instructions apply to a Windows 7 machine, but similar steps can be executed on other Windows versions like Vista or Windows Server 2008:

  • On the Windows 7 machine, select Windows Start => Control Panel.
  • Select Programs and Features.
  • In the left pane, click Turn Windows features on or off.
  • The Windows features dialog box opens. Check Internet Information Services.
  • Double-click Web Management Tools, then double-click IIS 6 Management Compatibility, and then check IIS 6 Metabase and IIS 6 Configuration Compatibility.
  • Double-click World Wide Web Services, double-click Application Development Features, then check ASP.NET.
  • Click OK.

Figure 1 shows a working configuration with IIS enabled.

Figure 1. Enabling Windows IIS
Enabling Windows IIS

An interesting aspect of the implementation presented here is the level of web services specification supported. In particular, the two web services involved support the SOAP 1.1 and WS-AT 1.1 specifications. Table 1 lists the namespaces of these specifications.

Table 1. Web services versions and schemas adopted
ElementVersion 1.1 specifications adopted in the sample
SOAP 1.1 Namespacehttp://schemas.xmlsoap.org/soap/envelope/
SOAP 1.1 Binding in WSDL http://schemas.xmlsoap.org/wsdl/soap/
WS-AT Schema http://docs.oasis-open.org/ws-tx/wsat/2006/06
WS-AT specification http://docs.oasis-open.org/ws-tx/wstx-wsat-1.1-spec.pdf

The choice to adopt these specification levels for this sample comes from theoretical and pragmatic considerations:

  • The availability of official support for these specification levels in both the current Microsoft .NET and IBM WebSphere / IBM BPM platforms.
  • The diffusion and wide adoption of these specifications.

In particular, from the point of view of product support, IBM BPM V8.0.1 Advanced supports SOAP 1.1 and 1.2, and WS-AT 1.0, 1.1 and 1.2. On Windows, .NET WCF 4 supports SOAP 1.1 and 1.2, and WS-AT 1.0 and 1.1.


Scenario overview

In a simple Java application that interacts with a single database management system (DBMS), the application can demarcate transaction boundaries using explicit SQL commits and rollbacks. A more sophisticated application environment, with multiple transactional resources distributed across a network, requires a dedicated component to manage the complexity of coordinating transactions. A transaction manager works with applications and application servers to provide services to control the scope and duration of transactions. A transaction manager also helps coordinate the completion of global transactions across multiple transactional resource managers (for example, database management systems, as in the sample presented in this article), provides support for transaction synchronization and recovery, and may provide the ability to communicate with other transaction manager instances.

Global transactions span multiple resource managers. To coordinate global transactions, the coordinating transaction manager and all participating resource managers should implement a multi-phased completion protocol, such as the two-phase commit (2PC) protocol. Although there are several proprietary implementations of this protocol, X/Open XA is the industry standard.

The two-phase commit protocol ensures that either all participants commit changes or none of them does. The implementation of the two-phase commit ACID (Atomicity, Consistency, Isolation, and Durability) protocol is supported in the SCA implementation in BPM V8 Advanced, leveraging the capabilities of the underlying IBM WebSphere Application Server.

Similar considerations can be applied to a Windows .NET-based environment, considering features like the Microsoft Distributed Transaction Coordinator (MSDTC) and the .NET Enterprise Services (COM+). In particular, the MSDTC service is a component of modern versions of Microsoft Windows that is responsible for coordinating transactions that span multiple resource managers, such as databases and queue menagers. MSDTC performs the transaction coordination role for components, usually with COM and .NET architectures.

With the consolidation and diffusion of the service-oriented architecture (SOA) design principles, the transactional concept has also been applied to the world of SOAP web services, with the WS-AT standard. For a good introduction to the WS-Atomic Transaction protocol, refer to the developerWorks article Making web services enterprise-ready. Using the WS-Atomic Transaction protocol and WebSphere Application Server.

WS-AT is a specification that extends the reach of traditional transaction behavior to the web services world. Its sibling in the WS-Transaction area is WS-Business Activity (WS-BA), which defines compensation mechanisms for atomic business activities. Both specifications are part of the WS-Transaction (WS-Tx) group.

The WS-AT specification is supported on both the platforms used in the scenario presented in this series: the Microsoft .NET framework and the WebSphere Application Server/ IBM BPM Java, Enterprise Edition (JEE) servers.

Based on these conceptual and theoretical premises, let's take a look at the transactional scenario described in this article. As described earlier, the scenario uses a couple of different databases, Bank1 and Bank2, based on two different database engines on two different machines. The database Bank1 is a SQL Server 2008 (Express) database on Windows machine A, and the database Bank2 is a DB2 UDB (v 9.7) database on Windows machine B. Machine B also has an installation of a BPM V8 Advanced with Process Designer and Integration Designer.

The data on the two databases is managed through a couple of web services:

  • A Charge web service (developed in .NET WCF) on machine A to interface with and operate the SQL Server database.
  • A Credit web service (developed in JAX-WS) on machine B to interface with and operate the DB2 database.

Obviously it's possible to implement this demonstration using two databases (also based on the same engine) on the same machine. Or, the BPM engine (with Process Center) can be installed on a third machine, separate from the two database machines. And the web services can also be deployed on different machines. The focus here is to demonstrate a scenario similar to a real-world context, in which the data sources involved are often based on different database engines that are installed on different physical (or virtual) machines.

On both databases there is an Accounts table with account names (in the Description field) and amounts (in the Amount field). In IBM BPM there is a process application named KWSBPM (KWS is the global prefix adopted in this demonstration). In this process app, there is a business process definition (BPD) named KWSBPM – Process1. The aim of this simple process is to:

  1. Ask the user (by a human service with a related coach-based presentation) the parameters for the requested transaction. For example, "Transfer an amount of 10 value units from the Karl account on Bank1 to the Donald account on Bank2."
  2. Execute a system task with the implementation (by an AIS) of the transaction. As you'll see later, the AIS is implemented by an SCA-based mediation flow that invokes a couple of web services using the WS-AT protocol.
  3. A coach to show the positive execution of the transaction and another coach to show the failure of the transaction, with some details about the reason for the failure (to explain the consequent rollback of the transaction to the user).

Using different values for the parameters in #1 above, you can test different execution scenarios, such as:

  • A couple of existing accounts on Bank1 and Bank2, with an amount to transfer that doesn't violate the availability of funds on the "charge" (debit) account drives to a positive transaction commit.
  • The non-existence of the "source" (to charge) or of the "target" (to credit) account drives to an error and transaction rollback
  • The attempt to transfer an amount greater that the availability on the "to charge" account drives to an error and transaction rollback.

To emphasize the management of the automatic transaction rollback provided by the SCA-based mediation we've adopted to implement the BPM AIS, the credit operation is executed in the mediation flow before the charge operation, so that the rollback of the credit operation after a charge error (for insufficient money, for example) on the debit account will be evident and explicit. The technical scenario implemented in the AIS is as follows:

  • Time t1: Credit on DB2 (Bank2), using the web service WSCreditBank2Svc
  • Time t2: Debit on SQL Server (Bank 1), using the web service WSChargeBank1Svc

Figure 2 shows this scenario, which we'll describe in more detail in the following sections of the article.

Figure 2. The transactional scenario
The transactional scenario

The building blocks of the proposed solution are:

  • Databases and data source configuration
  • The BPMN process in the BPM Process Designer
  • The AIS implementation in Integration Designer, based on a SCA ESB mediation with the invocation of a couple of web services (one based on .NET WCF and the other based on Java JAX-WS), for the Credit and Debit operations, respectively.

One interesting aspect of this solution is the significant level of layering, from a business-level process to a data-layer (data resource tier). Another important element to consider is the strong decoupling introduced by using the web services and the WS-AT protocol to manage the found transfer and the transactional context propagation.

Considering that the AIS is implemented on the BPM Advanced server, the "transaction manager" in this demonstration is the WebSphere Application Server, and the transactional attributes (as will be described later) are configured on the SCA Assembly diagram of the SCA mediation. In other words, WebSphere Application Server will be the "transaction coordinator", and both SQL Server and DB2 will be "transactional resources," managed by the WebSphere Application Server transaction coordinator and the Microsoft Distributed Transaction Coordinator (MSDTC).


Getting started

To understand the solution components and to complete the steps described in this article, you must have IBM Business Process Manager Advanced V8.0.1, including IBM Process Designer and IBM Integration Designer installed (we tested all on a Windows Server 2008 R2 64-bit machine). For the Windows-based part of the solution, you must have Visual Studio 2010 and SQL Server 2008.

You should be familiar with the IBM Process Designer and IBM Integration Designer authoring tools and have an understanding of AIS, SCA-based implementations, .NET, and Java-based web services.

The following four files are provided for download with this article. You can download and import these files and refer to them as you follow the steps to implement the sample scenario in this article.

KWSBPM.twx
A complete KWSBPM process application for BPM V8.0.1 Advanced, which you can import into Process Designer.
KWSBPM_Main.zip
A project interchange file for the KWSBPM AIS implementation, which you can download and import into Integration Designer. This AIS implements the transactional activity of the solution, invoking a couple of web services (in WS-AT) from an ESB SCA-based mediation flow.
WSChargeBank1Soln.zip
The .NET web service, which includes compressed solution and project files for the Microsoft Visual Studio 2010 IDE.
WSCreditBank2Project.zip
The JAX-WS web service, which includes a dedicated workspace or project for IBM Integration Designer.

You'll need to associate the SCA implementation (in Integration Designer) with the process application (in Process Designer). In Integration Designer, open the Process Center perspective, and click Open in the workspace beside the process application you want to associate with the SCA service. For this article, that is the KWSBPM process app.

To enable the proper execution of the transactional AIS, you need to create a couple of databases (a Bank1 SQL Server database on machine A and a Bank2 DB2 database on machine B), and configure a data source for the DB2 Bank2 database on the IBM WebSphere Application Server (on the BPM machine B), as described in the following sections.


Creating the databases and configuring the data sources

Our scenario involves a distributed transaction: a simple money transfer between two bank accounts, hosted by different banks, and also on different databases. This is quite a common scenario.

We'll use a SQL Server 2008 (Express) and a DB2 (V9.7) database engine. The first database, Bank1, is on the SQL Server on machine A, and contains an Accounts table, which holds Bank1's accounts. The second database, Bank2, is on DB2 on machine B, and contains another Accounts table, as shown below.

Listing 1. The Accounts table for the Bank1 database on the SQL Server on machine A
            ID 	Description 	Amount
1 	Karl 		100
2 	Albert 		200
3 	Ricky 		300
Listing 2. Accounts table for Bank2 database on DB2 on machine B
ID 	Description 	Amount
1 	Donald 		1000
2 	Mickey 		2000
3 	Minnie 		3000

These values will be adopted in the use cases described in this series, so you can insert this information into the two databases after creating the databases, tables, constraints and triggers as described in the following sections.

The Accounts tables have a primary key on the ID field (an Identity field) and a UNIQUE constraint on the Description field. Also an INSERT / UPDATE trigger is created on the Accounts tables; its goal is to check the remaining amount of a single account, so that it is not possible to have negative amounts in the tables: any INSERT or UPDATE operation that makes negative the remaining amount of an account will be aborted, rolling back the implicit (local) transaction that wraps the INSERT or UPDATE operation.

The two databases described here are the same databases already used for the transactional scenario described in the developerWorks four-part series Developing a transactional Advanced Integration Service with IBM Business Process Manager. The set-up of the two databases is described in Part 1 of the previous series Introduction and setting up the databases. However, the instructions are also included in this article for completeness. If you have already implemented these databases, you can skip the next two sections.

Create the Microsoft SQL Server database

Note: If you have already implemented this database in Part 1 of the previous series, you can skip this section.

As described earlier, one of the two databases in the example is a Microsoft SQL Server 2008 (Express) database, installed on machine A. In our example, the SQL Server database engine is configured for the mixed authentication mode, and we can connect to the database with an explicit SQL Server login (for example, the administrative account "sa" or another account with the necessary permissions). As you can see in Figure 4, the SQL Server and Windows Authentication mode is selected (that is, the mixed authentication mode). To set SQL Server 2008 Express to this security mode, follow the procedure described in How to: Change Server Authentication Mode.

Note that if you select the Windows authentication mode during installation, the sa login is disabled. If you later change the authentication mode to SQL Server and Windows Authentication mode, the sa login remains disabled. To enable the sa login in this case, use the ALTER LOGIN statement. The sa login can only connect to the server using SQL Server Authentication. For sake of simplicity in our example, the SQL Server data sources in WebSphere Application Server and BPM are configured with an authentication alias using the sa login. Figure 4 shows the configuration of the mixed mode on SQL Server 2008 using the SQL Server Management Studio.

In our implementation, the password for the sa login is passed to the process, and then to the AIS and to the Charge .NET web service application using the connection string to manage the transaction on the SQL Server database. In this implementation the value of this password is supposed to be simply mypwd. Obviously you need to change this value to your sa password, use another login, or even use another means to pass the password to the service, such as a configuration file in the Charge web service application, or using a Windows Authentication mode.

Figure 3. The SQL Server mixed authentication mode
The SQL Server mixed authentication mode

The name of the SQL Server database is Bank1. In this database there is a table called Accounts with the following three fields:

  • ID: the numeric id of the account (int, identity (autonumber), not null, primary key)
  • Description: the descriptive name of the account (varchar, not null, unique)
  • Amount: the amount on the account (int, not null)

Note that there is a "unique" constraint on the Description field.

Listing 1 shows the TSQL script to create the Bank1 database, the Accounts table, a trigger Accounts_InsUpd on the table, and to populate the table with some sample data.

Listing 3. Creation of the Bank1 database and related objects on SQL Server
-- *** BANK1 ***--
-- Create the Database (with default location, options and size)
CREATE DATABASE Bank1
GO

-- Create the table Bank1.Accounts, with its trigger and its data
USE Bank1
GO

CREATE TABLE Accounts(
	ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Description varchar(50) NOT NULL UNIQUE,
	Amount int NOT NULL
	)
GO

CREATE TRIGGER Accounts_InsUpd ON Accounts FOR INSERT, UPDATE AS
/* Check the remaining amount: it has to be non-negative */
/* If the INSERT/UPDATE makes the remaining amount negative, it is aborted */
/* Warning: this trigger is implemented as a one-row trigger; it doesn't manage multi */
/* row INSERT/UPDATE */
DECLARE @remaining int	-- Remaining amount
IF (SELECT COUNT(*) FROM inserted) = 1
	BEGIN
	SELECT @remaining = Amount FROM inserted
	IF @remaining < 0
		BEGIN
		RAISERROR('Insufficient money.', 11, 1)
		ROLLBACK TRANSACTION
		END
	END
GO

INSERT Accounts VALUES('Karl',   100)
INSERT Accounts VALUES('Albert', 200)
INSERT Accounts VALUES('Ricky',  300)
GO

Later in this series, you'll see how an exception is raised in the BPM AIS implementation when the update trigger on the Accounts table detects an attempt to set a negative amount.

To read the values from the table you can issue the select * from dbo.Accounts command using SQL Server Management Studio, as shown in Figure 4.

Figure 4. Reading of the Accounts table on SQL Server
Reading of the Accounts table on SQL Server

To read the uncommitted values (for example in the time between a prepare and a commit or rollback), you can use the with (readuncommitted) SQL Server table hint as follows: select * from dbo.Accounts with (readuncommitted) Figure 5 shows this kind of query in SQL Server Management Studio.

Figure 5. Reading of the uncommitted values on SQL Server
Reading of the uncommitted values on SQL Server

The readuncommitted table hint specifies that "dirty reads" are allowed. No shared locks are issued to prevent other transactions from modifying data read by the current transaction, and exclusive locks set by other transactions do not block the current transaction from reading the locked data. Allowing dirty reads can cause higher concurrency, but at the cost of reading data modifications that are then rolled back by other transactions. Note that this may present users with data that was never committed, or cause users to see records twice (or not at all). Later you'll see an equivalent option to allow dirty reads on a select statement on the DB2 database.

You can download Microsoft SQL Server 2008 Express Edition with SP3 here, and the Microsoft SQL Server 2008 Management Studio Express here.

For simplicity, we'll access the SQL Server with the standard administrative "sa", and the table name will be prefixed by the default SQL Server schema name "dbo", so the complete name to access the Accounts table is: dbo.Accounts.

It's important to consider the fact that a default installation of SQL Server supports XA-based transactions managed by the Microsoft Distributed Transaction Coordinator (MSDTC), but additional configuration is required to enable the XA transactional support when the SQL Server engine is accessed by a JDBC driver, as in our example. This enablement is described in Microsoft SQL Server 2008 – Understanding XA Transactions and other sources, such as the IBM WebSphere Enterprise Service Bus Information Center. The JDBC distributed transaction components are included in the xa directory of the Microsoft JDBC driver for the SQL Server installation. These components include the xa_install.sql and sqljdbc_xa.dll files. Complete the following steps to do this one-time configuration on the SQL Server installation to enable the XA transactional support via JDBC (in our scenario these configurations are required on Machine A, which is running SQL Server 2008 Express).

For simplicity, we'll access the SQL Server with the standard administrative "sa", and the table name will be prefixed by the default SQL Server schema name "dbo", so the complete name to access the Accounts table is: dbo.Accounts.

Enable the MSDTC service for XA transactions

The MSDTC service must be marked Automatic in Service Manager to make sure that it is running when the SQL Server service is started. To enable MSDTC for XA transactions, do the following:

  1. Select Start => Control Panel => Administrative Tools, and open Component Services.
  2. Expand Component Services and Computers, then right-click My Computer and select Properties.
  3. Click the MSDTC tab, and then click Security Configuration.
  4. Check Enable XA Transactions, then click OK. This restarts the MSDTC service.
  5. Click OK again to close the Properties dialog, then close Component Services.
  6. Stop and then restart SQL Server to ensure that it syncs up with the MSDTC changes.
Figure 6. Configure MSDTC security and XA support
Configure MSDTC security and XA support

Configure the JDBC distributed transaction components

To configure the JDBC transaction components, do the following:

  1. Copy sqljdbc_xa.dll from the JDBC installation directory to the bin directory of every SQL Server computer that will participate in distributed transactions.

    Note: If you're 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 an 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. If you are using XA transactions with a 64-bit SQL Server on an Itanium processor, use the sqljdbc_xa.dll file in the IA64 folder.

  2. Execute the database script xa_install.sql on every SQL Server instance that will participate in distributed transactions. 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'll need to run this script as an administrator of the SQL Server instance.
  3. To grant permissions to a specific user to participate in distributed transactions with the JDBC driver, add the user to the SqlJDBCXAUser role.

Create the IBM DB2 database

Note: If you have already implemented this database in Part 1 of the previous series, you can skip this section.

Machine B, which is running DB2 V9.7, has a Bank2 database with an Accounts table and a couple of triggers (for Insert and Update) to ensure that there are no negative amounts in the table, similar to the SQL Server Bank1 database.

Listing 2 shows the code to create the Accounts table on the Bank2 database, with related constraints and triggers. For simplicity, the schema name used here is ADMINISTRATOR, but you can make a different choice. The first part of Listing 2 is an example script for the initial creation of an empty Bank2 database.

Listing 4. Creation of Bank2 database and related objects on DB2
------------------------------------------------
-- Possible DDL Statement for Bank2 database creation
------------------------------------------------

CREATE DATABASE BANK2 AUTOMATIC STORAGE YES  ON 'C:\' DBPATH ON 'C:\' ALIAS BANK2 
USING CODESET IBM-1252 TERRITORY US COLLATE USING SYSTEM PAGESIZE 4096 WITH 'BANK2';

------------------------------------------------
-- DDL Statements for table "ADMINISTRATOR"."ACCOUNTS"
-- Note: to be executed on BANK2 database
------------------------------------------------

CREATE TABLE "ADMINISTRATOR"."ACCOUNTS"  (
		  "ID" INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (  
		    START WITH +0  
		    INCREMENT BY +1  
		    MINVALUE +0  
		    MAXVALUE +2147483647  
		    NO CYCLE  
		    NO CACHE  
		    NO ORDER ) ,
		  "DESCRIPTION" VARCHAR(50) NOT NULL ,
		  "AMOUNT" INTEGER NOT NULL )   
		 IN "USERSPACE1" ;

-- DDL Statements for indexes on Table "ADMINISTRATOR"."ACCOUNTS"

CREATE INDEX "ADMINISTRATOR"."CC1348226303097" ON "ADMINISTRATOR"."ACCOUNTS"
		("ID" ASC)
		
		COMPRESS NO ALLOW REVERSE SCANS;

-- DDL Statements for primary key on Table "ADMINISTRATOR"."ACCOUNTS"

ALTER TABLE "ADMINISTRATOR"."ACCOUNTS"
	ADD CONSTRAINT "CC1348226303097" PRIMARY KEY
		("ID");

-- DDL Statements for unique constraints on Table "ADMINISTRATOR"."ACCOUNTS"

ALTER TABLE "ADMINISTRATOR"."ACCOUNTS"
	ADD CONSTRAINT "CC1348501255207" UNIQUE
		("DESCRIPTION");

-------------------------------
-- DDL Statements for Triggers
-------------------------------

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE TRIGGER ADMINISTRATOR.ACCOUNTS_INS AFTER  INSERT  ON ADMINISTRATOR.ACCOUNTS
 REFERENCING  NEW AS new_row  NEW_TABLE AS new_table  FOR EACH ROW  MODE
DB2SQL
BEGIN ATOMIC
DECLARE remaining INT;--
SET remaining  = new_row.Amount;--
IF remaining < 0 THEN
SIGNAL SQLSTATE '70006' SET MESSAGE_TEXT = 'Insufficient money.';--
END IF;--
END;

SET CURRENT SCHEMA = "ADMINISTRATOR";
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","ADMINISTRATOR";
CREATE TRIGGER ADMINISTRATOR.ACCOUNTS_UPD AFTER  UPDATE OF AMOUNT ON ADMINISTRATOR.ACCOUNTS
 REFERENCING  OLD AS old_row  NEW AS new_row  OLD_TABLE AS old_table  NEW_TABLE
AS new_table  FOR EACH ROW  MODE DB2SQL
BEGIN ATOMIC
DECLARE remaining INT;--
SET remaining  = new_row.Amount;--
IF remaining < 0 THEN
SIGNAL SQLSTATE '70006' SET MESSAGE_TEXT = 'Insufficient money.';--
END IF;--
END;

-------------------------------------------
-- Create some values in the Bank2 database
-------------------------------------------
INSERT INTO ADMINISTRATOR.Accounts (DESCRIPTION, AMOUNT) VALUES('Donald', 1000);
INSERT INTO ADMINISTRATOR.Accounts (DESCRIPTION, AMOUNT) VALUES('Mickey', 2000);
INSERT INTO ADMINISTRATOR.Accounts (DESCRIPTION, AMOUNT) VALUES('Minnie', 3000);

Note that in DB2 we've used SQLSTATE >=70000 to define the custom error code for Insufficient money.

To read the values from the table from the DB2 Control Center, open the Bank2 database, right-click the Accounts table, and select Open, as shown in Figure 7.

Figure 7. Reading the Accounts data from DB2 using the Control Center
Reading the Accounts data from DB2 using the Control Center

Alternatively, you can issue the following commands using the DB2 command line processor, as shown in Figure 8:

connect to BANK2
SELECT * FROM ADMINISTRATOR.ACCOUNTS
Figure 8. Reading the Accounts data from DB2 using the command line
Reading the Accounts data from DB2 using the command line

You can read the uncommitted values from the Accounts table using the following command:
select * from administrator.accounts with ur

Figure 9. Read the uncommitted values from DB2
Read the uncommitted values from DB2

The with ur option on the select statement sets the uncommitted read isolation level.

Configure the DB2 data source for Bank2 on machine B

Note: The configuration presented is same as that in the section "Configure the DB2 data source for Bank2 on machine B" in Part 1 of the previous series. If you have already configured this data source, you can skip this section.

To enable the access to the Bank2 DB2 databases by the Credit web service on WebSphere Application Server (on machine B), you need to configure a data source on WebSphere Application Server. Specifically, to connect to the Bank2 DB2 database on machine B, you need a JDBC driver for DB2, an authentication alias and a data source. The JDBC driver you can use, DB2 Using IBM JCC Driver (XA) is already defined on WebSphere Application Server for BPM. This driver supports XA transactions, and the implementation class name for the driver is com.ibm.db2.jcc.DB2XADataSource.

Figure 10. JDBC provider for DB2
JDBC provider for DB2

Figure 11 shows the authentication alias used to access the Bank2 database on DB2, in our example we use IBMBPMNode01/bpmadmin. The important thing is that the account must have the required rights to access and manipulate the data on the DB2 Bank2 database and on the Accounts table defined in the database. For example, since we configured the schema for the Accounts table as ADMINISTRATOR, you can use the administrative account to define the authentication alias, or another account (like bpmadmin) that must be enabled with the correct authorization on the Bank2 database.

Figure 11. Authentication alias to access DB2
Authentication alias to access DB2

Configure the jdbc/bank2db2 data source used to access the Bank2 database on machine B (the IBM BPM server in our example) as follows (refer to Figures 12 and 13).

  • Scope: the scope on which your provider and data source are defined; for example cells:IBMBPMNode01Cell:nodes:IBMBPMNode01:servers:server
  • Provider: the selected JDBC provider for DB2; for example DB2 using IBM JCC Driver (XA) you defined earlier
  • Name: bank2db2
  • JNDI name: jdbc/bank2db2
  • Check Use this data source in container managed persistence (CMP)
  • Description: Any descriptive text
    Figure 12. JDBC data source to access DB2 (part 1 of 2)
    JDBC data source to access DB2 (part 1 of 2)
  • Select Select a data store helper class and for Data store helper classes provided by WebSphere Application Server select DB2 Universal data store helper
  • Authentication alias for XA recovery: IBMBPMNode01/bpmadmin
  • Component-managed authentication alias: IBMBPMNode01/bpmadmin
  • Driver Type: 4
  • Database name: BANK2
  • Server name: the server name; for example IBMBPM
  • Port number: the port number for the database DB2; for example 50000
    Figure 13. JDBC data source to access DB2 (part 2 of 2)
    JDBC data source to access DB2 (part 2 of 2)

In the configuration shown in Figure 13, note that the authentication alias IBMBPMNode01/bpmadmin is used for both Authentication alias for XA recovery and Component-managed authentication alias.

In summary, to access the DB2 Bank2 database on machine B, you defined a data source named bank2db2 (jdbc name: jdbc/bank2db2) and a bpmadmin authentication alias.


Conclusion

In Part 1 of this series, we gave you an overview and described the components of the distributed transaction scenario we will build in this series. You've learned how to create the databases on both the SQL Server and the DB2 Server, and how to configure the data sources on the IBM BPM application server. In Part 2, you'll learn how to configure the transactional support and implement the two transactional web services (for the Credit and Debit operations, respectively).


Acknowledgements

The authors would like to thank their colleagues Giuseppe Bottura and Simone Chiucchi for their reviews of this article, and their colleagues Stefano Angrisano, Matteo Franciolli, and Daniele Rossi for their contributions. We also want to thank Andrew J. Howes, Billy Lo, Frank I. Toth, Dave Screen, Callum Jackson, Konstantin Luttenberger and Oliver Rebmann for their inspiring and thoughtful developerWorks articles.


Downloads

DescriptionNameSize
Process application for IBM Process Designer)KWSBPM.twx1.3MB
AIS (for IBM Integration Designer)KWSBPM_Main.zip276KB
.NET web service (for Visual Studio)WSChargeBank1Soln.zip47KB
JAX-WS web service (for Integration Designer)WSCreditBank2Project.zip28KB

Resources

Resources specific to the .NET platform

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Business process management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Business process management, WebSphere
ArticleID=943538
ArticleTitle=Developing a distributed WS-AT transaction using an IBM BPM Advanced Integration Service, Part 1: Introduction and setting up the databases
publish-date=08282013