Introduction

This article develops a customer order example application that utilizes database events to invoke business processes using IBM® WebSphere® Integration Developer, WebSphere Process Server, DB2®, and the WebSphere Adapter for JDBC. Learn how database-driven business processes can help you reduce your time to market and total cost of ownership.

Prerequisites

In order to develop the step-by-step example application, consider the following prerequisites:

• WebSphere Integration Developer. You'll need both the development environment and the WebSphere Process Server integrated test environment, which is an option during the installation process of the development environment. The integrated test environment includes a complete WebSphere Process Server runtime that we will use to test the example.
  
  
• WebSphere Adapter for JDBC. See Resources for the official WebSphere Adapter for JDBC IBM site.
  
  
• DB2 Universal Database V8.2. The example uses DB2; however, any JDBC-complaint database can be used if you are comfortable with creating triggers and are familiar with its JDBC driver. A trial for DB2 Universal Database V8.2.2 is available at the following site:
  http://www14.software.ibm.com/webapp/download/brand.jsp?b=Information%20Management&dt=Trial

WebSphere JCA 1.5 Adapters

Java™ Component Architecture (JCA) is part of the Java Enterprise Edition (Java EE) specification to provide a standard way to communicate transactionally with non-Java EE systems as shown in Figure 1. The WebSphere portfolio provides a set of adapters to connect to various Enterprise Information Systems (EIS) such as SAP and Peoplesoft.

This article utilizes the WebSphere Adapter for Java Database Connectivity (JDBC) that can integrate with any database that has a compliant JDBC driver. We'll be using the "inbound interface" of this adapter to kick-off business processes when a create, update, or delete occurs in a database table. For more information on the Java Connector Architecture and the WebSphere Adapter portfolio, see Resources.

The business case for database-driven business processes

Your organization's backend infrastructure may be well suited for database-driven invocation of business processes, but this architectural design decision is not without its considerations or pitfalls. Consider the following factors from a business perspective:

• Database-driven business processes in a legacy environment. If you have legacy systems that already provide the information to invoke business processes in a database, database-driven business processes may be key in reducing time to market. A database can serve as a natural entity to invoke business processes in an enterprise architecture, especially for legacy integration, because organizations typically already have transactions related to their business processes in a database.
  
  
• Transactional Quality-of-Service. If you are in a situation where you cannot lose a single transaction -- such as an invoice, trade or quote -- utilizing the WebSphere Adapter for JDBC with a mature database system can provide the quality of service you desire. A key benefit of the JCA specification is that it defines how Java EE adapters are to be transactional so they can participate in Java EE container transactions. Thus, the WebSphere Adapter for JDBC can participate in a global transaction with your business processes. If a business process fails, the global transaction is rolled back, including any database interaction the adapter has exercised during the transaction.
  
  
• Database skills are a "dime-a-dozen." Utilizing existing database events to invoke business processes can reduce your total cost of ownership because the skills to develop and administer database events are likely already in your organization. Setting up the necessary database triggers and database integration artifacts is intuitive for many organization that use database-driven business processes. However, if your organization is new to WebSphere Integration Developer, Service Component Architecture (SCA), and BPEL Business Processes significant ramp-up training my be required for building the business processes themselves. See Resource for more information on these technologies.
  
  
• Performance is not a strength of this architecture. Network and database access is costly and thus, this may not meet your needs if throughput is of paramount importance. For instance, if another system, System A, writes an event to the database and then WebSphere Process Server picks it up to kick-off a business process, the database is essentially the “man-in-the-middle.” Any architecture that eliminates the database in the middle has more potential for performance. For instance, if System A supports JMS, MQ, RMI, or Web Services, it can communicate with WebSphere Process Server directly. Also, if System A is supported by another WebSphere adapter such as the Siebel or Oracle adapter, such an adapter is more suitable as it also eliminates the database in the middle.
  
  

