November 12, 2004

DB2 as a Web Services Consumer - Invoking a Web service from a DB2 SQL Statement

This is one more article by me which was published on asp today.

Abstract

DB2 Universal Database (UDB) V8.2 has strong support for Web services and can act as a Web services provider as well as consumer. As a Web service consumer, you can invoke the Web service from within Structured Query Language (SQL) statements by invoking a set of user-defined functions (UDFs). This article explains how to invoke a simple Web service from a DB2 SQL statement.

Introduction

In this article you will see how DB2 V8.2 acts as a Web services consumer. As a Web services consumer, SQL statements in DB2 can directly invoke the Web services method which resides on the other server. This eliminates the need for a separate module for invoking the Web service after receiving data from a SQL statement. It also saves your effort because data can be manipulated within the context of an SQL statement before it is returned to the client application.

To simplify this lets take a scenario where the publishing company has several stockiest worldwide. The central office of the publishing company wants to find out available stock of the book at various locations worldwide. In traditional approach the first step is to get required data from the local database, second is to get the stock of a book from every location using the web service and ISBN number and third present all the data to the user. The same has illustrated in following Figure 1:

image

Figure 1. Traditional Approach to retrieve data from remote location.

The same scenario can be represented using DB2 as web service consumer as shown in Figure 2.

image

Figure 2. DB2 as a Web Service Consumer.

By comparing two figures you will observe that when you use DB2 as a web service consumer there is no need of middle layer or code logic to invoke the web services. In figure 2, the getBookCount () user defined function itself calls web services and returns the stock data. This approach helps to improve productivity and maintainability of the product. In next section of article, you will see how to call a web service from the SQL statement.

System Requirements

In order to demonstrate DB2 as a Web services Consumer you need the following products:

  1. DB2 UDB V8.2

You can download latest trial version of DB2 from http://www-306.ibm.com/software/data/

  1. To create Web services you need Microsoft .NET 1.1 and to deploy the web service you need IIS 5.0 or higher on Windows operating System.

Note: You can create Web services using any platform/technologies. For demonstrating coexistence between different technologies I have developed Web services using Microsoft .NET 1.1.

  1. WebSphere 5.0 or higher (Optional)

WebSphere 5.0 or higher can be used to generate user defined functions(UDFs). WebSphere 5.x has inbuilt wizard to generate UDF to consume web service. This article doesn’t demonstrate generating UDF using WebSphere.

You can download the latest trial version of WebSphere at http://www-306.ibm.com/software/websphere/

Installing and Compiling the Sample Code

Assumptions:

  • The makeBookDB.bat assumes that all database related files are stored in C:\Apress folder while running the scripts.
  • While executing the DB2 related statements, userid and password are not supplied. Therefore DB2 uses default (logged) users authentication and creates the schema accordingly. If you want to specify different userid and password then you need to change database related scripts in attached code.
  • The bookStock web service is created and accessible using following URL:

http://localhost/bookStock/stock.asmx

The attached sample code demonstrate how to use the getBookStock() web method in the DB2 SQL statement. Details of each sample file have given below:

A. bookStock.zip – code for .NET web service

B. The database.zip consists following three files:

I. makeBookDB.bat – This batch file has everything from creating the BOOKINFO database to running a sample SQL statement which invokes bookStock Web Service.

II. bookInfo.db2 – The batch file calls this file to perform database and table related operations.

III. BookStockFromStockiest.udf –This file has User Defined Function (UDF) and the batch file calls this file to register it.

The makeBookDB.bat batch file performs following tasks:

1. Drops the connection to the database if exists already. (This is required if you are running batch file again in same command window.)

2. Drop BOOKINFO database if already exist.

Note: Ignore any exception from step 1 and 2 if you are running script first time since you will not have connection as well as BOOKINFO database.

3. The makeBookDB.bat then calls bookInfo.db2 file from c:\Apress. The bookInfo.db2 file performs following tasks:

a. It creates and connects to the BOOKINFO database.

b. It creates BOOKDETAILS table.

c. Inserts sample data in BOOKDETAILS table.

4. Enables BOOKINFO database for DB2 XML extender using dxxadm command.

5. Enables DB2 web service consumer using the db2enable_soap_udf command.

6. Registers the UDF from c:\apress\BookStockFromStockiest.udf.

7. Finally executes the SQL statement which invokes the getBookStock() web services method.

To install the web service code and to perform database related tasks follow the procedure given below:

  1. Deploy the web service by creating a bookStock virtual folder. The service should be accessible by invoking the URL

