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 IAIS) implementation, can manage an ACID distributed transaction between a couple of different database engines (Microsoft SQL Server and IBM DB2), and shows the automatic commit and rollback capabilities of the SCA-based implementation in BPM Advanced. You can implement this type of management by configuring interfaces of the components involved in the transaction (the Charge and Credit components in the example), without having to do any special custom code implementation.
An AIS is a collaboration between a business user working with IBM Process Designer and an integration developer working with IBM Integration Designer. An AIS, like the TxAIS presented in this article, can be used by a process developer without the need to know a lot of technical detail about the implementation of the technical service invoked, which means that process developer doesn't need to know a lot of Service Component Architecture (SCA), Java™ programming, transactional qualifiers, JDBC, and so on. The only thing that the process developer needs to know is the I/O interface of the AIS and a bit of functional specification about its behavior.
The core of the implementation presented in this series is a transactional Advanced Integration Service (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.
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.
It's important to note that in IBM Business Process Manager Advanced, process navigation of business process definitions (BPDs) does not participate in the same transaction context as an AIS. Therefore, a runtime failure in the BPD navigation that causes a BPD transaction to roll back does not roll back the transaction under which the currently executing AIS might be running. As a result, the AIS might be executed a second time.
This four-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)
- Define the BPMN process in Process Designer (Part 2)
- Implement the AIS in Integration Designer, based on an SCA BPEL microflow with a couple of Java-based SCA components (for the Credit and Debit operations, respectively) (Part 3)
- Review execution cases (Part 4)
The remainder of this article provides an overview of the scenario, and walks you through preliminary set-up, as well as creating and configuring the databases.
Scenario implementation details
The scenario described in this series can be applied in many different cases and contexts, but the implementation described here was implemented on the following products and versions:
Machine A is a Windows 7 computer named ADMINIB-PHG662G with the following:
- 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
Machine B is a Windows Server 2008 server named IBMBPM with the following:
- 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)
- BPM: IBM Business Process Manager V8 (18.104.22.168) Advanced
- WebSphere Application Server Network Deployment 22.214.171.124
- IBM Integration Designer 126.96.36.199
- IBM Process Designer 188.8.131.52
- On the DB2 Server, a Bank2 database
The scenario was tested on BPM V184.108.40.206 Advanced, but also on BPM V8.0.1, with Integration Designer V8.0.1, Process Designer V8.0.1 and WebSphere Application Server Network Deployment V220.127.116.11. In that case, the KBPM process application, after the import into Process Designer, required an upgrade of the compatibility level of the Data and Coaches system toolkits to V8.0.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 (see Figures 1 and 2). Although there are several proprietary implementations of this protocol, X/Open XA is the industry standard.
Two distinct phases ensure that either all the participants commit or all of them roll back changes. During the first, or prepare phase, as shown in Figure 1, the global coordinator attempts to prepare all the transaction's participating processes (usually named participants, cohorts, or workers) to take the necessary steps for either committing or aborting the transaction and to vote, either Yes/Commit (if the transaction participant's local portion execution has ended properly) or No/Abort (if a problem has been detected with the local portion).
Figure 1. Two-phase commit and global transaction – Phase 1
In the second, or commit phase, shown in Figure 2, based on voting of the participants, the coordinator decides whether to commit (only if all have voted Yes) or abort the transaction (otherwise), and notifies all the cohorts of the result. The cohorts then follow with the needed actions (commit or abort) with their local transactional resources (also called recoverable resources; for example, database data) and their respective portions in the transaction's other output, if applicable.
Figure 2. Two-phase commit and global transaction – Phase 2
The two-phase commit protocol ensures that either all participants commit changes or none of them do.
The implementation of the two-phase commit ACID (Atomic, Consistent, Isolation, and Durable) protocol is supported in the SCA implementation in BPM 8 Advanced, leveraging the capabilities of the underlying IBM WebSphere Application Server.
Based on this conceptual and theoretical premise, the transactional scenario described in this article is organized as follows. The scenario has a couple of different databases, Bank1 and Bank2, based on two different database engines on two different machines. The Bank1 database is a SQL Server 2008 (Express) database on Windows machine A, and the database Bank2 is DB2 UDB (V 9.7) database on a different Windows machine B. On this second machine, there is also a BPM V8 Advanced with Process Designer and Integration Designer. 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 and so on) can be installed on a third machine, separate from the machines that the two database engines are installed on. The focus of this article is to demonstrate a scenario similar to a real context, in which frequently the data sources involved are based on different database engines, installed on different physical (or virtual) machines.
In 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 KBPM ("K" is the global prefix adopted in this example) that contains a BPD named KBPM – Process1. The aim of this simple process is as follows:
- 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 we'll see later, the AIS is implemented by a BPEL microflow that invokes a couple of SCA components).
- Using Coaches, show the positive execution of the transaction, or show the failure of the transaction, along 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, we can test some different execution scenarios, such as:
- A couple of existing accounts on Bank1 and Bank2, that do not exceed the availability of funds on the "charge" (debit) account, and a positive transaction commit as the outcome
- The non-existence of the source (to charge) or of the target (to credit) account, that causes an error and a transaction rollback
- The attempt to transfer an amount greater than that available on the charge account, that causes an error and a transaction rollback
These execution scenarios will be covered in detail in Part 4 of this series.
In the BPEL microflow, the credit operation is executed before the charge operation. We chose this in order to make the rollback of the credit operation after a charge error (for insufficient funds, for example) on the debit account evident and explicit. This kind of execution flow emphasizes the management of the automatic transaction rollback provided by the SCA-based BPEL microflow adopted to implement the IBM BPM AIS. The scenario implemented in the AIS, as shown in Figure 3, is as follows:
- Time t1: Credit on DB2 (Bank2)
- Time t2: Debit on SQL Server (Bank 1)
Figure 3 shows the transactional scenario we'll implement in this series.
Figure 3. The transactional scenario
The scenario can be reversed by simply passing different parameters to the initial Coach in the BPM process, so that it's also possible to transfer money from Bank2 (on DB2) to the SQL Server (on Bank1).
One interesting aspect of this solution is the significant level of layering, from a business-level process to a data layer (data resource tier).
Because the AIS is implemented on the IBM BPM Advanced server, the transaction manager in this scenario is WebSphere Application Server, and the transactional attributes (as you'll see later) are configured on the SCA assembly diagram of the BPEL microflow and related credit or debit Java-based SCA component's interfaces. In other words, WebSphere Application Server is the transaction coordinator, and both SQL Server and DB2 are transactional resources.
To better understand the solution components and to complete the steps described in this article, you must have IBM Business Process Manager Advanced V8, including IBM Process Designer V8 and IBM Integration Designer V8, installed (I tested all on a Windows Server 2008 R2 64-bit machine). You should be familiar with the IBM Process Designer and IBM Integration Designer authoring tools and have an understanding of AIS and SCA-based implementations.
- Download and import the following two files
provided with this article, and refer to them as you follow
the steps to implement the sample scenario in this article:
- KBPM.twx is a complete KBPM process application for BPM Advanced, that you can import into Process Designer.
- KBPM Main.zip is a project interchange file for the KBPM Advanced Integration Service implementation, that you can download and import into Integration Designer. This AIS implements the true transactional activity of the solution. In the demo I've defined the Integration Designer workspace in C:\workspaces\KBPMWks on the BPM server.
- Associate the SCA implementation with the process application. To do this, open the Process Center perspective in Integration Designer and click Open in workspace beside the process application you want to associate with the SCA service. For this article, that is the KBPM Process App.
- To enable the execution of the transactional AIS, create a couple of databases (a Bank1 SQL Server database on machine A and a Bank2 DB2 database on machine B), and configure the related data sources on WebSphere Application Server (on BPM machine B), as described in the following sections.
Create the databases and configure the data sources
Our scenario involves a distributed transaction: a simple money transfer between two bank accounts, hosted by different banks, and so 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, named Bank1, will be on the SQL Server on machine A, and it will contain an Accounts table, which holds Bank1's accounts. The second database will be Bank2 on DB2 on machine B, and it will contain another Accounts table, as shown here:
Accounts table for Bank1 database on SQL Server on machine A
ID Description Amount 1 Karl 100 2 Albert 200 3 Ricky 300
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 kind of information in the two databases after the creation of 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.
Create the Microsoft SQL Server database
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.
Figure 4. 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 populate the table with some sample data.
Listing 1. 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.
Note that in the trigger, the specified severity in RAISERROR needs to be equal to or greater than 11, otherwise the custom error message will not be passed to the Java client code by the JDBC (Java Database Connectivity) Driver.
To read the values from the table you can issue the following command using
SQL Server Management Studio, as as shown in Figure 5:
select * from dbo.Accounts
Figure 5. Reading 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
Server table hint as follows:
select * from dbo.Accounts with (readuncommitted)
Figure 6 shows this kind of query in SQL Server Management Studio.
Figure 6. Reading 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 (MS DTC), 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).
Enable the MS DTC service for XA transactions
The MS DTC service must 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, 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 MS DTC 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 MS DTC changes.
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
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 2. 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
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 processor
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 data sources on WebSphere Application Server
To enable the access to the Bank1 and Bank2 databases by IBM BPM Advanced, you need to configure a couple of data sources on WebSphere Application Server: the SQL Server data source (for Bank1 on Machine A) and the DB2 data source (for Bank2 on machine B).
Configure the SQL Server data source for Bank1 on machine A
To connect a Microsoft SQL Server database from a BPM installation, you need to use a specific JDBC driver. You can use an existing SQL Server JDBC Driver already available in your installation or the type 4 JDBC driver for SQL Server available from Microsoft. Figure 10 shows a possible configuration for a JDBC provider for SQL Server in the WebSphere Application Server administrative console. Refer to "9.5 Example: Connecting to an SQL Server database" in the IBM Redbook WebSphere Application Server V8: Administration and Configuration Guide for more detail about this kind of configuration.
Figure 10. JDBC provider for SQL Server
The most important points are the Class path, the Native library path and
the Implementation class name
in this case). Note also the name of the driver JAR file, in this case
The environment variable
CONNECTJDBC_JDBC_DRIVER_PATH must be set to the
path of the SQL JDBC driver (for example
C:\IBM\WebSphere\AppServer\jdbcdrivers\SQLServer, where the SQL Server
driver file sqljdbc4.jar resides). You can perform this configuration in
in the WebSphere Application Server administrative console using the
Ensure that the adopted driver is XA-enabled or XA-capable. In this example, I selected the driver available on the jdbcdrivers\SQLServer folder of the WebSphere installation.
For a complete example of WebSphere configuration of the SQL Server Microsoft JDBC Driver, refer to Data Source And Resource Reference Settings In WebSphere 7.0, Rational Application Developer For WebSphere Ver 8 With Java 6 And Microsoft Sql Server 2008.
You also need an authentication alias. For simplicity, in this example, we defined an authentication alias for the administrative (sa) account of SQL Server, as shown in Figure 11. You can perform this configuration in the WebSphere administrative console under Security/Global Security/Java Authentication => Authorization Service/J2C authentication data.
Figure 11. Configure authentication alias to access SQL Server
Now that you've defined a JDBC provider for SQL Server and an available authentication alias, you can create a data source to access the Bank1 database on SQL Server on machine A. The name selected for this data source is jdbc/bank1sqlserver. Configure the jdbc/bank1sqlserver data source 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 Type 4 provider for SQL Server you created earlier
- JNDI name:
- Check Use this data source in container managed persistence (CMP)
- Description: Any descriptive text
Figure 12. JDBC Data source to access SQL Server (part 1 of 2)
- Select Specify a user-defined data store helper and for
Enter a package-qualified data store helper class name,
- Component-managed authentication alias:
- Server name: the server name and the server instance name of
SQL Server, for example
- Database name:
- Port number:
Figure 13. JDBC Data source to access SQL Server (part 2 of 2)
In the above figures, machine A is ADMINIB-PHG662G\sqlexpress (the instance of a SQL Server Express is always named as <servername>\sqlexpress).
In summary, to access the SQL Server Bank1 on the machine A, you've defined
a JDBC provider for SQL Server, a data source named
bank1sqlserver (jdbc name:
jdbc/bank1sqlserver) and an authentication
Configure the DB2 data source for Bank2 on machine B)
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), shown in Figure 14, is already
defined on WebSphere Application Server. This driver supports XA
transactions, and the implementation class name for the driver is
Figure 14. JDBC provider for DB2
Figure 15 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
ADMINISTRATOR, you can use the
administrative account to define the authentication alias, or another
bpmadmin) that must be enabled
with the correct authorization on the Bank2 database.
Figure 15. 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 16 and 17).
- Scope: the scope on which your provider and data source are
defined; for example
- Provider: the selected JDBC provider for DB2; for example
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 16. 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; for example
Figure 17. JDBC Data source to access DB2 (part 2 of 2)
In the configuration shown in Figure 17, 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
bpmadmin authentication alias.
In Part 1 of this series, we covered the overview and 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 define and implement the business process in IBM Business Process Manager Advanced.
The author would like to thank his colleagues Stefano Angrisano, Marco Antonioni, Giuseppe Bottura, Claudio Cantoni, Matteo Franciolli and Daniele Rossi, and his good friend Alberto Venditti (author, about eight years ago, of a similar demo on a different technological platform) for their reviews and contributions to this article.
|Process application for Process Designer||KBPM.twx||873KB|
|AIS for Integration Designer||KBPM-Main.zip||42KB|
- The benefits of using IBM Business Process Manager Advanced – SOA, process integration, tools, and more, developerWorks, 2012
- Exploring WebSphere Process Server transactionality, developerWorks, 2009
- BPEL fault handling in WebSphere Integration Developer and WebSphere Process Server, developerWorks, 2007
- Configuring error handling for Advanced Integration Services in IBM Business Process Manager Advanced V8, developerworks, 2012
- Linking business processes and enterprise services together using IBM Business Process Manager Advanced, developerWorks, 2012
- WebSphere Application Server V7: Accessing Databases from WebSphere, IBM Redpaper
- WebSphere Application Server V8: Administration and Configuration Guide, IBM Redbook
- IBM BPM V8 Information Center
- IBM BPM V8.0.1 Information Center
- IBM webcast replay: Transactions in WebSphere Process Server
- Sample Exchange: Advanced Integration Service - Call a BPEL process from a BPD (Advanced Hiring Sample) (requires an IBM BPM Community account)
- Microsoft SQL Server 2008 R2 – Understanding XA Transactions
- Service Runtime Exception handling in the IBM WebSphere Process Server V7 Information Center
- Application design considerations for exceptions and faults in the IBM WebSphere Process Server V7 Information Center
- IBM WebSphere Process Server Best Practices in Error Prevention Strategies and Solution Recovery, IBM Redpaper
- developerWorks BPM zone: Get the latest technical resources on 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, also available in both Kindle and PDF versions.