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 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").
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:
- Create the databases and configure the data sources (Part 1)
- Configure the transactional support and implement the two transactional web services (for the Credit and Debit operations, respectively) (Part 2)
- Define the BPMN process in Process Designer (Part 3)
- Implement the AIS in Integration Designer, based on an SCA mediation (ESB) flow, with the invocation of the two transactional web services (Part 4)
- 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 126.96.36.199
- IBM WebSphere Application Server Network Deployment 188.8.131.52
- IBM Integration Designer 184.108.40.206
- IBM Process Designer 220.127.116.11
- 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
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
|Element||Version 1.1 specifications adopted in the sample|
|SOAP 1.1 Namespace||http://schemas.xmlsoap.org/soap/envelope/|
|SOAP 1.1 Binding in WSDL||http://schemas.xmlsoap.org/wsdl/soap/|
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.
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:
- 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."
- 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.
- 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 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).
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.
- A complete KWSBPM process application for BPM V8.0.1 Advanced, which you can import into Process Designer.
- 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.
- The .NET web service, which includes compressed solution and project files for the Microsoft Visual Studio 2010 IDE.
- 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
Figure 3. 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
To read the uncommitted values (for example in the time between a prepare
and a commit or rollback), you can use the
(readuncommitted) SQL Server table hint
select * from dbo.Accounts with (readuncommitted)
shows this kind of query in SQL Server Management Studio.
Figure 5. Reading of the uncommitted values on SQL Server
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.
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:
- Select Start => Control Panel => Administrative Tools, and open Component Services.
- Expand Component Services and Computers, then right-click My Computer and select Properties.
- Click the MSDTC tab, and then click Security Configuration.
- Check Enable XA Transactions, then click OK. This restarts the MSDTC service.
- Click OK again to close the Properties dialog, then close Component Services.
- 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 the JDBC distributed transaction components
To configure the JDBC transaction components, do the following:
- Copy sqljdbc_xa.dll from the JDBC installation directory to the bin
directory of every SQL Server computer that will participate in
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.
- 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.
- 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
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
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
You can read the uncommitted values from the Accounts table using the
select * from administrator.accounts with ur
Figure 9. Read the uncommitted values from DB2
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
Figure 10. JDBC provider for DB2
Figure 11 shows the authentication alias used to access the Bank2 database
on DB2, in our example we use
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
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
- Provider: the selected JDBC provider for DB2; for
DB2 using IBM JCC Driver (XA)you defined earlier
- JNDI name:
- 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)
- 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:
- Component-managed authentication alias:
- Driver Type: 4
- Database name:
- Server name: the server name; for example
- Port number: the port number for the database DB2;
Figure 13. 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
In summary, to access the DB2 Bank2 database on machine B, you defined a
data source named
bank2db2 (jdbc name:
jdbc/bank2db2) and a
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).
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.
|Process application for IBM Process Designer)||KWSBPM.twx||1.3MB|
|AIS (for IBM Integration Designer)||KWSBPM_Main.zip||276KB|
|.NET web service (for Visual Studio)||WSChargeBank1Soln.zip||47KB|
|JAX-WS web service (for Integration Designer)||WSCreditBank2Project.zip||28KB|
- Developing a transactional Advanced Integration Service with IBM Business Process Manager (4-part developerWorks series)
- The benefits of using IBM Business Process Manager Advanced – SOA, process integration, tools, and more (developerWorks article)
- Exploring WebSphere Process Server transactionality (developerWorks article)
- Configuring error handling for Advanced Integration Services in IBM Business Process Manager Advanced V8 (developerWorks article)
- Linking business processes and enterprise services together using IBM Business Process Manager Advanced (developerWorks BPM Journal article)
- WebSphere Application Server V7: Accessing Databases from WebSphere (IBM Redpaper)
- WebSphere Application Server V8: Administration and Configuration Guide (IBM Redbook)
- IBM BPM V8.0.1 Information Center
- Transactions in WebSphere Process Server (IBM webcast replay)
- Advanced Integration Service - Call a BPEL process from a BPD (Advanced Hiring Sample) (BPM Sample Exchange; requires an IBM BPM Community account)
- Building transactional Web services with WebSphere Application Server and Microsoft .NET using WS-AtomicTransaction (developerWorks article)
- Microsoft .NET WCF interoperability with a WebSphere ESB service gateway using WS-Atomic transactions (developerWorks article)
- Ensuring transactional integrity using Web Services Atomic Transaction support in WebSphere ESB and WebSphere Application Server (developerWorks article)
- Making web services enterprise-ready - Using the WS-Atomic Transaction protocol and WebSphere Application Server (developerWorks WebSphere Developer Technical Journal article)
- Web Services Atomic transaction (WS-AT) on IBM WebSphere Application Server 8.5 (WebSphere Application Server V8.5 Information Center)
- Tour Web Services Atomic Transaction operations (developerWorks article)
- developerWorks BPM zone: Get the latest technical resources for IBM BPM solutions, including downloads, demos, articles, tutorials, events, webcasts, and more.
- IBM BPM Journal: Get the latest articles and columns on BPM solutions in this quarterly journal.
- Microsoft SQL Server 2008 R2 – Understanding XA Transactions (Microsoft MSDN Library)
- Configuring WS-Atomic Transaction Support in Windows 7 64-bit (Blog entry)
- C#.Net How To: Create a WCF Web Service in Visual Studio 2010 – Create a WCF WebService in C#.Net (Blog entry)
- Configuring WS-Atomic Transaction Support (Microsoft MSDN Library)
Dig deeper into Business process management on developerWorks
Get samples, articles, product docs, and community resources to help build, deploy, and manage your cloud apps.
Experiment with new directions in software development.
Software development in the cloud. Register today to create a project.
Evaluate IBM software and solutions, and transform challenges into opportunities.