http://localhost/bookStock/stock.asmx

  1. Unzip the database.zip file in c:\. (All unzipped 3 files should be present in C:\Apress.)
  2. Open DB2 Command Window using “Programs->IBM DB2->Command Line Tools->Command Window”. This opens command window as "C:\Program Files\IBM\SQLLIB\BIN\>"
  3. Run the makeBookDB.bat on command window as:

C:\Program Files\IBM\SQLLIB\BIN\> C:\apress\ makeBookDB.bat

  1. At the end, the makeBookDB.bat script executes the SQL statement which uses a UDF function to invoke a Web Service.

Article Structure

This article is divided in three sections to explain how DB2 acts as a Web Service consumer:

  • Introduction to DB2 and Web services
  • Creating simple Web services in Microsoft .NET
  • Creating DB2 UDF (User Defined Function) using WebSphere for consuming .NET Web services.

Introduction to DB2 and Web Services

In this section you will see:

  • DB2 V8.2 and Web services
  • DB2 V8.2 as a Web services Consumer
  • The prerequisites for consuming Web services in DB2 V8.2

DB2 V8.2 and Web services

DB2 UDB V8.2 introduced strong support for Web services.DB2 V8.2 can act as Web services provider as well as consumer. As a Web services provider, DB2 exposes web service by which you can execute stored procedure, UDF or SQL statements by using Web services Object Runtime Framework (WORF), also known as Document Access Definition Extension (DADX) files. DB2 exposes SQL statements or stored procedure data as a Web services using DADX file. The Web services Object Runtime Framework (WORF) and Document Access Definition Extension (DADX) are the part of DB2 8.x.

As a Web services consumer, you can invoke Web services from within Structured Query Language (SQL) statements by invoking a set of user-defined functions (UDFs) which actually invoke the web services.

This article is intended to explain how DB2 act as a Web services consumer.

DB2 as a Web Services consumer

DB2 V8.2 comes with the ability to invoke Web services from within Structured Query Language (SQL) statements. This can be achieved by invoking set of User-Defined Functions (UDFs) that provide a high-speed client Simple Object Access Protocol (SOAP) over Hypertext Transfer Protocol (HTTP) interface to access Web services. You can call these functions directly from SQL statements. Using SQL statement to access Web services data can save your effort because data can be manipulated within the context of an SQL statement before it is returned to the client application.

For example, the following SQL statement shows how to use the User Defined Function (UDF) getBookCount () to get the book stock. The getBookCount () function is registered and published as User Defined Function (UDF) on the DB2 server and returns book stock from inputted ISBN number.

Select TITLE,PRICE, getBookCount('wsURLA', ISBN) as STOCK_FROM_A, getBookCount('wsURLB',ISBN) as STOCK_FROM_B from BookDetails where ISBN='1234567890'

The SELECT statement passes the ISBN to the getBookCount () function. In particular, the DB2 function getBookCount () does the following actions:

  • It composes a SOAP request
  • It posts the request to the service endpoint
  • It receives the SOAP response
  • It returns the content of the SOAP body

Prerequisites for DB2 Database to invoke Web services in a SQL statement

Following are the prerequisites for DB2 database to invoke or consume a Web services in the DB2 SQL statement.

DB2 V8.2 database should be enabled for DB2 XML extender The DB2 XML Extender is an integrated component of DB2 and enables a wide range of new applications through the following functions:

  • Extract XML elements and attributes into traditional SQL data types.
  • Store an entire XML document within a column value
  • Query within a XML document
  • Create XML documents from one or more tables
  • Update one or more tables from a XML document
  • Compatible with the powerful search functions of DB2 Net Search Extender for searching one or more sections within a set of XML documents.

In order to use DB2 as web service consumer, make sure that the database is enabled for XML extender.

The dxxadm command can be used in following syntax to enable DB2 XML Extender as shown below. The userid and password are optional in this command and if not supplied it takes credentials for the current logged user:

dxxadm enable_db dbname -l userid -p password

E.g. In our sample example you are enabling BOOKINFO database for DB2 XML Extender as shown below:

C:\Program Files\IBM\SQLLIB\BIN\>dxxadm enable_db BOOKINFO

You can use the sysfunctions table in your database to check whether the database is enabled for XML extender or not.

The sysfunctions table consists of XML related functions if database is enabled for XML extender. Therefore by using following SQL statement you can verify whether the database is enabled for XML extender or not.

select name from sysibm.sysfunctions where name like ‘XML%’

