Developing a transactional Advanced Integration Service with IBM Business Process Manager, Part 4: Execution cases

In the final article in this series, you'll learn how to execute and test different transactional scenarios with the components designed and developed in the previous parts of this series, and will learn about the ACID (Atomicity, Consistency, Isolation, Durability) characteristics of the implemented solution.

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.



03 April 2013

Introduction

The final part of this series presents some interesting execution cases. In a real-world situation these kind of scenarios could become the basis to develop some practical test cases for the transactional solution.


Case 1: All okay

In this scenario, funds are transferred from an account on Bank1 (on SQL Server) to an account on Bank2 (on DB2), with existing accounts and without trigger-based exceptions (the requested amount to transfer is available on Bank1). The outcome in this scenario is a transaction commit. We'll call this case as an "all okay" case.

Following are example input values for this case, as shown in Figure 1:

  • Data source for Bank1 (to Charge) (-): jdbc/bank1sqlserver
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Karl
  • Data source for Bank2 (to Credit) (+): jdbc/bank2db2
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Donald
  • Money to transfer: 10
Figure 1. Inputs for the "all okay" case
Inputs for the standard all okay case

And Figure 2 shows the related "all okay" message in the Coach.

Figure 2. Output from the "all okay" case
Output from the standard (all okay) case

As you can see, the final output message is the concatenation of the (positive) output message from the k2Credit component with the (positive) output message from the k1Charge component.


Case 2: Insufficient money

In this scenario, funds are transferred from an account on Bank1 (on SQL Server) to an account on Bank2 (on DB2), with existing accounts and with a database trigger-based exception due to insufficient money on the charge account (on Bank1). The outcome of this scenario is a transaction rollback.

Following are example input values for this case, as shown in Figure 3:

  • Data source for Bank1 (to Charge) (-): jdbc/bank1sqlserver
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Karl
  • Data source for Bank2 (to Credit) (+): jdbc/bank2db2
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Donald
  • Money to transfer: 200
Figure 3. Parameter values to create an insufficient money case
Parameter values to create an insufficient money case

Figure 4 shows the related message in the final error Coach. The transaction is rolled back, and the amounts in the Karl and Donald accounts do not change.

Figure 4. The error for insufficient money
The error for insufficient money

In this case the runtime fault, intercepted by the k1Charge component, comes from the database trigger on the SQL Server database (Bank1).


Case 3: Invalid account

In this scenario, funds are transferred from an existing account on Bank1 (on SQL Server) to a non-existing account (Donaldx) on Bank2 (on DB2), which results in an exception, raised by the k2Credit Java component. The outcome in this scenario is a transaction rollback.

Following are example input values for this case, as shown in Figure 5:

  • Data source for Bank1 (to Charge) (-): jdbc/bank1sqlserver
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Karl
  • Data source for Bank2 (to Credit) (+): jdbc/bank2db2
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Donaldx
  • Money to transfer: 10
Figure 5. Parameter values to create an Invalid Account case
Parameter values to create an Invalid Account case

Figure 6 shows the related message in the final error Coach. The amount in the Karl account is not changed.

Figure 6. The error for Invalid Account on Bank2 (to Credit)
The error for Invalid Account on Bank2 (to Credit)

Case 4: Invalid Account Case 2

Funds are transferred from a non-existent account, Karlx) on Bank1 (on SQL Server) to an existing account on Bank2 (on DB2), which results in an exception, raised by the k1Charge Java component. The outcome of this scenario is a transaction rollback.

Following are example input values for this case, as shown in Figure 7:

  • Data source for Bank1 (to Charge) (-): jdbc/bank1sqlserver
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Karlx
  • Data source for Bank2 (to Credit) (+): jdbc/bank2db2
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Donald
  • Money to transfer: 10
Figure 7. Parameter values to create another Invalid Account case
Parameter values to create another Invalid Account case

Figure 8 shows the related message on the final error Coach for this case. The amount in the Donald account is not changed.

Figure 8. The error for Invalid Account on Bank1 (to Charge)
The error for Invalid Account on Bank1 (to Charge)

Case 5: Reverse transaction

You can try any combination of the last three scenarios described above, and you can also try to reverse the flow of the transaction, making Bank1 (on SQL Server) the bank with the accounts to credit and the Bank2 (on DB2) the bank with the accounts to charge.

Following are example input values for this case, as shown in Figure 9:

  • Data source for Bank1 (to Charge) (-): jdbc/bank2db2
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Donald
  • Data source for Bank2 (to Credit) (+): jdbc/bank1sqlserver
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Karl
  • Money to transfer: 10
Figure 9. Input values for a reverse scenario
Input values for a reverse scenario

The final state after the positive execution of this reverse transaction is as follows:

On database Bank2 (on DB2):

ID	DESCRIPTION	AMOUNT
2	Donald          990
3	Mickey		2000
4	Minnie		3000

On database Bank1 (on SQL Server):

ID	DESCRIPTION	AMOUNT
1	Karl		110
2	Albert		200
3	Ricky		300

