Level: Introductory Henry Yu, Enterprise Architect , Columbus, Georgia
19 Dec 2002 With DB2, you can use Web services with your preferred application environment. This article shows you how easy it is to create a .NET client that consumes DB2 DADX Web services.
©2002 International Business Machines Corporation. All rights reserved.
Introduction
There have been many articles on using DB2® Universal DatabaseTM
and Web services, but all of the articles I read were about using Web services in a JavaTM environment. I was faced with a business scenario in which the advantages of a Web services programming model were clear, but I needed to access DB2 data with a Microsoft® .NET client. Luckily, IBM recently shipped an enhancement to the DB2 Web services object runtime framework (WORF) to enable interoperability with .NET.
Why Web services and why DB2?
A Web service is an interface that describes a collection of operations that are network-accessible through standardized XML messaging. Web services are built on existing and standards such as HyperText Transfer Protocol (HTTP), eXtensible Markup Language (XML), Simple Object Access Protocol (SOAP), Web Services Description Language (WSDL), and the Universal Description, Discovery, and Integration (UDDI).
Web services provide a language-neutral, environment-neutral programming model that accelerates application integration inside and outside the enterprise and provides an easier-to-use architecture for integrating information.
DB2 Web services support is embodied in the Web Services Object Runtime Framework (WORF). It supports the following types of Web services operations:
- XML-based query or storage. An XML document is stored in DB2 relational tables and composed again on retrieval. This method of operation requires the presence of DB2 XML Extender. You need to create a mapping document called document access definition (DAD) file to describe the mapping between XML documents and relational tables.
- SQL-based operations, such as calling stored procedures, or querying, inserting, updating, or deleting DB2 data. You do not need XML Extender for this method because it uses a default XML tagging based on tables and columns.
Both Web services operations are controlled by a file called Document Access Definition Extension (DADX). All the operations that can be performed by the Web Services are defined in a DADX file. You can manually create a DADX file in a text editor, or you can use WebSphere® Studio tools to generate it.
Because DB2 DADX Web services allows a client to access data in DB2 from anywhere throughout the Internet, it provides valuable capability for many business scenarios, including the one I describe in this article.
A business scenario
A financial service and insurance company has a large sales force to sell their products and services. A salesperson can be an investment advisor, a broker, or an agent. As shown in Figure 1, the salesperson usually uses a front-end system (maybe a 3270 screen) that connects to the company's backend system to support their day-to-day sales activities.
Figure 1. A client/server sales support model
The front-end system does two things:
- Retrieves information from the back-end system, including such things as - product information, customer information and different types of forms. Examples of particular types of information are:
- Submits customer enrollment and open account information to the back-end system for further processing. Most of this activity entails sending completed forms to the back-end system.
Using DB2 DADX Web services, we can devise a new solution for sales support. Instead of building tightly coupled on-line support system, we can build a standalone, loosely coupled smart client system that can work in both connected and disconnected mode.
As shown in Figure 2, this new solution retrieves product-related information and up-to-date forms from the back-end DB2 database through DB2 DADX Web services. It also submits completed forms through DB2 DADX Web services. DB2 is the ideal database to provide data management for this e-business application, and Web services work as a perfect data exchange vehicle.
Figure 2. A smart client solution based on Web services
As a standalone thick client, the smart client system can provide not only a better user interface but also powerful sales support functions such as financial planning tools, analysis tools, and even some simulation tools.
What are the benefits of this solution? First, the smart client system can be used without the backend system connection. When the client is installed on a laptop, sales people are no longer tied to a physical office - they can take the system on the road. The system can even be installed on a desktop at home. Whenever an Internet connection is available, the smart client system can exchange data with back-end system through the Web services. Second, the powerful tools provided by the system will help the sales people provide better service to their customers.
For reasons that are outside the scope of this article, the requirement was to use C# to build this application system on the Microsoft .NET platform. Developing a .NET client in C# for a DB2 DADX Web services is possible because of .NET interoperability support built into WORF. This interoperability is possible because of the WORF support for the "document style" SOAP binding that is used by .NET tools.
The next section demonstrates how to create a .NET client in C# that consumes a DB2 DADX Web service. Because this is just a conceptual prototype, a simplified business scenario is used. The real system is more complicated.
Creating the solution
In this section, I show you how to implement a DB2 DADX Web service and a .NET smart client for a service. The client uses a product code and a state code to retrieve life insurance rate from a rate table in DB2.
Installing and configuring the software
The following software is used in this demonstration:
To set up the development environment, use the directions included in the technical white paper: Implementing DB2 Web Services, included with the download.
http://www.ibm.com/developerworks/db2/zones/webservices/worf/. This article assumes that you install and configure WORF for Apache Jakarta Tomcat on UNIX® and Windows as described in that paper.
If you are using a previous version of WORF, you must install the new
worf.jar, using the instructions that you used before.
Building DADX Web services
Create a new database DADXDEMO in DB2. In this database there will be two new tables: RATETABLE and AGE_TABLE as shown as Figure 3. You can find out all DDL and SQL statements for database creation and sample data in the download portion of this article. You can define the following SQL query and test it with WSAD SQL query builder. This select statement can be used to retrieve Term Life Insurance rate from the RATETABLE and AGE_TABLE based on the product ID and state code.
SELECT DB2ADMIN.AGE_TABLE.MIN_AGE,
DB2ADMIN.AGE_TABLE.MAX_AGE,
DB2ADMIN.RATETABLE.COVERAGE, DB2ADMIN.RATETABLE.RATE
FROM DB2ADMIN.AGE_TABLE, DB2ADMIN.RATETABLE
WHERE DB2ADMIN.AGE_TABLE.AGE_CDE = DB2ADMIN.RATETABLE.AGE_CDE
AND DB2ADMIN.RATETABLE.PRODUCT_ID = :prod
AND DB2ADMIN.RATETABLE.STATE = :state
ORDER BY MIN_AGE ASC, MAX_AGE ASC, COVERAGE ASC |
Figure 3. Database table design
You can use the XML tools in WebSphere Studio Application Developer (WSAD) to generate a DADX file from the SQL query. In addition, you can create groups and group properties for deployment to WebSphere and Tomcat. For testing purposes, it also can generate a Java proxy to access the Web service.
Because the DB2 DADX Web service created in this article is accessed by a .NET C# client, and because I didn't have access to WSAD V5, I developed the DB2 DADX Web service outside of WSAD. But if you have WSAD V5, you can use the tools instead.
The DADX file in Listing 1 was generated and exported from WSAD. It is included in the download that accompanies this article.
Listing 1. DADX file ProdInfoDadx.dadx
<?xml version="1.0" encoding="UTF-8"?>
<dadx:DADX
xmlns:dadx="http://schemas.ibm.com/db2/dxx/dadx"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"
xsi:schemaLocation="http://schemas.ibm.com/db2/dxx/dadx
dadx.xsd http://schemas.xmlsoap.org/wsdl/ wsdl.xsd">
<dadx:operation name="QueryProdInfo">
<wsdl:documentation
xmlns="http://www.w3.org/1999/xhtml">
</wsdl:documentation> <dadx:query>
<dadx:SQL_query> SELECT
DB2ADMIN.AGE_TABLE.MIN_AGE, DB2ADMIN.AGE_TABLE.MAX_AGE,
DB2ADMIN.RATETABLE.COVERAGE, DB2ADMIN.RATETABLE.RATE
FROM DB2ADMIN.AGE_TABLE, DB2ADMIN.RATETABLE WHERE
DB2ADMIN.AGE_TABLE.AGE_CDE = DB2ADMIN.RATETABLE.AGE_CDE
AND DB2ADMIN.RATETABLE.PRODUCT_ID = :prod AND
DB2ADMIN.RATETABLE.STATE = :state ORDER BY MIN_AGE ASC,
MAX_AGE ASC, COVERAGE ASC </dadx:SQL_query>
<dadx:parameter name="prod" type="xsd:string"/>
<dadx:parameter name="state" type="xsd:string"/>
</dadx:query> </dadx:operation>
</dadx:DADX>
|
Deploying and testing the Web service
For a simple DADX file, it's not worth creating a new Web application for its deployment. Instead, I use the WORF example Web application.
Here are the steps for deploying the DADX file with Tomcat:
- Create a new group
dxx_prodinfo by cloning group
dxx_sample.Group, which is part of the WORF samples. You can find it under services\WEB-INF\classes\groups. This "services" is the services context as described in the WORF white paper.
- Put the DADX into a new group called
dxx_prodinfo.
- Copy
group.properties from
dxx_sample to dxx_prodinfo.
- Modify
group.properties as follows:
- Update the
web.xml file to add entries for the
dxx_prodinfo group.
To test the Web service, start Tomcat, and then use the following URL to test the DB2 DADX Web service:
http://127.0.0.1:8080/services/db2prodinfo/ProdInfoDadx.dadx/TEST |
Figure 4 is the testing screen.
Figure 4. Testing the QueryProdInfo service
Creating the .NET client
The WSDL file is the key for a client to consume Web services. It describes the particular Web service interface. WSAD can generate the Web Service proxy from the WSDL file. Client programs use a proxy to interact with the Web services. It is the proxy that shields all SOAP messaging from client programs.
The default WSDL binding style is "rpc". However, the new version of WORF also supports the "document" binding style option. IBM tooling supports both options. But Microsoft tooling only supports "document" style option. Use the "document" style option support in WORF for interoperability with .NET.
Figure 5 . WSDL file with the "rpc" binding style
For a given DADX file, WORF generates a WSDL file for the Web service using a URL of the following format:
http://localhost:8080/webapplication/group/dadxfile/WSDL |
Figure 5 shows what the WSDL file looks like when you don't specify the useDocumentStyle property. It produces a WSDL file with the "rpc" binding style and the "literal" default encoding. This is the type of WSDL file produced also by WSAD V4.x.
A Java proxy can be easily created in WSAD using this type of WSDL file. However, the Microsoft WSDL tool in the .NET Framework SDK doesn't accept this WSDL file style; it only accepts the SOAP "document" binding style with the "literal" default encoding.
Using the latest version of WORF, we can generate a WSDL file with "document" binding style by using the property useDocumentStyle. We have added this property with a value of true into the group.properties file in the group dxx_prodinfo. Figure 6 shows what the "document style" WSDL looks like, which can be used with the Microsoft WSDL tool.
Figure 6. WSDL file with SOAP "document" binding style
A .NET WSDL proxy for the ProdInfo DB2 DADX Web services can be generated using the "document" style WSDL file.
- Make sure Tomcat is up.
- Then issue the following command to generate a C# WSDL proxy:
wsdl /nologo /language:cs /namespace:COM.ML
/out:ProdInfoProxy.cs
http://localhost:8080/services/db2prodinfo/
ProdInfoDadx.dadx/WSDL |
- Compile the C# WSDL proxy using the following command:
csc /nologo /out:ProdInfoProxy.dll /target:library ProdInfoProxy.cs |
Based on the WSDL C# proxy, any application client can be developed to consume DB2 DADX Web services. Here I use the simple console-based C# program in Listing 2 as a sample. Even though it is simple, it has everything we need for this demo. And it can be refined into a reusable class for complicate application development. Several classes used in it are defined in proxy ProdInfoProxy.cs. .
Listing 2. Console-based C# client code
using System;
using System.Data;
using COM.ML;
public class ProdInfoClient {
public static void Main( )
{
QueryProdInfoResponse qRow;
QueryProdInfoResultQueryProdInfoRow[] pRecords;
string min_age;
string max_age;
string coverage;
string rate;
theService proxy = new theService();
QueryProdInfo qParm = new QueryProdInfo();
WriteMessage("Invoking Web Service Method through the proxy.");
try
{
qParm.prod = "VGTLIFE";
qParm.state = "NJ";
qRow = proxy.QueryProdInfo(qParm);
pRecords = qRow.@return.QueryProdInfoResult;
for(int i=0; i < pRecords.Length; i ++)
{
min_age = pRecords[i].MIN_AGE.ToString();
max_age = pRecords[i].MAX_AGE.ToString();
coverage = pRecords[i].COVERAGE.ToString();
rate = pRecords[i].RATE.ToString();
WriteMessage(
min_age + " " + max_age + " " + coverage + " " + rate);
}
}
catch(Exception e)
{Console.WriteLine("Threw general exception: {0}", e);}
}
private static void WriteMessage(string message)
{
Console.WriteLine("Server returns: {0}", message);
}
}
|
- Save this program as
ProdInfoClient.cs.
- Compile it as follows:
- Run
ProdInfoClient.exe.
Figure 7 shows what this looks like: the .NET C# client gets the Term Life Insurance rates for product code VGTLIFE
and state code NJ.
Figure 7. Demo .NET C# client running result
In actual system development, the term life insurance rates will most likely be stored into a .NET DataSet object. It can be persisted into a local data repository or an XML file for later use. Since this up-to-date product data has been stored in a local data store, a sales person can use the smart client system while disconnected from the backend system.
A Web services provider may involve more complicated DB2 DADX Web services using insert, update, delete statements or even triggering a stored procedure. Different operations can be glued into one or several DADX files. A .NET smart client can work with the Web services provider to form a new computing paradigm.
Conclusion
In this article, I've shown you how to create a .NET C# client to consume a DB2 DADX Web service. The latest WORF improves the interoperability between DB2 DADX Web services and the Microsoft .NET platform. Integrating DB2 with a .NET smart client through Web services shows new opportunities providing better business solutions.
Download | Name | Size | Download method |
|---|
| dadxsample.zip | 6KB | |
Resources
About the author  | | | Henry Yu is an Enterprise Architect with extensive experience in developing large-scale, complex enterprise-wide architectures, OO and client-server architectures and Mainframe development. He has been involved in both business and technical aspects of the full life cycle of software development, especially in the financial industry. He can be reached at j2ee_2000@yahoo.com. |
Rate this page
| 