In summary, database-driven business processes are often a strong architectural design decision for legacy integration, transactional quality of service, and database or mainframe-centric skillsets. Database-driven business processes may not be the best solution when there are stringent performance requirements or there is costly network latency.

Customer Order example

This section details the configuration and development of the customer order example application. The example application is composed of business processes that take customer orders. Consider the following database entities:

When a row is created, updated, or deleted for the ORDER table, a row is added to the ORDER_EVENT table via a corresponding database trigger. The JCA Adapter for JDBC utilizes the ORDER_EVENT table to invoke downstream business processes as shown in Figure 3. The adapter accomplishes this by polling the ORDER_EVENT table for new rows (at an interval that you can specify).

The above diagram is a general illustration that is vendor-neutral. For our example application, the database on the left is DB2, the Java EE runtime is WebSphere Process Server, and the business processes are Service Component Architecture (SCA) business process components based on the Business Process Execution Language (BPEL) standard developed with WebSphere Integration Developer.

Create DB2 tables and triggers

The following instructions walk through the creation of a new DB2 database along with the necessary tables and triggers for the Customer Order Example.

1. Start the DB2 Command Center. We'll be using the DB2 Command Center extensively during the development of the example. If you prefer to use a DDL script to setup the database, see the Downloads section, which includes db2-order-tables-and-triggers.ddl.
1. Windows
   To the start the Command Center under Windows, open the start menu programs and select IBM DB2 => Administration => Command Center.
2. Linux
   Under Linux, you'll need to assume a DB2 administrative user and then execute the db2cc script. Here is an example:
   
   
   Listing 1. Start DB2 Command Center under Linux
   

   [rain%] su db2admin [rain%] password: ******** [rain%] /opt/IBM/db2/V8.2/bin/db2cc

   
   
2. Once the Command Center is open, create a new database called ORDERS. Do so by right-clicking Databases and selecting Create Database => Standard and then click Finish.
   
   
   Figure 4. ORDERS Database Creation
   
   
   
3. Once the ORDERS database is created, open the Command Editor by selecting the “c:>” icon.
   
   
   Figure 5. Open Command Editor
   
   
   
4. Click Add to connect the Command Editor to the ORDERS database. Uncheck Use implicit credentials and insert the username and password for your DB2 instance.
   
   
   Figure 6. Connect Command Editor to ORDERS database
   
   
   
   
   Important: This article assumes a database schema of the name db2admin. This is critical to note because the artifacts we develop for WebSphere Process Server will include the schema name as part of their naming conventions. If you don’t have this schema by default, it is recommended that you create a schema called db2admin with the following command in order to complete the step-by-step example without unnecessary debugging.
   
   
   Listing 2. Create db2admin Schema
   

   CREATE SCHEMA db2admin AUTHORIZATION <your-admin-username-here>

   
   
5. Insert the following command in the command editor and click the execute button as shown in Figure 7.
   
   
   Listing 3. Create Order Table
   

   CREATE TABLE ORDER ( ORDER_ID INTEGER NOT NULL PRIMARY KEY, DESC VARCHAR(255), QTY INTEGER );

   
   
   
   
   Figure 7. Create ORDER Table
   
   
   