Ten value units were transferred from the Donald account on database Bank2 (on DB2) to the Karl account on database “Bank1” (on SQL Server).


Verify the transactional behavior

Because the implemented SCA-based microflow executes the Credit operation first, we can explicitly show the transactional behavior leveraging the short delay of a few seconds (four seconds in the provided implementation) between the Credit and the Charge invokes in the flow. If, for example, we try to transfer an amount of 200 value units from the Karl account on Bank1 (SQL Server) to the Donald account on Bank2 (on DB2), given the default value of 100 value units of availability for Karl as the initial situation, and providing following input values, as shown in Figure 10:

  • Data source for Bank1 (to Charge) (-): jdbc/bank1sqlserver
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Karl
  • Data source for Bank2 (to Credit) (+): jdbc/bank2db2
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Donald
  • Money to transfer: 200
Figure 10. Parameter values for the demonstration of rollback
Parameter values for the demonstration of rollback

the following actions are executed:

  1. The TxAIS microflow (the AIS implementation) starts, creating a transactional context.
  2. The first operation, the Credit operation on the target account, is invoked (Donald on Bank2 on DB2). The operation completes without errors.
  3. In the wait time between the Credit operation and the subsequent Charge (debit) operation, if you read the "uncommitted records" (dirty read) on DB2 (the target credit account), you'll see something like Figure 11 (in the DB2 command line processor).
    Figure 11. Change in Uncommitted values for the Donald account
    Change in Uncommitted values for the Donald account

    The amount for the Donald account has grown from 1000 to 1200 (but the record is not committed!). A normal select command gives you an amount of 1000 for Donald.

  4. After a few seconds, the second operation on the flow is invoked, that is the Charge (debit) operation on the source account, Karl on Bank1 (on SQL Server). The trigger on the SQL Server Accounts table raises an error, because the requested amount is not available in the Karl account. This error is intercepted (caught) by the k1Charge component, which rethrows the error to the microflow (the BPEL process).
  5. The exception is managed in the BPEL process exception handler, which exits with a fault. At this time, the resources involved in the transaction (the DB2 target database and the SQL Server source databases) receive a rollback command. In fact, if you try to read the records on DB2, you'll see the kind of situation shown in Figure 12.
    Figure 12. Rollback of Donald account to the original value
    Rollback of Donald account to the original value

As you can see, the penultimate uncommitted read still gives a value of 1200 for Donald, but after the exception raised by the k1Charge component, the returned value has a value of 1000 (see Figure 12) for “Donald, which is explicit evidence of the automatic rollback of the transaction.

The error is managed in the BPM flow with the error event, which is connected to the specific Coach to show the error details to the user. Figure 13 shows the error message in the Coach.

Figure 13. The final error message
The final error message

The "Insufficient money" error message is originally generated by the trigger on the SQL Server database, and is later intercepted by the k1Charge component, which rethrows the exception to the BPEL AIS implementation, which in turn returns the exception to the BPMN implementation in IBM BPM.

A similar scenario (with a different kind of error, but with an identical transaction-rollback outcome) can be shown if, for example, you try to transfer an amount of 200 value units from Karlx (or any other account that does not exist) on Bank1 (SQL Server) to Donald (or any other account that does exist) on Bank2 (on DB2). In this case, the credit operation on DB2 is successful, but the debit (charge) operation on SQL Server raises an "Invalid account" error that causes the rollback of the whole transaction.

Following are example input values for this case, as shown in Figure 14:

  • Data source for Bank1 (to Charge) (-): jdbc/bank1sqlserver
  • Table for Account1 for charge (on Bank1): dbo.Accounts
  • Account1 for charge (on Bank1): Karlx
  • Data source for Bank2 (to Credit) (+): jdbc/bank2db2
  • Table for Account2 for credit (on Bank2): ADMINISTRATOR.Accounts
  • Account2 for credit (on Bank2): Donald
  • Money to transfer: 50
Figure 14. Configure a rollback for a non-existing account
Configure a rollback for a non-existing accoun

Figure 15 shows the final error in the Error Coach.

Figure 15. Error for non-existing account
Error for non-existing account

Conclusions

In this series, you've seen how IBM Business Process Manager Advanced, leveraging an implementation in Advanced Integration Services, can manage an ACID distributed transaction between a couple of different database engines (Microsoft SQL Server and IBM DB2). You can implement this kind of management by configuring the interfaces of components involved in the transaction (the Charge and Credit components in the example), without any special custom code implementation.

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.


Acknowledgements

The author would like to thank his colleagues Stefano Angrisano, Marco Antonioni, Giuseppe Bottura, Matteo Franciolli and Daniele Rossi, and his good friends Claudio Cantoni and Alberto Venditti (author, about eight years ago, of a similar demo on a different technological platform) for their reviews and contributions to this article.

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=863110
ArticleTitle=Developing a transactional Advanced Integration Service with IBM Business Process Manager, Part 4: Execution cases
publish-date=04032013