If database is enabled for XML extender then above SQL statement should return some function names starting with XML.

The Web service consumer must be installed and enabled

To enable web service consumer use the db2enable_soap_udf command. This command has following syntax

db2enable_soap_udf -n dbName [-u uID] [-p password] [-force]

e.g.

db2enable_soap_udf -n BOOKINFO -force

To invoke Web services in SQL statement, first you need a Web service and secondly a UDF to invoke that Web service. In particular, you will go through the following tasks:

  • Create and publish Web services
  • Create User Defined Function (UDF)
  • Register and Use UDF in SQL statement

Creating and Publishing Web services

In order to demonstrate how to invoke web service from the DB2 SQL statement you need to create a sample web service. In our example you will create a simple web service to get the stock of a book. The web method in web service will take the ISBN of a book and it will return number of copies available in the stock.

To start, open Visual Studio .NET and create a new ASP .NET Web Service project in C#. Name the project as bookStock and rename the default Service1.asmx as stock.asmx.

Create getBookStock () web method and add the following code in stock.asmx.cs. The getBookStock () method here takes ISBN of the book as input parameter and returns number of books available in the stock. To avoid complexity, we are using simple switch-case logic for returning books available in the stock. In actual case the code will return the available stock from database. The ISBN numbers which you are using in the web method are present in BOOKDETAILS database. The logic will return 0 if there is mismatch for ISBN.

[WebMethod]public int getBookStock(string isbn) { int intStock = 0; switch (isbn) { case "0738490555": intStock = 121; break; case "0738491497": intStock = 321; break; case "0738498246": intStock = 481; break; case "1590592697": intStock = 193; break; case "1590593456": intStock = 256; break; } return intStock; }




Build and test the Web service by running the project. Entire WSDL for above service looks as shown below:




<?xml version="1.0" encoding="utf-8"?><wsdl:definitions xmlns:http="http://schemas.xmlsoap.org/wsdl/http/" xmlns:soap="http://schemas.xmlsoap.org/wsdl/soap/" xmlns:s="http://www.w3.org/2001/XMLSchema" xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:tns="http://tempuri.org/" xmlns:tm="http://microsoft.com/wsdl/mime/textMatching/" xmlns:mime="http://schemas.xmlsoap.org/wsdl/mime/" targetNamespace="http://tempuri.org/" xmlns:wsdl="http://schemas.xmlsoap.org/wsdl/"> <wsdl:types> <s:schema elementFormDefault="qualified" targetNamespace="http://tempuri.org/"> <s:element name="getBookStock"> <s:complexType> <s:sequence> <s:element minOccurs="0" maxOccurs="1" name="isbn" type="s:string" /> </s:sequence> </s:complexType> </s:element> <s:element name="getBookStockResponse"> <s:complexType> <s:sequence> <s:element minOccurs="1" maxOccurs="1" name="getBookStockResult" type="s:int" /> </s:sequence> </s:complexType> </s:element> </s:schema> </wsdl:types> <wsdl:message name="getBookStockSoapIn"> <wsdl:part name="parameters" element="tns:getBookStock" /> </wsdl:message> <wsdl:message name="getBookStockSoapOut"> <wsdl:part name="parameters" element="tns:getBookStockResponse" /> </wsdl:message> <wsdl:portType name="stockSoap"> <wsdl:operation name="getBookStock"> <wsdl:input message="tns:getBookStockSoapIn" /> <wsdl:output message="tns:getBookStockSoapOut" /> </wsdl:operation> </wsdl:portType> <wsdl:binding name="stockSoap" type="tns:stockSoap"> <soap:binding transport="http://schemas.xmlsoap.org/soap/http" style="document" /> <wsdl:operation name="getBookStock"> <soap:operation soapAction="http://tempuri.org/getBookStock" style="document" /> <wsdl:input> <soap:body use="literal" /> </wsdl:input> <wsdl:output> <soap:body use="literal" /> </wsdl:output> </wsdl:operation> </wsdl:binding> <wsdl:service name="stock"> <documentation xmlns="http://schemas.xmlsoap.org/wsdl/" /> <wsdl:port name="stockSoap" binding="tns:stockSoap"> <soap:address location="http://localhost/bookStock/stock.asmx" /> </wsdl:port> </wsdl:service></wsdl:definitions>



Creating User Defined Function (UDF) to call Web services.



In this section you will see:




  • Creating a UDF function to consume Web services.


  • Registering UDF on DB2


  • Calling Web service from DB2 SQL statement.



