Developing a transactional Advanced Integration Service with IBM Business Process Manager, Part 1: Introduction and setting up the databases

Leveraging the capabilities of the Advanced Integration Services (AIS) in IBM Business Process Manager V8 Advanced, this series describes how to implement a distributed ACID transactional scenario in IBM Business Process Manager that includes two databases on different machines. The scenario shows the automatic rollback capabilities offered by the SCA-based management in an AIS.

Share:

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.



06 March 2013

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 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.


Series overview

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:

  1. Create the databases and configure the data sources (Part 1)
  2. Define the BPMN process in Process Designer (Part 2)
  3. 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)
  4. 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 (8.0.0.0) Advanced
    • WebSphere Application Server Network Deployment 8.0.0.3
    • IBM Integration Designer 8.0.0.0
    • IBM Process Designer 8.0.0.0
  • On the DB2 Server, a Bank2 database

The scenario was tested on BPM V8.0.0.0 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 V8.0.0.5. 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.


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 (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
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
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:

  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 we'll see later, the AIS is implemented by a BPEL microflow that invokes a couple of SCA components).
  3. 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 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.


Getting started

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.

  1. 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.
  2. 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.
  3. 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 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
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 with (readuncommitted) SQL 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
Reading 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 (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:

  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 MS DTC 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 MS DTC changes.

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

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 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 processor
Reading the Accounts data from DB2 using the Command Line processor

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 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
JDBC provider for SQL Server

The most important points are the Class path, the Native library path and the Implementation class name (com.microsoft.sqlserver.jdbc.SQLServerXADataSource, in this case). Note also the name of the driver JAR file, in this case sqljdbc4.jar.

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 Environment/WebSphere variable.

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
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 cells:IBMBPMNode01Cell:nodes:IBMBPMNode01
  • Provider: the selected JDBC Type 4 provider for SQL Server you created earlier
  • Name: bank1sqlserver
  • JNDI name: jdbc/bank1sqlserver
  • 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)
    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, specify com.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper
  • Component-managed authentication alias: IBMBPMNode01/sa
  • Server name: the server name and the server instance name of SQL Server, for example ADMINIB-PHG662G\sqlexpress
  • Database name: Bank1
  • Port number: 1433
    Figure 13. JDBC Data source to access SQL Server (part 2 of 2)
    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 alias sa.

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 com.ibm.db2.jcc.DB2XADataSource.

Figure 14. JDBC provider for DB2
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 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 15. Authentication alias to access DB2
Figure 15. 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 16 and 17).

  • 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 16. 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 17. 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 17, 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 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.


Acknowledgements

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.


Downloads

DescriptionNameSize
Process application for Process DesignerKBPM.twx873KB
AIS for Integration DesignerKBPM-Main.zip42KB

Resources

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=859224
ArticleTitle=Developing a transactional Advanced Integration Service with IBM Business Process Manager, Part 1: Introduction and setting up the databases
publish-date=03062013