6. Similarly, enter the following commands to create an order event table and triggers to populate the event table for insert, update, and delete operations.
   

   Important: If you have an existing table that you want to monitor rather than ORDER, you can use the same event table. If you are using a different schema name, you'll need to change all three triggers to insert a different OBJECT_NAME. The text inserted must exactly match the name of the business graph used by the adapter (see step 2 of Create the business processes). The format for this text is <schema-name><table-name>BG. For example, if the name of your schema is AS2 and the table you are monitoring is called PROCUREMENT, change all Db2adminOrderBG references to As2ProcurementBG.

   
   
   Listing 4. Create Event Table and Triggers
   

   CREATE TABLE ORDER_EVENT ( EVENT_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE ) PRIMARY KEY, OBJECT_KEY INTEGER NOT NULL, OBJECT_NAME VARCHAR(40) NOT NULL, OBJECT_FUNCTION VARCHAR(40) NOT NULL, EVENT_PRIORITY INT NOT NULL, EVENT_TIME TIMESTAMP DEFAULT CURRENT TIMESTAMP NOT NULL, EVENT_STATUS INT NOT NULL, EVENT_COMMENT VARCHAR(100) ); CREATE TRIGGER event_create AFTER INSERT ON ORDER REFERENCING NEW AS N FOR EACH ROW MODE DB2SQLINSERT INTO ORDER_EVENT (object_key, object_name, object_function, event_priority, event_status) VALUES (N.ORDER_ID, 'Db2adminOrderBG', 'Create', 1, 0); CREATE TRIGGER event_update AFTER UPDATE ON ORDER REFERENCING NEW AS N FOR EACH ROW MODE DB2SQLINSERT INTO ORDER_EVENT (object_key, object_name, object_function, event_priority, event_status) VALUES (N.ORDER_ID, 'Db2adminOrderBG', 'Update', 1, 0); CREATE TRIGGER event_delete AFTER DELETE ON ORDER REFERENCING OLD AS O FOR EACH ROW MODE DB2SQLINSERT INTO ORDER_EVENT (object_key, object_name, object_function, event_priority, event_status) VALUES (O.ORDER_ID, 'Db2adminOrderBG', 'Delete', 1, 0);

   
   
7. We need to include the necessary DB2 JDBC Type 4 driver and license JARs in WebSphere Process Server's library directory. The files that you'll need to copy can be found by default in the following locations:
   
   1. Linux
      /home/db2inst1/sqllib/java/db2jcc.jar
/home/db2inst1/sqllib/java/db2jcc_license_cu.jar
   2. Windows
      C:\Program Files\IBM\SQLLIB\java\db2jcc.jar
C:\Program Files\IBM\SQLLIB\java\db2jcc_license_cu.jar
8. Copy both JARs to the lib directory of your WebSphere Process Server installation. Assuming that you are using the WebSphere Process Server runtime that is used as the integrated test environment for WebSphere Integration Developer, the default location for the library directory is as follows:
   
   1. Linux
      /opt/ibm/WebSphere/ID/runtimes/bi_v6/lib
   2. Windows
      C:\Program Files\IBM\WebSphere\ID\runtimes\bi_v6\lib

Import and configure the adapter

This section walks through importing the WebSphere Adapter for JDBC into your WebSphere Integration Developer workspace and configuring it.

1. Open WebSphere Integration Developer in a new workspace.
2. Select File => New => Enterprise Service Discovery. This brings up a wizard that will walk us through the generation of the necessary workspace artifacts and the configuration of the database connection properties.
3. Click Import Resource Adapter and then the Browse button. Select CWYBC_JDBC.rar from the following location:
   <your-adapter-install-directory>/adapter/jdbc/deploy
   
   
   Figure 8. Import Adapter Resource
   
   
   
   
4. At this point, a window appears asking if you want to switch to the J2EE Perspective. Select Yes and then close the Enterprise Service Discovery window. The adapter has been imported to our workspace as a J2EE "Connector" project. We need to switch to the J2EE perspective to add the DB2 JDBC driver to the classpath of the JDBC Adapter project.
5. Expand Connector Projects and right-click CWYPC_JDBC. Then, select Properties => Java Build Path => Libraries tab. Add External JARs and import both db2cc.jar and db2jcc_license_cu.jar as shown in Figure 9.
   
   
   Figure 9. Add DB2 JDBC type 4 driver JARs to Connector Project
   
   
   
6. Click OK and revert to the business integration perspective by selecting the open perspective icon at the top right next to "J2EE."
7. Open the Enterprise Discovery Service wizard again by clicking File => New. Select the JDBC EMD Adapter and click Next.
   
   
   Figure 10. Enterprise Service Discovery Wizard
   
   
   
