1303 randone-pdf 2

Copyright IBM Corporation 2013 TrademarksDeveloping a transactional Advanced Integration Service withIBM Business Process Manager, Part 1: Introduction and settingup the databases

Page 1 of 27

Developing a transactional Advanced IntegrationService with IBM Business Process Manager, Part 1:Introduction and setting up the databasesCarlo Randone ([email protected])Certified IT ArchitectIBM

06 March 2013

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

View more content in this series

Introduction

According to the IBM developerWorks article The benefits of using IBM Business ProcessManager Advanced, IBM Business Process Manager Standard takes a fairly optimistic view of theprocessing environment and therefore does not provide significant out-of-the-box capabilities foraddressing database transactional issues when they occur. Fortunately, however, these types ofadvanced capabilities are addressed in the IBM Business Process Manager Advanced (hereafterIBM BPM) environment, which provides many connectivity capabilities and adopts a rather morepessimistic view to complement the more optimistic view of IBM Business Process ManagerStandard. This series shows how IBM BPM Advanced, leveraging an Advanced IntegrationServices IAIS) implementation, can manage an ACID distributed transaction between a couple ofdifferent database engines (Microsoft SQL Server and IBM DB2), and shows the automatic commitand rollback capabilities of the SCA-based implementation in BPM Advanced. You can implementthis 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 codeimplementation.

An AIS is a collaboration between a business user working with IBM Process Designer and anintegration developer working with IBM Integration Designer. An AIS, like the TxAIS presented inthis article, can be used by a process developer without the need to know a lot of technical detail

developerWorks ibm.com/developerWorks/

Developing a transactional Advanced Integration Service withIBM Business Process Manager, Part 1: Introduction and settingup the databases

Page 2 of 27

about the implementation of the technical service invoked, which means that process developerdoesn'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 knowis 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 IntegrationService (AIS). An AIS is used to call a service implemented in IBM Integration Designer from aBPM business process definition (BPD). This kind of integration pattern is described in the IBMdeveloperWorks article: Linking business processes and enterprise services together using IBMBusiness Process Manager Advanced.

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

It's important to note that in IBM Business Process Manager Advanced, process navigation ofbusiness process definitions (BPDs) does not participate in the same transaction context as anAIS. Therefore, a runtime failure in the BPD navigation that causes a BPD transaction to roll backdoes not roll back the transaction under which the currently executing AIS might be running. As aresult, the AIS might be executed a second time.

Series overviewThis four-part series covers the following major tasks to implement the distributed transactionscenario, 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 throughpreliminary set-up, as well as creating and configuring the databases.

Scenario implementation detailsThe scenario described in this series can be applied in many different cases and contexts, but theimplementation 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

ibm.com/developerWorks/ developerWorks


Page 3 of 27

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 IntegrationDesigner V8.0.1, Process Designer V8.0.1 and WebSphere Application Server NetworkDeployment V8.0.0.5. In that case, the KBPM process application, after the import into ProcessDesigner, required an upgrade of the compatibility level of the Data and Coaches system toolkits toV8.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 distributedacross a network, requires a dedicated component to manage the complexity of coordinatingtransactions. A transaction manager works with applications and application servers to provideservices to control the scope and duration of transactions. A transaction manager also helpscoordinate the completion of global transactions across multiple transactional resource managers(for example, database management systems, as in the sample presented in this article), providessupport for transaction synchronization and recovery, and may provide the ability to communicatewith other transaction manager instances.

Global transactions span multiple resource managers. To coordinate global transactions, thecoordinating transaction manager and all participating resource managers should implement amulti-phased completion protocol, such as the two-phase commit (2PC) protocol (see Figures 1and 2). Although there are several proprietary implementations of this protocol, X/Open XA is theindustry 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 prepareall the transaction's participating processes (usually named participants, cohorts, or workers) totake 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 aproblem has been detected with the local portion).



Page 4 of 27

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, thecoordinator 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 neededactions (commit or abort) with their local transactional resources (also called recoverableresources; for example, database data) and their respective portions in the transaction's otheroutput, 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 ofthem 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 ofthe underlying IBM WebSphere Application Server.



Page 5 of 27

