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.
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
Setting the stage
Here is a brief introduction to the products we used and how to configure them to work together.
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:
Create the database and enable it for XML:
- Start the DB2 9 Control Center.
- In the Object view, right click on All Databases and select Create Database => Standard. A Create Database wizard will display.
- On the Name page (Figure 2) of the Create Database wizard, enter
OrderUpdas the Database name.
- Check Enable Database for XML.
Figure 2. Create new database
- Click Next through the Storage and Region pages, accepting the defaults.
- On the Summary page, click Finish.
Locate, change, and run the DDL for the ESB logger mediation primitive:
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.
Open the file in a Notepad window.
Change one column type in the schema, from
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);
- 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
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:
- 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
- In the administrative console, log in without entering a user ID.
- In the Resources link, select JDBC Providers.
- Select New to create a new JDBC provider.
- Select DB2 for database type.
- Select DB2 Universal JDBC Driver Provider for provider type.
- Select XA data source for implementation type.
- Click Next.
- 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
- Click OK on the Configuration panel.
- On the JDBC Providers panel, click on DB2 Universal JDBC Driver Provider.
- On the right side, click on Data Sources.
- Select New.
- For Name, enter
OrderUpdateDS (Figure 6).
- For JNDI name, enter
- For database name, enter
Figure 6. JDBC data source configurations
- Click OK and save the configurations.
- 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.
- Click on the Save link to save the configurations on the server.
- Log out and close the administrative console.
- Restart the server to make the updated information available.
- 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).
A logging mediation for pureXML
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.
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
Figure 8. 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):
Switch to the Business Integration perspective in WebSphere Integration Developer, and open the mediation in the assembly editor (Figure 9).
Figure 9. 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.
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
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.
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
The data source name entered there,
jdbc/OrderUpdateDS, points to the ORDERUPD database we created above. The root variable is set to
/bodyby default and defines that the entire message is logged.
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:
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
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.
Open the Control Center by selecting Start => All Programs => IBM DB2 => DB2 (default) => General Administration Tools => Control Center.
Once the tool has started, navigate to the ORDERUPD database and its ESBLOG.MSGLOG table (Figure 13).
Figure 13. DB2 Control Center
- 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
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>
Now, we want to create a list of all orders that have been changed, hence the following XPath statement:
This statement will:
Select all request messages that stem from an invocation of the updateOrderDeliveryDate operation, which is indicated by the
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
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:
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
Using the Command Center, you can experiment further with different XPath statements to perform further analysis of the logged messages.
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.
We’d like to thank Rob Phippen from WebSphere ESB development and Susan Malaika from DB2 development for their help with this article.
|Sample application||WESB_DB2-ProjectInterchange.zip||289 KB|
- WebSphere ESB product page
- DB2 9 product page
- DB2 9 Express-C download page
- Getting started with WebSphere Enterprise Service Bus and WebSphere Integration Developer
- Developing custom mediations for WebSphere Enterprise Service Bus
- Redbook: Enabling SOA Using WebSphere Messaging
- pureXML in DB2 9: Which way to query your XML data?
- Tutorial: DB2 XML evaluation guide: A step-by-step introduction to the XML storage and query capabilities of DB2 Viper
- DB2 9 technical enablement Wiki
- Redbook: DB2 9: pureXML Overview and Fast Start
- XPath tutorial
- Tutorial: Introducting XQuery