IM databases

TimesTen

TimesTen provides a family of real-time infrastructure software products designed for low latency, high-volume data, event and transaction management. Unlike conventional infrastructure software, TimesTen’s products are optimized for deployment within the application tier. They are used in networks, telecom services, operational support systems, contact centers, airline and reservation systems, and securities trading applications. They are also being used more broadly for mid-tier data management, for example in e-commerce applications and service-oriented architecture (SOA) infrastructures, because of their performance, low maintenance, flexibility, and reliability.

TimesTen is designed with the knowledge that data resides in main memory and can take more direct routes to data, reducing the length of the code path and simplifying algorithms and structure. (RDBMS designed for disks)

June 20, 2005, the acquisition of TimesTen by Oracle

FastDB

FastDB is opensource Object-Relational Main-Memory Embedded Database system tightly integrated with C++ language. It uses OS virtual mapping mechanism to access data & provides subset of SQL language with OO extensions.

GigaBASE

GigaBASE is object-relational embedded database engine for C++ applications. It provides SQL-like query language, smart C++ interface (loading objects instead of tupples), transaction based on shadowing page algorithm

Mnesia

Mnesia is a distributed DataBase Management System (DBMS), appropriate for telecommunications applications and other Erlang applications which require continuous operation and exhibit soft real-time properties.

nHibernate – To be or not ?

There are lots of chaos around ORM like nHibernate. Some of in favor of nHibernate where as some hate it like nothing. The triumph of nHibernate is totally depends on architecture of your project & complexity of the database.

Advantages of nHibernate

• Rapid application development – you don’t have to write any SQL or stored procedure!
  • Mapping can be made easy with ActiveRecord (attribute-based mapping) - you can reduce the time of writing XML or any relational attributes.
• Better for maintenance – no stored proc, no SQL therefore any table changes affects only entities & associated layers
• Clear separation of UI & DB layer. If you rename any field in any table then you have to change only entity mapping. Where as in traditional relational model any table field change affects all layers.
• Easily migrate your code between different databases
• Provides business entity form of representation of DB tables so you get all advantages of OOP.
• Massive gain in performance with distributed cache (e.g. memcache) (assuming most data is non-volatile)
• Think about nested query

Disadvantages

ORM is not a good choice if
• your database has complex(deep) relationships
• high frequency applications where data is volatile
• old/legacy/migrated database – In ORM first you have to develop object model & then database. If you don’t want to break existing poor relations then ORM is not a good choice
• disconnected layers – smart client won’t supports lazy loading; transferring data & message size (Creating DAO classes mapped with domain may improve performance but requires lots of coding & therefore future maintenance)
• consider time require to train developers
• consider time for optimization

Conclusion

DAO and ORM are both valid approaches to persistence and the choice between them needs to be considered on a per-project basis. DAO approach is industry standard & proven approach. Since DAL is most vital part of any architecture you need to be VERY careful if you are going to use ORM like nHibernate.

Some refrences & sample code

• http://www.oracle.com/technology/pub/articles/vasiliev-jpql.html
• http://www.metaarchit.com/articles.html

XML AUTO in SQL 2005

Mark has sent a tip which automatically generates an XML document as the output of the query using "FOR XML AUTO" functionality.

Adding the elements keyword lets you nest nodes. E.g. It outputs an INVC record with nested HIST items.

<INVC>

<HIST>

<HIST>

select * from invc I inner join HIST H on I.VENDID=H.VENDID and I.INVOICE=H.INVOICE and I.EXPPED=H.EXPPED where I.invoice='testUI1' FOR XML AUTO, elements

Pretty slick and a nice time saver when you need a quick XML doc to test with. There is extensive help in SQL on FOR XML AUTO.

ADO .NET Entity Framework

The upcoming version of ADO .NET comes with entity framework, you can read some more on this MSDN article.

SQL Server 2005 Security Best Practices

Came across this page - SQL Server 2005 Security Best Practices - Operational and Administrative Tasks. This page also points to an free eBook on seup and migration task.

OLAP in SQL Server 2005

This article got from a fellow co-worker, discusses the major OLAP components of Analysis Services, all of which can be implemented by even a first-time cube builder. A follow up article by Mark Frawley will examine the differences between Analysis Services in SQL 2000 and SQL 2005.

Warehouse data information

Following is the link for the brief overview of the data warehouse concept:

http://www.devx.com/dbzone/Article/21410/0/page/1

Parameterized query

Here are some parameterized query examples

-- Get the text of the SysObjects view.
exec sp_executesql N'exec sp_helptext @objname=@p0',N'@p0 varchar(50)',@p0='sysobjects'
-- Query the SysObjects view.
exec sp_executesql N'select * from master..sysobjects where name=@p0',N'@p0 varchar(50)',@p0='sysusers'
-- Attempt SQL injection...fails.
exec sp_executesql N'select * from master..sysobjects where name=@p0',N'@p0 varchar(50)',@p0='sysusers''; drop database Northwind; --'