DB2 requires a User Defined Function (UDF) to consume the Web services and to process the response. In our example you will create getBookStock() function to invoke the bookStock Web service discussed above.



You can manually write UDF function using notepad or any text editing tool but sometimes it’s complex. To make it easy you can use WebSphere 5.1 or higher for creating UDFs automatically. The WebSphere 5.x generates UDF automatically using inbuilt Web Service User-Defined Function Wizard by providing a WSDL of the Web services.



Following is code snippet for the getBookCount() UDF. 




CREATE FUNCTION getBookCount( wsURL VARCHAR(200), parameters_isbn VARCHAR(100) ) RETURNS INTEGER LANGUAGE SQL CONTAINS SQL EXTERNAL ACTION NOT DETERMINISTIC RETURN with soap_input (in) AS (VALUES varchar( '<m:getBookStock xmlns:m="http://tempuri.org/" SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">'  '<m:isbn>'  parameters_isbn '</m:isbn>'  '</m:getBookStock>') ), soap_output(out) AS (VALUES db2xml.soaphttpv( wsURL, 'http://tempuri.org/getBookStock', (SELECT in FROM soap_input)) ) select db2xml.extractInteger(db2xml.xmlclob(x.out), '//getBookStockResult') from soap_output x !




In above code, the getBookCount () function takes ISBN as input parameter and returns total number of books in the stock. The getBookCount () function also takes web service URL as an input parameter. The UDF can be made more flexible by providing web service URL as an input parameter. Following figure shows various components of the getBookCount UDF.



image



Figure 3. UDF described.



Save the UDF after editing it in notepad. Note that you can use any extension for file name and you can store file locally in any folder.



Registering and Using UDF in SQL statement



If you are using WebSphere then it automatically registers UDF in DB2 database.



To register UDF manually in database use the db2 command as shown:



db2 -td! -f c:\taxerUDF.db



In above command the “-td!” tells the command line processor to define and to use “!” as the end of UDF character where as “-f” indicates that the command has to perform on a file.



Finally invoke the UDF function using SQL as shown in the following SQL statement.



select isbn,price, getBookCount ('http://localhost/bookStock/stock.asmx', isbn) AS STOCK_FROM_A from bookdetails



Above command returns all the stock of books and the output is shown below:



db2 => select isbn,price, getBookCount ('http://localhost/bookStock/stock.asmx', isbn) AS STOCK_FROM_A from bookdetails



ISBN PRICE STOCK_FROM_A



---------- --------- ------------



1590593456 59.99 256



1590592697 44.99 193



0738498246 69.00 481



0738491497 46.00 321



0738490555 39.00 121



5 record(s) selected.



To get the stock for particular book you can modify above SQL statement using where clause as:



select isbn,price, getBookCount ('http://localhost/bookStock/stock.asmx', isbn) AS STOCK_FROM_A from bookdetails where isbn ='1590592697'



The output of the above statement is:



db2 => select isbn,price, getBookCount ('http://localhost/bookStock/stock.asmx', isbn) AS STOCK_FROM_A from bookdetails where isbn ='1590592697'



ISBN PRICE STOCK_FROM_A



---------- --------- ------------



1590592697 44.99 193



1 record(s) selected.



If you want to use UDF function without select statement then you can use it as:



values getbookcount ('http://localhost/bookStock/stock.asmx', '1590593456')



The output of above statement will be:



db2 => values getbookcount ('http://localhost/bookStock/stock.asmx', '1590593456')



1



-----------



256



1 record(s) selected.



The following example shows how to retrieve all the book stock from two stockiest assuming web service URLs exists:



select isbn,price,



getBookCount ('http://stockiestA/bookStock/stock.asmx', isbn) AS STOCK_FROM_A,



getBookCount ('http://stockiestB/bookStock/stock.asmx', isbn) AS STOCK_FROM_B



from bookdetails



The DB2 engine throws following error if web service does not exist or web server is down.



SQL0443N Routine "DB2XML.SOAPHTTPV" (specific name "SOAPHTTPVIVO") has



returned an error SQLSTATE with diagnostic text "Error during socket connect".



SQLSTATE=38309



Conclusion



In this article you saw how to invoke Web service from DB2 SQL statement. By invoking a web service from SQL statement saves your effort because data can be manipulated within the context of an SQL statement before it is returned to the client application. This increases productivity by reducing the development time/cost as well as easy for the maintenance.

at 11/12/2004  

Tags: , ,