IBM WebSphere Developer Technical Journal: Using DB2 Version 9 pureXML with WebSphere ESB

Combining the extensive XML support in DB2® Version 9 with the flexible mediation functionality in WebSphere® Enterprise Service Bus (ESB) can result in a powerful solution with broad practical applications.

Dinesh G Shetty (dinu.shetty@in.ibm.com), Software IT Architect, IBM

Author photoDinesh G Shetty works as a Software IT Architect at the IBM Software Lab Services where he leads the Application Integration Competency. He is an expert on WebSphere Message Broker. He has worked in and led WebSphere Message Broker Test teams for more than 4 years. His current role involves reviewing and developing EAI solutions for customers and partners. Dinesh works from the software labs in India. In his spare time he spends time with his family and friends. He is also a keen cruiser motorbike enthusiast.



Srinandan K Sridhar (srisridh@in.ibm.com), Senior Associate Software Specialist, IBM

Srinandan K SridharSrinandan K Sridhar works as a consultant in the Software Group’s Lab Services Organization. His focus areas include WebSphere MQ, WebSphere Message Broker and WebSphere ESB. He lives and works in Bangalore, India.



Andre Tost, Senior Technical Staff Member, IBM

Andre TostAndre Tost works as a Senior Technical Staff Member in the Software Group's Enterprise Integration Solutions organization, where he helps IBM's customers establishing Service-Oriented Architectures. His special focus is on Web services technology. Before his current assignment, he spent ten years in various partner enablement, development and architecture roles in IBM software development, most recently for the WebSphere Business Development group. Originally from Germany, he now lives and works in Rochester, Minnesota. In his spare time, he likes to spend time with his family and play and watch soccer whenever possible.


developerWorks Master author
        level

23 August 2006

From the IBM WebSphere Developer Technical Journal.

Introduction

IBM recently shipped a new version of its flagship database product, DB2 Version 9. With this release comes first class support for XML documents, including the ability to store and query XML documents without the need to map them into a relational model. IBM® WebSphere® ESB offers mediation functionality that can be utilized to implement a service oriented architecture (SOA) by providing tooling and a runtime for connecting (Web) services across multiple protocols and message formats.

About this solution
At the time of this writing, DB2 9 is not yet officially supported for WebSphere ESB V6.0.1, hence the solution described in this article must be considered speculative, and suitable only for test environments and exploratory situations.

Given that, in an SOA environment, much of the data is formatted in XML, combining these two products into one solution is a very attractive proposition. Hence, we will show in this article a first example of how DB2 9 and WebSphere ESB can be integrated together, and how you can take advantage of what both have to offer. We will provide a realistic scenario to serve both as a concrete example, and as a way for us to take you through the detailed process to set it all up.


The business problem and its (IT) solution

We will assume a fictitious company for our scenario. Our company has an online ordering solution, through which customers can order products directly from a catalog available on the Web. When an order has been entered through the Web site, a set of activities is triggered, effectively executing the business process that is responsible for processing the order, including the ultimate delivery to the customer. The various backend systems involved in the processing of the order are all connected via an Enterprise Service Bus (ESB). This provides connectivity between services over various protocols, as well as a separation of concerns, namely by delegating many of the aspects of service interaction to the ESB so that they don’t have to be dealt with in the service implementations.

Furthermore, the company has noticed that many of its customers are complaining about late shipment of orders. It is not clear where and why these late shipments are occurring, and so a mediation is designed that enables the ESB to capture information about any change that is made to the delivery date of an order. The services that have access to the delivery date are well known, so the mediation can be placed in the appropriate place with certainty.

The information that the services exchange is all formatted in XML (SOAP), hence all messages that come across the ESB are in XML also. The new mediation that logs all delivery date changes simply takes the entire payload of the message and logs it to a database. However, capturing this information still does not solve the business problem of late deliveries. The data must be analyzed to identify bottlenecks in the business process, or wherever the cause may lie. Hence, XML-based queries are executed against the database, correlating the message data to pinpoint the problem source. Being able to run those queries directly against the stored XML data makes this task much simpler and faster to develop. The additional control and monitoring of delivery dates result in a direct and measurable benefit from the ESB. The implementation of the solution is made easier by leveraging the XML capabilities of the database.

We will explain how to implement this scenario and show you how the appropriate mediation can be built with WebSphere ESB and DB2 9. Keep in mind, however, that the implementation shown here is a simplification created to show the concept, and is not meant to be a production-quality solution.

Figure 1 shows a high-level overview of the system.

Figure 1. High-level system overview
High-level system overview

Setting the stage

Here is a brief introduction to the products we used and how to configure them to work together.

WebSphere ESB