Based on this conceptual and theoretical premise, the transactional scenario described in thisarticle is organized as follows. The scenario has a couple of different databases, Bank1 andBank2, based on two different database engines on two different machines. The Bank1 databaseis a SQL Server 2008 (Express) database on Windows machine A, and the database Bank2 isDB2 UDB (V 9.7) database on a different Windows machine B. On this second machine, thereis also a BPM V8 Advanced with Process Designer and Integration Designer. It's possible toimplement this demonstration using two databases (also based on the same engine) on thesame machine, or the BPM engine (with Process Center and so on) can be installed on a thirdmachine, separate from the machines that the two database engines are installed on. The focusof this article is to demonstrate a scenario similar to a real context, in which frequently the datasources involved are based on different database engines, installed on different physical (orvirtual) machines.

In both databases there is an Accounts table with account names (in the Description field) andamounts (in the Amount field). In IBM BPM there is a process application named KBPM ("K" is theglobal prefix adopted in this example) that contains a BPD named KBPM Process1. The aim ofthis simple process is as follows:

1. Ask the user (by a human service with a related Coach-based presentation) the parametersfor the requested transaction (for example, "Transfer an amount of 10 value units from theKarl 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'llsee later, the AIS is implemented by a BPEL microflow that invokes a couple of SCAcomponents).

3. Using Coaches, show the positive execution of the transaction, or show the failure ofthe transaction, along with some details about the reason for the failure (to explain theconsequent rollback of the transaction to the user).

Using different values for the parameters in #1 above, we can test some different executionscenarios, such as:

A couple of existing accounts on Bank1 and Bank2, that do not exceed the availability offunds 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 anerror and a transaction rollback

The attempt to transfer an amount greater than that available on the charge account, thatcauses 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 chosethis 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 themanagement of the automatic transaction rollback provided by the SCA-based BPEL microflowadopted to implement the IBM BPM AIS. The scenario implemented in the AIS, as shown in Figure3, is as follows:



Page 6 of 27

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 theBPM 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-levelprocess to a data layer (data resource tier).

Because the AIS is implemented on the IBM BPM Advanced server, the transaction manager inthis 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 debitJava-based SCA component's interfaces. In other words, WebSphere Application Server is thetransaction 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 DesignerV8 and IBM Integration Designer V8, installed (I tested all on a Windows Server 2008 R2 64-bitmachine). You should be familiar with the IBM Process Designer and IBM Integration Designerauthoring tools and have an understanding of AIS and SCA-based implementations.



Page 7 of 27

1. Download and import the following two files provided with this article, and refer to them as youfollow the steps to implement the sample scenario in this article:

KBPM.twx is a complete KBPM process application for BPM Advanced, that you canimport into Process Designer.

KBPM Main.zip is a project interchange file for the KBPM Advanced Integration Serviceimplementation, that you can download and import into Integration Designer. This AISimplements the true transactional activity of the solution. In the demo I've defined theIntegration Designer workspace in C:\workspaces\KBPMWks on the BPM server.

2. Associate the SCA implementation with the process application. To do this, open the ProcessCenter perspective in Integration Designer and click Open in workspace beside the processapplication you want to associate with the SCA service. For this article, that is the KBPMProcess App.

3. To enable the execution of the transactional AIS, create a couple of databases (a Bank1 SQLServer database on machine A and a Bank2 DB2 database on machine B), and configure therelated data sources on WebSphere Application Server (on BPM machine B), as described inthe following sections.

Create the databases and configure the data sourcesOur scenario involves a distributed transaction: a simple money transfer between two bankaccounts, hosted by different banks, and so also on different databases. This is quite a commonscenario.

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 itwill contain another Accounts table, as shown here:

Accounts table for Bank1 database on SQL Server on machine AID Description Amount1 Karl 1002 Albert 2003 Ricky 300

Accounts table for Bank2 database on DB2 on machine BID Description Amount1 Donald 10002 Mickey 20003 Minnie 3000

These values will be adopted in the use cases described in this series, so you can insert this kindof information in the two databases after the creation of databases, tables, constraints and triggersas described in the following sections.

The Accounts tables have a primary key on the ID field (an Identity field) and a UNIQUE constrainton 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 havenegative amounts in the tables: any INSERT or UPDATE operation that makes negative the



Page 8 of 27

remaining amount of an account will be aborted, rolling back the implicit (local) transaction thatwraps the INSERT or UPDATE operation.

Create the Microsoft SQL Server databaseAs 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 engineis configured for the mixed authentication mode, and we can connect to the database withan explicit SQL Server login (for example, the administrative account "sa" or another accountwith the necessary permissions). As you can see in Figure 4, the SQL Server and WindowsAuthentication mode is selected (that is, the mixed authentication mode). To set SQL Server2008 Express to this security mode, follow the procedure described in How to: Change ServerAuthentication Mode.

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

Figure 4. The SQL Server mixed authentication mode



Page 9 of 27

The name of the SQL Server database is Bank1. In this database there is a table called Accountswith 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 triggerAccounts_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 Bank1GO

-- Create the table Bank1.Accounts, with its trigger and its dataUSE Bank1GO

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 amountIF (SELECT COUNT(*) FROM inserted) = 1 BEGIN SELECT @remaining = Amount FROM inserted IF @remaining < 0 BEGIN RAISERROR('Insufficient money.', 11, 1) ROLLBACK TRANSACTION END ENDGO

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 theupdate 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 than11, otherwise the custom error message will not be passed to the Java client code by the JDBC(Java Database Connectivity) Driver.



Page 10 of 27

To read the values from the table you can issue the following command using SQL ServerManagement 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 orrollback), 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

