Article Author: Ajit Mungale
Introduction
In this article you will see how DB2 8.2 acts as a web services consumer. As a web services consumer, SQL can directly invoke a web service which resides on a different server. This eliminates the need for a separate module for invoking the web service after receiving data from an SQL query. It also saves you effort because data from a web service can be manipulated with SQL before it is returned to the client application.
With the release of Microsoft SQL Server 2005 comes SQLCLR support. This means that by using managed code, you can now call web services from SQL Server 200,5 too. This article ,however, will focus on DB2, where the mechanism for invoking web services is rather different.
To see why calling a web service could be useful, let’s view a scenario where a publishing company has several warehouses worldwide. The central office of the publishing company wants to find out the available stock of the book at various locations. In a traditional approach, the first step is to get the required book identification data from the local database, and next step is to get the stock information from every location – eg. using a web service and the ISBN number – finally you’d present all the data to the user. This is illustrated in Figure 1:
Figure 1. The traditional approach to retrieving data from a remote location.
The same scenario can be represented using DB2 as a web service consumer as shown in Figure 2; in this figure the numbers indicate the sequence of events.
Figure 2. DB2 as a web service consumer.
By comparing the two figures, you will observe that when you use DB2 as a web service consumer there is no need for a 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 code maintainability.
In the next section, you will see how to call a web service from SQL.
System Requirements
In order to demonstrate DB2 as a web services consumer you need DB2 version 8.2 You can download the latest trial version from http://www-306.ibm.com/software/data/.
In this article I used .NET 1.1 to create and deploy the web service because I thought that would give a nice demonstration of coexistence between different technologies.
Although I didn’t do so in this article, If you are using similar techniques in your own code, you might also find it helpful to use WebSphere 5.0 to generate the DB2 user-defined functions (UDFs), because WebSphere 5.x has a built-in wizard to generate UDFs to consume web services.
You can download the latest trial version of WebSphere from http://www-306.ibm.com/software/websphere/
About the Sample Code Download
The attached sample code demonstrates how to use a web service called getBookStock with DB2 SQL. If you open the zip file you’ll find, at the top level, it contains one file makeBookDB.bat,and two folders BookStock, and Database:
The BookStock folder contains the code for the .NET web service
The Database folder contains the files that set up the database, including thes files:
- makeBookDB.bat – This batch file has all the SQL, from creating the BOOKINFO database to the query that invokes the bookStock web service.
- bookInfo.db2 – The batch file calls this file to perform database and table-related operations.
- BookStockFromWarehouse.udf -This file contains the user defined function (UDF); the batch file calls this file to register it.
The makeBookDB.bat batch file performs following tasks:
- Drops the BOOKINFO database if it already exists, invokes the bookInfo.db2 file from C:Apress. bookInfo.db2, creates and connects to the BOOKINFO database and the BOOKDETAILS table, and inserts sample data into the BOOKDETAILS table.
- Enables the BOOKINFO database for the DB2 XML extender using a dxxadm command. Note that the DB2 XML extender is discussed later in this article.
- Enables the DB2 web service consumer using the db2enable_soap_udf command.
- Registers the UDF from C:apressBookStockFromWarehouse.udf.
- 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:
- Deploy the web service by creating a bookStock virtual directory in IIS. The service should be accessible by invoking the URL http://localhost/bookStock/stock.asmx
- Unzip the files in the database.zip file to C:Apress.
- Open the DB2 command window using Programs->IBM DB2->Command Line Tools->Command Window. This opens command window in C:Program FilesIBMSQLLIBBIN>
- Run makeBookDB.bat in a command window as:
C:Program FilesIBMSQLLIBBIN> C:apress makeBookDB.bat
- Finally, the makeBookDB.bat script executes the SQL query which uses a UDF function to invoke a web service.
Note that the code makes the following assumptions:
- The utility makeBookDB.bat assumes that all database related files are stored in C:Apress while running the scripts. You can put the files somewhere else, but then you should make changes in the .bat file accordingly.
- While executing the SQL, the userid and password are not supplied. Therefore DB2 uses the executing user’s authentication and creates the schema accordingly.
- The bookStock web service is created and accessible using the URL, http://localhost/bookStock/stock.asmx
Introduction to DB2 and Web Services
DB2 8.2 introduced strong support for web services: DB2 8.2 can act as a web services provider as well as a consumer.
DB2 as a web services provider requires that the Web Services Object Runtime Framework (WORF), also known as Document Access Definition Extension (DADx) is installed. WORF is provided as a zip file, dxxworf.zip, and is shipped with DB2 UDB server editions. It can be found in the subdirectory SQLLIBsamplesjavaWebsphere under the directory where DB2 was installed. WORF is also shipped with WebSphere Information Integrator, WebSphere Studio Version 5 and Rational Application Developer 6.
DB2 as a web services consumer requires that you install the DB2 XML extender and that you enable DB2 web services consumer UDFs
The next section discusses how DB2 can act as a web services provider and consumer.
DB2 as a Web Services Provider:
When DB2 is acting as a web services provider, a web services client application can access a DB2 UDB database through a WSDL interface. You can create a WSDL interface to DB2 data using WORF. In simplified terms, access to DB2 data can be defined using an XML file called the DADx file. This XML file can contain a series of operations. Each of the operations can consist of DB2 stored procedure invocations, XML document storage or retrieval, or CREATE, SELECT, UPDATE, DELETE SQL statements. The following code snippet provides an example of a DADX file with one operation based on a SQL query.
<?xml version="1.0" encoding="UTF-8"?> <DADX xmlns=http://schemas.ibm.com/db2/dxx/dadx> <operation name="showemployees"> <query> <SQL_query>SELECT * FROM EMPLOYEE</SQL_query> </query> </operation> </DADx>
In this code, a single operation called showemployees is defined for retrieving all of the data from the employee table within a DB2 database. The data is returned from the DB2 web service as an XML document or as a Java object. XML parsers are readily available for various programming languages, including Java and C.
DB2 as a Web Services consumer
DB2 behaves as a web services consumer when it invokes a web service through SQL.. This can be achieved by invoking a set of user-defined functions that provide a high-speed SOAP interface to access web services over HTTP. You can call these functions directly from SQL. Using SQL to access data from web services can save you effort because data can be manipulated by SQL before it is returned to the client application.
For example, the following SQL query shows how to use the UDF getBookCount to get the book stock. It presumes the getBookCount function is registered and published as a user-defined function on the DB2 server and returns book stock based on an 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 query passes the ISBN to the getBookCount function. In particular, the UDF 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 Invoking Web Services with SQL
There are two prerequisites for a DB2 database to invoke or consume a web service with SQL.
- The DB2 8.2 database should be enabled for the DB2 XML extender
- The web service consumer must be installed and enabled
The XML Extender
The DB2 XML Extender is an integrated component of DB2 and enables a wide range of new applications through the following features:
- Extracting XML elements and attributes into traditional SQL data types.
- Storing an entire XML document within a column value
- Querying within an XML document
- Creating an XML document from one or more tables
- Updating one or more tables from an XML document
- Searching one or more sections within a set of XML documents.
The dxxadm command can be used as follows to enable the DB2 XML Extender as shown below. The userid and password are optional in this command, and if not supplied they default to the credentials for the current logged user:
dxxadm enable_db dbname -l userid -p password
The following snippet enables the BOOKINFO database for the DB2 XML Extender:
C:Program FilesIBMSQLLIBBIN>dxxadm enable_db BOOKINFO
You can use the sysfunctions table in your database to check whether the database is enabled for the XML extender or not. The sysfunctions table contains XML-related functions if database is enabled for XML extender. Therefore, by using following query you can verify whether the database is enabled for the XML extender or not.
select name from sysibm.sysfunctions where name like ‘XML%’
If the database is enabled for the XML extender, then the query should return some function names that start with XML.
Enabling the Web Service Consumer
In order to enable the web service consumer, use the db2enable_soap_udf command. This command has the following syntax
db2enable_soap_udf -n dbName [-u uID] [-p password] [-force]
For example:
db2enable_soap_udf -n BOOKINFO -force
To invoke web services in SQL, first you need a web service and secondly a UDF to invoke that web service. In particular, you will go through the following tasks:
Creating and Publishing web services
In order to demonstrate how to invoke web services from DB2 with SQL, you need to create a simple web service. In this example you will create a simple web service to get the stock of a book. The web method in the web service will take the ISBN of a book and it will return number of copies in stock.
To start, open Visual Studio .NET and create a new ASP .NET Web Service project in C#. Name the project bookStock and rename the default Service1.asmx to stock.asmx.
Create a getBookStock web method and add the following code in stock.asmx.cs. The getBookStock method here takes the ISBN of the book as its input parameter and returns the number of books available in the stock. To avoid complexity, I’m using simple switch-case logic for returning the books in stock. In real life, the code will return the available stock from the database. The ISBN numbers which you are using in the web method are present in the BOOKDETAILS database. The logic will return 0 if there is a mismatch for the 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. The entire WSDL for above service is 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 Functions to call web services.
DB2 requires a user-defined function to consume web services and process their responses.
In our example you will create a getBookStock function to invoke the bookStock web service discussed above.
Note that web services deal with XML-formatted data while SQL deasl with datas in tables. As discussed earlier, XML extender functions takes care of converting XML elements and attributes into traditional SQL data types.
You can manually write a UDF function using Notepad or any text editing tool, but sometimes that’s complex to do. To make it easier, you can use WebSphere 5.1 or higher to create UDFs automatically. WebSphere 5.x generates UDFs automatically using a built-in Web Service User-Defined Function Wizard by providing a WSDL of the web services.
The following code shows the entire getBookCount UDF.
CREATE FUNCTION getBookCount( wsURL VARCHAR, parameters_isbn VARCHAR ) 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 this code, the getBookCount function takes the ISBN as input parameter and returns the total number of books in stock. The getBookCount function also takes a web service URL as an input parameter. The UDF can be made more flexible by providing the web service URL as an input parameter. Figure 3 shows various components of the getBookCount UDF.
Figure 3. UDF described
Save the UDF after editing it in a text editor (Notepad is fine!).
Registering and Using UDF in SQL
To register the UDF manually in the database use the db2 command as follows:
db2 -td! -f c:taxerUDF.db
This -td! command tells the command-line processor to use an ! as the end-of-statement and -f specifies the file that contains the SQl to process.
If you create tour UDF with a WebSphere wizard rather than using the manual process described here, then it automatically registers the UDF in the DB2 database.
Finally, you should invoke the UDF function using an SQL query, as follows:
select isbn,price, getBookCount (‘http://localhost/bookStock/stock.asmx’, isbn) AS STOCK_FROM_A from bookdetails
This query returns the stock for all books. Its output is:
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 query, using a 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 this query 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 a UDF without a query then you can use a VALUES statement:
values getbookcount (‘http://localhost/bookStock/stock.asmx’, ‘1590593456’)
The output of the above statement is:
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 information from two warehouses.
select isbn,price, getBookCount (‘http://warehouseA/bookStock/stock.asmx’, isbn) AS STOCK_FROM_A, getBookCount (‘http://warehouseB/bookStock/stock.asmx’, isbn) AS STOCK_FROM_B from bookdetails
DB2 reports the following error if the web service does not exist or the 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 a web service from a DB2with SQL. Invoking a web service from SQL saves effort because data can be manipulated with SQL before it is returned to the client application. This increases productivity by reducing the development time and cost and making it easier to maintain.