8. Populate your database credentials and connection properties as in Figure 11. Be sure to change 192.168.0.100 to the IP Address of the machine hosting DB2. If DB2 is on the same machine as WebSphere Process Server, you can use localhost. If your DB2 instance does not use the default 50000 port, don't forget to change that as well.
   
   
   Figure 11. Database Credentials
   
   
   
9. Click Run Query and you should see the tables in the db2schema as shown in Figure 12. Select the ORDER table and then click => Add. Finally, click Next.
   
   
   Figure 12. Introspect ORDER table
   
   
   
10. Ensure that the Service Type is set to Inbound and insert com/ibm/dataobjects for the BOLocation. You can think of this as a package name or namespace for the adapter artifacts.
    
    
    Figure 13. Select Inbound Service Type
    
    
    
11. Notice that a business integration module project is needed to put the adapter artifacts in. Create one by clicking New => Next. Insert EventDrivenProcess for the module name and click Finish.
    
    
    Figure 14. Create Integration Module Project
    
    
    
12. Select Use discovered connection properties as shown in Figure 15.
    
    
    Figure 15. Database Connection Properties
    
    
    
13. The dialog will expand with more fields. Insert the following information:
    
    
    Listing 5. Database Connection Properties
    

    Username: db2admin Password: <your-db2-password> EventOrderBy: EVENT_ID EventTableName: ORDER_EVENT DatabaseVendor: DB2

    
    
14. Finally, click Finish and you should see a newly created business integration module with the inbound adapter Export SCA component.

Create the business processes

Your workspace should look similar to Figure 16. When a database event occurs the JDBCInboundInterface component is invoked. In this section, we'll build the components that are notified when the JDBCInboundInterface is invoked. Namely -- three business processes that are invoked for create, update, and delete operations. Familiarity with Service Component Architecture (SCA) and WebSphere business processes are assumed. For information on developing business processes with WebSphere Integration Developer, refer to the information center provided in the Resources section. .

1. Our first take is to create a business process that consumes an order creation event. Right-click Interfaces and select New => Interface. Insert OrderCreate for the name and com/ibm/interfaces for the folder.
2. Create an interface exactly like Figure 17. Notice that the operation is called createDb2adminOrder, the single Input parameter is called Row, and the BO type for Row is Db2adminOrderBG. This interface is actually a subset of the adapter interface (it includes only the create operation).
   
   
   Figure 17. Build OrderCreate Interface
   
   
   
3. Create two more interfaces called OrderUpdate and OrderDelete in the com/ibm/interfaces folder. Create the same Input as OrderCreate except name the operations updateDb2adminOrder and deleteDb2adminOrder respectively.
4. Right-click the white space on the assembly editor and click Add Node => Process. Name it OrderCreateProcess. Select the floating interface icon and set its interface as CreateOrder. Double-click OrderCreateProcess to implement the component. Insert com/ibm/interfaces for the folder.
5. Below the receive component, create a Java Snippet component called LogProcessEntry. Insert the following java code under Properties tab => Details for LogProcessEntry. In practice, you would likely use a more advanced logging method; however, for our purposes we will use the console.
   
   
   Listing 6. OrderCreate LogProcessEntry Snippet
   

   System.out.println( "============ New Order Process Invoked ============"); System.out.println( "ORDER_ID is "+Row.getDataObject("Db2adminOrder").getInt("orderid")); System.out.println( "DESC is "+Row.getDataObject("Db2adminOrder").getString("desc")); System.out.println( "QTY is "+Row.getDataObject("Db2adminOrder").getInt("qty")); System.out.println( "===================================================");

   
   
6. Next, create a Choice component called ApplyDiscountChoice. Insert the following Java code for the Case component:
   
   
   Listing 7. ApplyDiscount Case Statement
   

   return Row.getDataObject("Db2adminOrder").getInt("qty") > 10;

   
   