The readuncommitted table hint specifies that "dirty reads" are allowed. No shared locks are issuedto prevent other transactions from modifying data read by the current transaction, and exclusivelocks 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 modificationsthat are then rolled back by other transactions. Note that this may present users with data that wasnever committed, or cause users to see records twice (or not at all). Later you'll see an equivalentoption 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 MicrosoftSQL Server 2008 Management Studio Express here.



Page 11 of 27

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

It's important to consider the fact that a default installation of SQL Server supports XA-basedtransactions managed by the Microsoft Distributed Transaction Coordinator (MS DTC), butadditional configuration is required to enable the XA transactional support when the SQL Serverengine is accessed by a JDBC driver, as in our example. This enablement is described inMicrosoft SQL Server 2008 Understanding XA Transactions and other sources, such as the IBM WebSphere Enterprise Service Bus Information Center. The JDBC distributed transactioncomponents are included in the xa directory of the Microsoft JDBC driver for the SQL Serverinstallation. These components include the xa_install.sql and sqljdbc_xa.dll files. Complete thefollowing steps to do this one-time configuration on the SQL Server installation to enable the XAtransactional 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 isrunning when the SQL Server service is started. To enable MS DTC for XA transactions, do thefollowing:

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 SQLServer 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 inthe x86 folder, even if the SQL Server is installed on an x64 processor. If you are using XAtransactions with a 64-bit SQL Server on the x64 processor, use the sqljdbc_xa.dll file in thex64 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 participatein distributed transactions. This script installs the extended stored procedures that are calledby sqljdbc_xa.dll. These extended stored procedures implement distributed transaction andXA support for the Microsoft SQL Server JDBC Driver. You'll need to run this script as anadministrator of the SQL Server instance.



Page 12 of 27

3. To grant permissions to a specific user to participate in distributed transactions with the JDBCdriver, add the user to the SqlJDBCXAUser role.

Create the IBM DB2 databaseMachine B, which is running DB2 V9.7, has a Bank2 database with an Accounts table and acouple 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 relatedconstraints and triggers. For simplicity, the schema name used here is ADMINISTRATOR, but you canmake a different choice. The first part of Listing 2 is an example script for the initial creation of anempty 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 CODESETIBM-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



Page 13 of 27

-------------------------------

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 MODEDB2SQLBEGIN ATOMICDECLARE remaining INT;--SET remaining = new_row.Amount;--IF remaining < 0 THENSIGNAL 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_TABLEAS new_table FOR EACH ROW MODE DB2SQLBEGIN ATOMICDECLARE remaining INT;--SET remaining = new_row.Amount;--IF remaining < 0 THENSIGNAL 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 Insufficientmoney.

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.



Page 14 of 27

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, asshown in Figure 8:

connect to BANK2 SELECT * FROM ADMINISTRATOR.ACCOUNTS

Figure 8. Reading the Accounts data from DB2 using the Command Lineprocessor

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



Page 15 of 27