WebSphere ESB supports the integration of service-oriented, message-oriented and event-driven technologies to provide a standards-based, messaging infrastructure to companies wanting a fast start to an Enterprise Service Bus. WebSphere ESB is based on the robust J2EE 1.4 infrastructure and associated platform services provided by IBM WebSphere Application Server Network Deployment V6.0.2.

WebSphere ESB leverages the Service Component Architecture (SCA) for building mediations and supports a variety of protocols and messaging patterns. Tooling for WebSphere ESB is available with IBM WebSphere Integration Developer, which supports a visual, drag-and-drop style to build mediations.

See the WebSphere ESB product page for more detailed information.

DB2 9 pureXML

Along with much new functionality, DB2 9 introduces a feature that enables storing, searching, validating, and managing XML data. Hence, DB2 9 provides a reliable and scalable platform to access and integrate with traditional backend data, as well as with XML data.

See the DB2 9 product page for more detailed information.

For this article, we have used the Express-C edition of DB2 9, which is a no-charge data server for use in development and deployment of applications. You can download it and use it for free.

Configuring DB2 9 for use with WebSphere ESB

Configuring DB2 9 for use with ESB becomes relevant in our case as we are attempting to use the logging mediation to store the message as XML. Storing the message in its native XML format has several advantages over storing the message as a large object (LOB). In particular, we can query and retrieve portions of the XML messages based on certain filtering predicates, rather than retrieve entire messages and then parse and filter out unnecessary information in our application. This reduces the complexity of our work and offers run time performance improvements.

To realize these advantages, we need to configure our environment. The primary configuration to be done in this case is to change the table schema of the ESB Logger Mediation primitive to store pureXML™ data into the database. The motive behind this article is to show how this data can then be retrieved as XQuery results using simple XPath. The configuration tasks to achieve this are:

  1. Create the database and enable it for XML:

    1. Start the DB2 9 Control Center.
    2. In the Object view, right click on All Databases and select Create Database => Standard. A Create Database wizard will display.
    3. On the Name page (Figure 2) of the Create Database wizard, enter OrderUpd as the Database name.
    4. Check Enable Database for XML.
      Figure 2. Create new database
      Create new database
    5. Click Next through the Storage and Region pages, accepting the defaults.
    6. On the Summary page, click Finish.
  2. Locate, change, and run the DDL for the ESB logger mediation primitive:

    1. Locate the table.ddl file under the folder: <<WID_Install_Dir>>\runtimes\bi_v6\util\EsbLoggerMediation\DB2UDB_EXPRESS_V82. This file will be available under the WebSphere Integration Developer installation (WID). The <<WID_Install_Dir>>\runtimes\bi_v6\util\EsbLoggerMediation folder contains the DDLs for various databases, like SQL Server, Oracle®, Informix®, Cloudscape®, Sybase, and DB2. For the purpose of this article, we use the table.ddl in the DB2UDB_EXPRESS_V82 directory. So that you do not lose the original file, make a copy of the original for safekeeping.

    2. Open the file in a Notepad window.

    3. Change one column type in the schema, from CLOB to XML, to take advantage of the XML capabilities in DB2 9. The updated DDL looks like the code below, with the changes marked in bold:

      -- @start_restricted_prolog@
      -- Licensed Materials - Property of IBM
      -- 5724-I82 5724-L01
      -- (C) Copyright IBM Corporation 2005. All Rights Reserved.
      -- US Government Users Restricted Rights- Use, duplication or disclosure
      -- restricted by GSA ADP Schedule Contract with IBM Corp.
      -- @end_restricted_prolog@
      	
      CREATE SCHEMA ESBLOG;
      
      CREATE TABLE ESBLOG.MSGLOG
        (TIMESTAMP TIMESTAMP NOT NULL,
         MESSAGEID VARCHAR(36) NOT NULL,
      	MEDIATIONNAME VARCHAR(256) 
      	NOT NULL,
         MODULENAME VARCHAR(256),
         MESSAGE XML,
         VERSION VARCHAR(10));
         
      ALTER TABLE ESBLOG.MSGLOG
        ADD CONSTRAINT PK_MSGLOG PRIMARY KEY (TIMESTAMP, MESSAGEID, MEDIATIONNAME);
    4. Run the DDL from the DB2 9 command line processor. A screenshot of the output is shown in Figure 3.
      Figure 3. Execute DDL file
      Execute DDL file
  3. For our sample scenario, we will utilize the WebSphere ESB test environment in WebSphere Integration Developer. To make the new database and table usable in the test server, perform these steps in the administrative console:

    1. Start the console by opening the Servers view in WebSphere Integration Developer. Right-click on WebSphere ESB Server v6.0 and select Run administrative console (Figure 4).
      Figure 4. Start administrative console from WebSphere Integration Developer
      Start administrative console from WebSphere Integration Developer
    2. In the administrative console, log in without entering a user ID.
    3. In the Resources link, select JDBC Providers.
    4. Select New to create a new JDBC provider.
    5. Select DB2 for database type.
    6. Select DB2 Universal JDBC Driver Provider for provider type.
    7. Select XA data source for implementation type.
    8. Click Next.
    9. On the Configuration panel, we need to "hard code" the class path for the JDBC driver JAR files. For example, assuming that you have installed the product in E:\Program Files\IBM\SQLLIB, you would have entered values as shown in Figure 5:
      Figure 5. Setting up CLASSPATH for JDBC provider
      Setting up CLASSPATH for JDBC provider
    10. Click OK on the Configuration panel.
    11. On the JDBC Providers panel, click on DB2 Universal JDBC Driver Provider.
    12. On the right side, click on Data Sources.
    13. Select New.
    14. For Name, enter OrderUpdateDS (Figure 6).
    15. For JNDI name, enter jdbc/OrderUpdateDS.
    16. For database name, enter ORDERUPD.
      Figure 6. JDBC data source configurations
      JDBC data source configurations
    17. Click OK and save the configurations.
    18. In the Data Sources panel, click on Test Connection to verify that the connection succeeds. If this does not work, follow the next steps and try again after restarting the server.
    19. Click on the Save link to save the configurations on the server.
    20. Log out and close the administrative console.
    21. Restart the server to make the updated information available.