7. Under the Case component, create another Java Snippet called Discount. Insert the following Java code for Discount:
   
   
   Listing 8. Discount Snippet
   

   System.out.println("++++++++ Discount Applied! ++++++++");

   
   
8. Right-click the ApplyDiscountChoice component and select Add Otherwise. Under the Otherwise component, create an empty component and call it no-op.
9. Your resulting OrderCreateProcess should look exactly like Figure 18. As you've probably already observed, this business process checks to see if the order quantity is greater than 10 and applies a discount if this is the case. Otherwise, it processes the order normally. In practice, the discount and no-op components would likely be replaced with calls to order processing web services, JMS services, or other SCA components.
   
   
   Figure 18. Order Create Business Process
   
   
   
10. Open your assembly diagram and create a business process called OrderUpdateProcess. Use the OrderUpdate interface and com/ibm/processes for the folder. As before, add a Java Snippet called LogProcessEntry and populate it with the following Java code:
    
    
    Listing 9. OrderUpdate LogProcessEntry Snippet
    

    System.out.println( "============ Update Order Process Invoked ============"); System.out.println( "ORDER_ID is "+Row.getDataObject("Db2adminOrder").getInt("orderid")); System.out.println( "DESC is "+Row.getDataObject("Db2adminOrder").getString("desc")); System.out.println( "QTY is "+Row.getDataObject("Db2adminOrder").getInt("qty")); System.out.println( "======================================================");

    
    
11. Create a Choice component called CheckQuantityChoice. Populate the Case component with the following Java code:
    
    
    Listing 10. CheckQuantity Case Statement
    

    return 0 == Row.getDataObject("Db2adminOrder").getInt("qty");

    
    
12. Create a Java Snippet called RemoveOrder. Insert the following Java code for RemoveOrder:
    
    
    Listing 11. RemoveOrder Snippet
    

    System.out.println("++++++ QTY is 0. Removing Order. ++++++");

    
    
13. Create an Otherwise component with a no-op component below it as before.
14. The resulting UpdateOrderProcess should look exactly like Figure 19. This business process checks to see if an order's quantity is 0. If so, it removes the order from the system (again the implementation is stubbed out for brevity).
    
    
    Figure 19. Update Order Business Process
    
    
    
15. Create a business process called DeleteUpdateProcess. Use the OrderDelete interface and com/ibm/processes for the folder. Add a Java Snippet called LogProcessEntry and populate it with the following Java code:
    
    
    Listing 12. OrderDelete LogProcessEntry Snippet
    

    System.out.println( "============ Delete Order Process Invoked ============"); System.out.println( "ORDER_ID is "+Row.getDataObject("Db2adminOrder").getInt("orderid")); System.out.println( "DESC is "+Row.getDataObject("Db2adminOrder").getString("desc")); System.out.println( "QTY is "+Row.getDataObject("Db2adminOrder").getInt("qty")); System.out.println( "======================================================");

    
    
16. Create a Choice component called CheckForPremiumCustomer. Insert the following Java code in the Case component:
    
    
    Listing 13. CheckForPremiumCustomer Case Statement
    

    String premiumCode = "222"; String orderId = Integer.toString(Row.getDataObject("Db2adminOrder").getInt("orderid")); if(3 > orderId.length()) return false; else return orderId.substring(0,3).equals(premiumCode);

    
    
17. Under the Case component, create a Java Snippet called SendFeedbackSurvey with the following Java code:
    
    
    Listing 14. SendFeedbackSurvey Snippet
    

    System.out.println( "+++++ Premium customer order deleted. Feedback survey send. +++++");

    
    
18. Create an Otherwise and no-op component as before.
19. The OrderDeleteProcess checks to see if the first three digits of orderId are "222." If so, it considers the order associated with a premium customer and sends a feedback survey. The process should look identical to Figure 20.
    
    
    Figure 20. Order Delete Business Process
    
    
    