Figure 9. 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 toconfigure 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 aspecific JDBC driver. You can use an existing SQL Server JDBC Driver already available in yourinstallation or the type 4 JDBC driver for SQL Server available from Microsoft. Figure 10 shows apossible configuration for a JDBC provider for SQL Server in the WebSphere Application Serveradministrative console. Refer to "9.5 Example: Connecting to an SQL Server database" in the IBMRedbook WebSphere Application Server V8: Administration and Configuration Guide for moredetail about this kind of configuration.



Page 16 of 27

Figure 10. JDBC provider for SQL Server

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

The environment variable CONNECTJDBC_JDBC_DRIVER_PATH must be set to the path of the SQLJDBC driver (for example C:\IBM\WebSphere\AppServer\jdbcdrivers\SQLServer, where the SQLServer driver file sqljdbc4.jar resides). You can perform this configuration in in the WebSphereApplication 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 driveravailable 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 ApplicationDeveloper 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 authenticationalias for the administrative (sa) account of SQL Server, as shown in Figure 11. You can perform



Page 17 of 27

this configuration in the WebSphere administrative console under Security/Global Security/JavaAuthentication => 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. Thename selected for this data source is jdbc/bank1sqlserver. Configure the jdbc/bank1sqlserver datasource as follows (refer to Figures 12 and 13):

Scope: the scope on which your provider and data source are defined; for examplecells: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



Page 18 of 27

Figure 12. JDBC Data source to access SQL Server (part 1 of 2)

Select Specify a user-defined data store helper and for Entera package-qualified data store helper class name, specifycom.ibm.websphere.rsadapter.ConnectJDBCDataStoreHelper

Component-managed authentication alias: IBMBPMNode01/sa Server name: the server name and the server instance name of SQL Server, for exampleADMINIB-PHG662G\sqlexpress

Database name: Bank1 Port number: 1433



Page 19 of 27

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 ServerExpress is always named as \sqlexpress).

In summary, to access the SQL Server Bank1 on the machine A, you've defined a JDBC providerfor SQL Server, a data source named bank1sqlserver (jdbc name: jdbc/bank1sqlserver) and anauthentication 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 IBMJCC 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 iscom.ibm.db2.jcc.DB2XADataSource.



Page 20 of 27

Figure 14. JDBC provider for DB2

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



Page 21 of 27

Figure 15. Authentication alias to access DB2

Configure the jdbc/bank2db2 data source used to access the Bank2 database on machine B (theIBM 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 examplecells: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



Page 22 of 27

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



Page 23 of 27

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

In summary, to access the DB2 Bank2 database on machine B, you defined a data source namedbank2db2 (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 transactionscenario we will build in this series. You've learned how to create the databases on both the SQLServer and the DB2 Server, and how to configure the data sources on the IBM BPM applicationserver. In Part 2, you'll learn how to define and implement the business process in IBM BusinessProcess Manager Advanced.



Page 24 of 27

Acknowledgements

The author would like to thank his colleagues Stefano Angrisano, Marco Antonioni, GiuseppeBottura, 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 theirreviews and contributions to this article.



Page 25 of 27

Downloads

Description Name SizeProcess application for Process Designer KBPM.twx 873KB

AIS for Integration Designer KBPM-Main.zip 42KB



Page 26 of 27

Resources

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.



Page 27 of 27

About the author

Carlo Randone

Carlo Randone is a Certified IBM IT Architect and Open Group Master CertifiedIT Architect in IBM Global Business Services, Italy. Carlo has a deep knowledge ofdifferent development platforms and middleware on heterogeneous environments andoperating systems. He worked for several years as a Certified Trainer and SolutionDeveloper 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, andEnterprise Architecture planning and design. He enjoys collecting documentation andhardware pieces related to the historical development of IT, and to support this hobbyhe is a member of the Charles Babbage Institute.

Copyright IBM Corporation 2013(www.ibm.com/legal/copytrade.shtml)Trademarks(www.ibm.com/developerworks/ibm/trademarks/)

Table of ContentsIntroductionSeries overviewScenario implementation detailsScenario overviewGetting startedCreate the databases and configure the data sourcesCreate the Microsoft SQL Server databaseCreate the IBM DB2 databaseConfigure the data sources on WebSphere Application Server

ConclusionAcknowledgementsDownloadsResourcesAbout the authorTrademarks

1303 randone-pdf 2

Documents

ibm process designer

ibm bpm advanced

ibm db2

ibm integration designer

process developerdoesnt

ibm developerworks article

business user

business processes