HOW to Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C# .NET

HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C# .NET: "This step-by-step article describes how to do bulk inserts and updates with different Microsoft .NET data providers by using the OpenXML method. Although the sample in this article uses the SqlClient managed provider, you can also use the OLEDB or the ODBC managed provider."

Removing lock on a table

Use following sql to remove lock on any table (E.g. Called Transaction Begin without end)

set transaction isolation level read uncommitted

ORM is dead

Chris has sent me following info with deadly title...

Anyone who thinks ORM is the "latest and greatest" hasn't had much exposure to The Brave New World . . .

http://www.db4o.com/about/productinformation/

UPDLOCK / READPAST / ROWLOCK

READPAST
Specifies that the Database Engine not read rows that are locked by other transactions.

For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5. READPAST is primarily used to reduce locking contention when implementing a work queue that uses a SQL Server table. A queue reader that uses READPAST skips past queue entries locked by other transactions to the next available queue entry, without having to wait until the other transactions release their locks.

ROWLOCK
Specifies that row locks are taken when page or table locks are ordinarily taken.

UPDLOCK ( UPDLOCK , READPAST )
Specifies that update locks are to be taken and held until the transaction completes.

Example -

-- Use the READPAST optimizer hint to skip the locked row & at the same time
-- requesting for UPDLOCK
IF ( SELECT au_id FROM authors WITH( UPDLOCK , READPAST )
WHERE au_id = '172-32-1176' ) IS NULL
PRINT 'Row is locked for update / delete.'
ELSE
PRINT 'Row is not locked for update /delete.'

-- To handle multiple locking of multiple rows that satisfy a condition
-- Count of rows should be know before to use this check

if COALESCE( ( SELECT COUNT( * ) FROM authors WITH( UPDLOCK , READPAST )
WHERE state = 'CA' ) , 0 ) <> 15
PRINT 'Some rows are locked for update / delete.'
ELSE
PRINT 'Rows are not locked for update /delete.'


-- Using LOCK_TIMEOUT to timeout the SELECT statement
SET LOCK_TIMEOUT 1000 -- 1 second timeout
DECLARE @au_id
SET @au_id = ( SELECT au_id FROM authors WITH( UPDLOCK )
WHERE au_id = '172-32-1176' )
IF @@ERROR <> 0
PRINT 'Row is locked for update / delete.'
ELSE
PRINT 'Row is not locked for update / delete.'


To get more info visit http://msdn2.microsoft.com/en-us/library/ms187373(SQL.90).aspx

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.

Login failed for user 'username'. The user is not associated with a trusted SQL Server connection.

This error occurs if the SQL server has been configured to operate in "Windows Authentication Mode (Windows Authentication)" and doesn't allow the use of SQL accounts (e.g. sa, myuser etc). You can see KB article for same here..

This was happening because during installation, SQL Server Database Engine was set to Windows Authentication mode. This topic describes how to change the security mode after installation.

SQL 2005: RESTORE FILELIST is terminating abnormally

Using SQL Server Mgmt Studio (2005) I was trying to restore a 2005 database from a backup device and when I tried
to add the backup device in the restore database wizard I got the following error message:

Too many backup devices specified for backup or restore; only 64 are allowed.
RESTORE FILELIST is terminating abnormally.

I tried following command but got same error message:

RESTORE FILELISTONLY FROM disk = 'C:\testportal.bak'

This error message occurs when you try to restore a SQL 2005 database onto a SQL 2000 instance.

When I checked version using “select @@version” it was showing me that I was running SQL 2000!

So I went to Add / Remove programs (Control Panel) and removed SQL 2000 related stuff. (which was unnecesarry)

I tried to backup database from file but again got same error message.

Finally I found that to use 2005 server I have to connect to ‘mymachine/sqlexpress’ and not to ‘mymachine’

Passing Arrays into a stored procedure

Sue sent me following link for passing Arrays into a stored procedure

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm

Database connection strings

Find it hard to remember database connection strings? Here is an easy reference of connection strings for all types of data sources and databases.

Select your database:

• MS SQL Server
• MS Access
• Oracle
• IBM DB2
• MySql
• Sybase
• Interbase
• Informix
• Excel
• Text
• dBase Dbf
• Visual FoxPro

Integrating Reporting Services into Your Application

Here is MSDN article on - Integrating Reporting Services into Your Application

Command line reference for Windows, Bash, Oracle, and SQL Server

Here is site which has Command line reference for Windows, Bash, Oracle, and SQL Server...

DB2 on Linux and Windows Resources

Resouces on DB2 on Linux and Windows

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:

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.

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.

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.