20. Switch back to your assembly diagram and create a Selector component. Name the selector SelectBusinessProcess.
21. Add the JDBCInboundInterface as the interface for the Selector.
22. Right-click SelectBusinessProcess and select Wire (Advanced) from the dropdown menu. Check all the business process interfaces: OrderCreate, OrderUpdate, and OrderDelete. Click OK.
23. Double-click on SelectBusinessProcess to implement the component and choose com/ibm/selectors for the folder.
24. For each JDBCInboundInterface operation, set the Default Destination to the corresponding BusinessProcess. For example, select createDb2adminOrder and select OrderCreateProcess for the Default Destination as shown in Figure 21.
    
    
    Figure 21. Business Process Selector Implementation
    
    
    

This completes the development of the example application! Your final assembly diagram should look like Figure 22.

Test the application

This section tests the business processes with the Test Component Wizard and finally tests the entire application by populating the ORDER table in DB2.

1. Start WebSphere Process Server under the Server tab.
2. Right-click SelectBusinessProcess and select Test Component which displays the Test Component Wizard.
3. By default, the Test Component Wizard emulates downstream SCA components which is not the our desired result. To remove the emulators, select the Configuration tab and select all three for removal as shown in Figure 23.
   
   
   Figure 23. Remove Emulators from Test Component Dialog
   
   
   
4. Switch back to the Events tab. Populate the createDb2adminOrder operation as shown in Figure 24. You should get the corresponding console output.
   
   
   Figure 24. Test Component Wizard for OrderCreateProcess
   
   
   
5. Be sure to test the updateDb2adminOrder and deleteDb2adminOrder operations as well.
6. To test the entire application with real database-driven events, open the DB2 command center again.
   
   
   Figure 25. Open ORDER table
   
   
   
   You should see a dialog similar to Figure 26.
7. Click Add Row to create a table row and Delete Row to remove a row. To update a row simply edit the fields.
8. Click Commit to finalize your changes to ORDER and trigger the inbound adapter.
   
   
   Figure 26. Edit ORDER table
   
   
   

Conclusion

I hope you've found this introduction to database-driven business processes with the WebSphere and DB2 portfolio timely and useful. We covered what database-driven business processes are, why and when they should be used, and a detailed step-by-step example showcasing how to implement such business processes with the WebSphere Process Server platform. Now that you've gone through the steps to configure and develop the customer order example application, you can use this information to maximize the your organization's efficiency by developing your own database-driven business processes.

Acknowledgements

The most credit for this article is due to David Mulley for helping with technical best practices and troubleshooting. A special thanks to Bhargav Perepa as well who worked with me on this subject at a large government customers. Thanks to Alex Polozoff as well for reviewing this article.

Back to top

Downloads

Information about download methods

Resources

• Official IBM WebSphere Adapter site
  
  
• WebSphere Adapter for JDBC site
  
  
• Java Connector Architecture 1.5 Specification
  
  
• developerWorks article covering JDBC Type 4 Drivers
  
  
• developerWorks WebSphere Business Integration zone
  
  
• Official WebSphere Process Server site
  
  
• Official WebSphere Integration Developer site
  
  
• Business Process Execution Language for Web Services (WS-BPEL) Specification
  
  
• Service Component Architecture (SCA) Specification
  
  

About the author

Robert R. Peterson is part of the enablement team under IBM Software Services for WebSphere. He works to ensure that the WebSphere portfolio of products brings IBM's clients the greatest value possible. Robert is an accomplished inventor and co-author of WebSphere Application Server V6: Performance and Scalability. He is an alumnus of IBM's prestigious Extreme Blue Program and holds a M.S. in Computer Engineering from the University of Florida.

Comments

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Prerequisites
• WebSphere JCA 1.5 Adapters
• The business case for database-driven business processes
• Customer Order example
• Create DB2 tables and triggers
• Import and configure the adapter
• Create the business processes
• Test the application
• Conclusion
• Acknowledgements
• Downloads
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into WebSphere on developerWorks

Rate a product. Write a review.

Write a review for Rational products today!

Special offers