A logging mediation for pureXML

We assume here that you have worked with WebSphere Integration Developer V6.0.1 and are familiar with building mediation modules for WebSphere ESB. Two other developerWorks articles, Getting started with WebSphere Enterprise Service Bus and WebSphere Integration Developer and Developing custom mediations for WebSphere Enterprise Service Bus, take you through the basic scenario of creating a mediation module. Read (or at least browse) these articles before continuing with this one.

So far, we configured a data source that lets us write XML data to a table in DB2 9. Next, we will look at an example that will take advantage of the new configuration. Included with this article is a WebSphere Integration Developer project interchange file, called WESB_DB2-ProjectInterchange.zip, which has the entire example in it. Import this file into your WebSphere Integration Developer workspace, with all contained projects selected.

  1. We will consider a simple service for order management that interacts with its consumers via WebSphere ESB. The service takes in order messages together with the updated delivery date. Before each message is actually sent to the service provider, the mediation logs it. The purpose of logging the order messages is to perform XQuery operations on the message to find out why orders were delayed. For this example, the service itself does nothing. It just prints the order ID that is sent to the service. The sample Order.xsd and Customer.xsd are shown in Figures 7 and 8.

    Figure 7. Order.xsd
    Order.xsd
    Figure 8. Customer.xsd
    Customer.xsd

    The service described above is available in the OrderManagement project that you imported with the project interchange file. The enterprise application project is called OrderManagementEAR.

    To work with the OrderManagementMediation module (also imported):

    1. Switch to the Business Integration perspective in WebSphere Integration Developer, and open the mediation in the assembly editor (Figure 9).

      Figure 9. Assembly editor
      Assembly editor

      Notice that our mediation module has one export and one import, and, of course, a mediation flow component. Both the export and import have Web service SOAP/HTTP bindings. The import points directly to the OrderManagement Web service we described above.

    2. To look closer at the mediation flow component, double-click on UpdateOrderComponent, which will open the Mediation Flow Editor. Select the link between the exported and the imported operation, as shown in Figure 10.

      Figure 10. Mediation Flow Editor
      Mediation Flow Editor

      There is only one mediation primitive in the request flow, namely the Message Logger primitive, which logs every message that flows through the bus to a database.

  2. Connect the logging mediation primitive to DB2 9.

    As we mentioned already, we want to log messages in DB2 9, so we have to tell the Message Logger primitive to use the new data source we created earlier. Do that by selecting LogUpdateOrderDelivery and opening the Details tab in its Properties view (Figure 11).

    Figure 11. Setting the DB2 9 JDBC data source to logging primitive
    Setting the DB2 9 JDBC data source to logging primitive

    The data source name entered there, jdbc/OrderUpdateDS, points to the ORDERUPD database we created above. The root variable is set to /body by default and defines that the entire message is logged.

  3. Before you can run a sample requester so that log messages are created and stored in the database, you need to start the embedded WebSphere ESB server in WebSphere Integration Developer, and deploy the following projects to that server:

    • OrderManagementEAR
    • OrderManagementMediationApp
  4. As mentioned above, the mediation module has one export with SOAP/HTTP bindings. The export is represented by a WSDL file that contains the exported interface and the appropriate endpoint address. We used this WSDL file to generate a simple test client, which we then enhanced to generate a number of messages. You can find the test client in the OrderManagementWSClient project (switch to the Java™ perspective to find it). Its main class is called com.acme.order.service.OrderManagementProxy. We will not analyze this application any further, since it is mainly generated from the WSDL file and only serves to create a set of test messages. You can run this file from within WebSphere Integration Developer, by using selecting Run => Run As => Java Application. If run successfully, it prints out a completion statement. In the server console, you should see printed statements from the invoked service, as shown in Figure 12:

    Figure 12. Output from running the sample
    Output from running the sample

The XML query

After we have run the test client application, we have logged a number of messages and can now look at how to query this data. One simple way to execute queries against the database is with the Command Editor tool within the DB2 Control Center, and so we will use that here.

  1. Open the Control Center by selecting Start => All Programs => IBM DB2 => DB2 (default) => General Administration Tools => Control Center.

  2. Once the tool has started, navigate to the ORDERUPD database and its ESBLOG.MSGLOG table (Figure 13).

    Figure 13. DB2 Control Center
    DB2 Control Center
  3. You can use the Open link in the bottom right section to see if there are any rows in the table. However, to run the actual query, right click on the table name and select Query... to open the Command Editor. A default query for all rows in the table will already be filled in (Figure 14).
    Figure 14. DB2 Command Editor
    DB2 Command Editor

    The Command Editor provides a simple way to experiment with various query statements and see the results right away. As we mentioned earlier, we will use XQuery and XPath for our queries into the message log. Here is an example of the XML message that is logged in the database:

    <body xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"           
    xmlns:service="http://service.order.acme.com"           
    xsi:type="service:updateOrderDeliveryDateRequest">
    <updateOrderDeliveryDate>
    <order>
    	<customer>
    		<address>
    			<city>Rochester</city>
    			<country>USA</country>
    			<streetAddress> 9876 Center Street</streetAddress>
    			<zipCode>55901</zipCode>
    		</address>
    		<name>Joe Smith</name>
    	</customer>
    	<orderId>12345</orderId>
    	<deliveryDate>2006-08-04T05:00:00.0Z</deliveryDate>
    </order>
    <newDate>2006-08-06T05:00:00.0Z</newDate>
    </updateOrderDeliveryDate>
    </body>
  4. Now, we want to create a list of all orders that have been changed, hence the following XPath statement:

    /body[@xsi:type='service:updateOrderDeliveryDateRequest']/ updateOrderDeliveryDate/order

    This statement will:

    1. Select all request messages that stem from an invocation of the updateOrderDeliveryDate operation, which is indicated by the type attribute.

    2. Select all <order> elements to create the list we are looking for.

    Once we have the correct XPath statement, we can embed it into an XQuery that indicates which table column we want to run the XPath query on:

    xquerydb2-fn:xmlcolumn('ESBLOG.MSGLOG.MESSAGE') /body[@xsi:type='service:updateOrderDeliveryDateRequest']/ updateOrderDeliveryDate/order

    Insert this statement into the Command Editor window and run the query. The result should be as shown in Figure 15.

    Figure 15. XQuery results
    XQuery results
  5. To refine the query to identify all of those orders that have a shipment address zip code that starts with "10", we can use this XPath statement:

    /body[@xsi:type='service:updateOrderDeliveryDateRequest']/ updateOrderDeliveryDate/order[//zipCode[starts-with(.,'10')]]

    Respectively, the updated XQuery looks like this:

    xquerydb2-fn:xmlcolumn('ESBLOG.MSGLOG.MESSAGE') /body[@xsi:type='service:updateOrderDeliveryDateRequest']/ updateOrderDeliveryDate/order[//zipCode[starts-with(.,'10')]]

    Figure 16 shows the Command Editor that results from this new query.

    Figure 16. XQuery results
    XQuery results

Using the Command Center, you can experiment further with different XPath statements to perform further analysis of the logged messages.


Conclusion

In this article, we showed you how to configure DB2 version 9 to be used with WebSphere ESB and, more specifically, how to use it as the message log database for the logging mediation primitive. We then introduced a concrete scenario that lets you build a mediation flow for a service and log messages in the database, which we then analyzed using XQuery and XPath.

An enhanced example could complement this solution by showing how to read data from the database from within a custom mediation primitive, to enrich a message that flows through the bus, or to make routing decisions, just to name two examples.


Acknowledgements

We’d like to thank Rob Phippen from WebSphere ESB development and Susan Malaika from DB2 development for their help with this article.


Download

DescriptionNameSize
Sample applicationWESB_DB2-ProjectInterchange.zip289 KB

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, Information Management
ArticleID=155138
ArticleTitle=IBM WebSphere Developer Technical Journal: Using DB2 Version 9 pureXML with WebSphere ESB
publish-date=08232006