Transforming Business Logic into Web Services Using DB2 9.5 and IBM Data Studio

In the
first ten parts of
this series
, I’ve introduced you to some of the many features available
within the IBM Data Studio integrated
development environment (IDE), which can be used with any of the mainstream
relational IBM data servers. Specifically, I’ve
shown you how to set up and use database connection objects, how to generate an
overview diagram of your database architecture, how to build OLE DB functions
that can be used to easily integrate data from external data sources that have
an OLE DB provider, and how to create an SQL statement using either the SQL
Builder or the SQL Editor in IBM Data
Studio. Finally, I showed you how to take the FEMALEPERSONNEL SQL statement
built in previous installments and quickly turn that into a stored procedure
using the New Stored Procedure wizard in IBM
Data Studio.

In this
part, I’m going to show you how to take business logic such as stored
procedures, functions, or SQL statements (often referred to as routines)
and turn them into a Web service.

An introduction to IBM Data Web Services

If you open any magazine, surf any
Web site, or attend any conference, you’d be hard-pressed to avoid hearing or
reading about Web services. They’ve been around for some time now and are used
to create loosely coupled applications. The specifications and standards
associated with Web services such as SOAP and the Web Services Description Language (WSDL) are well defined and
mature. As companies move their IT applications towards service-oriented
architecture (SOA), there is often a need to expose underlying application
functionality and business logic stored within a data server as a Web service (for
example, the SQL statement and stored procedure created so far in this series).
While Web service enablement itself is not equal to SOA, Web services are vital
components towards making SOA possible.

IBM Data Web Services (IBM DWS) is a technology that revolutionizes the way
you can expose database business logic as services for use in SOA applications.
Using IBM DWS with IBM data servers, you can easily provision
encapsulated business logic such as SQL scripts and routines as Web services
with mere clicks of a button. That’s right; you can literally take any SQL
statement or routine, right-click, and generate invocations for JMS, SOAP, or REST Web service end points,
which can then be used in building loosely coupled applications. Furthermore,
you can group multiple database operations into one or more services that can
be deployed and run on a myriad of supported application servers and invoked by
any client with a Web browser.

To appreciate just how much IBM DWS simplifies your path to SOA applications, you
need to understand why the IBM DWS
technology was invented, and what creating a Web service was like using
previous versions of DB2 software.

One major problem in the
large-scale adoption of DB2 Web services and the migration of legacy
applications to these services has been the lack of a good foundation and
toolset to quickly create and provision them. For example, earlier versions of
DB2 software used the WebSphere Object Runtime Framework (WORF) to enable
service operations on top of DB2. Although WORF does the job, the effort that
went into the definition and development of these Web services was cumbersome;
specifically, it involved creating custom Data Access Definition Extension
(DADX) files, which were complex and not based on any standards. In addition,
the WORF framework was specific to the DB2 family of products and wasn’t well
supported for other IBM data servers such as Informix
Dynamic Server.

IBM DWS was introduced when DB2 9.5 became generally
available. You might have noticed that this Web service infrastructure is aptly
named IBM Data Web Services. There’s no DB2 in that title;
that’s because this technology can be used across of all the mainstream IBM data servers: DB2 for Linux, UNIX, and Windows,
DB2 for z/OS, Informix Dynamic Server, and DB2 for IBM i (formerly known as IBM i5/OS). What’s more, it’s applicable to back-level
versions of these data servers too! For example, you can expose business logic
on a DB2 Universal Database Version 8.2 for Windows database as a Web service
using this framework.

The best part about the IBM DWS technology (as you’re going to find out) is
that it provides a framework to transform business logic into a Web service without
writing a single line of code! The IBM
DWS technology is fully integrated into IBM
Data Studio, which makes it easy to create bottom-up Web service development
(auto WSDL generation, XML mapping, type mapping, and so on).

Specifically, IBM DWS provides the
following features for IBM data servers:

  • Single-click
    creation capabilities that include a drag-and-drop deployment infrastructure
    (there’s a new folder for Web services in IBM
    Data Studio) without any programming
  • Full Web
    service interface support such as SOAP over HTTP, automatic WSDL generation, and more
  • REST-style
    service interface with HTTP GET/POST bindings
  • Web 2.0
    capabilities (for example, JSON, RSS, Feeds, AJAX with XSLT, and so on)
  • The capability
    to expose legacy application components as services

The path to Web services starts and
ends in IBM Data Studio. For example, using IBM Data Studio
you first create a data development project, which acts as a container for the
Web services work you’re going to create, such as SQL scripts, routines, Web
services, and XML artifacts, as you’ve done thus far in this series.

Each data
development project is linked to a database connection involving any of the
following IBM data servers: DB2 for Linux,
UNIX, and Windows (Version 8 or later), DB2 for z/OS (Version 7 or later), DB2
for i (Version 5 or later), and Informix Dynamic Server (Version 10 or later).

You can optionally deploy your Web
services to a myriad of provisioning tiers. For example, WebSphere Application
Server Community Edition (Application Server/CE) is included as part of an IBM Data Studio download. IBM DWS can also be deployed through IBM Data Studio to other popular application servers
such as IBM WebSphere Application Server, Apache
Tomcat, and even the IBM Data Power SOA
Appliance
. IBM
Data Studio also gives you the option to generate a Web Archive (WAR) file for
deployment to Web teams.

IBM DWS supports SOAP, JMS, and REST end points for Web services. The variety of end points
and supported styles offers plenty of options in terms of clients that can be
used to invoke these services. When you create a Web service in IBM Data Studio, the default selection is to generate SOAP and REST services; while
deploying your Web services, you can select which services to generate according
to your application requirements. If you generate SOAP/HTTP style services, the XML
request document is contained inside the SOAP body element of the SOAP
request message. The IBM DWS REST style interface supports
the following request types: HTTP POST with an XML request document, HTTP GET with input parameters in a URL, and HTTP POST with
URL-encoded parameters in the request document.

IBM Data Studio also provides XSLT features that you can
use to apply style sheet transformations to each service operation; you’d use
this option to customize both input and output streams to and from the service.
For example, you can have the output of a service delivered to your client in
XML, and use an XSLT transform to fine-tune the display results to match the
client’s form factor. The ability to define XSLT transformations in IBM Data Studio for your Web services is very useful
for a wide array of applications beyond formatting. For example, you can use it
to customize your Web service to conform to an existing WSDL document. The
retrieved XML format from IBM DWS might
not be the desired one for your other applications; the right format depends on
the application or existing IT infrastructure and the nature of the project.
XSLT can be used to map both input and output of DWS to existing XML schema
specifications.

What’s more, you no longer need to
choose between top-down or bottom-up approaches to building Web services since
the bottom-up services generated by IBM
DWS can be mapped to the top-down format requirements on the client. You can
also use this feature to retrieve results in formats such as JSON, or generate feed formats such as RSS, based on the database content.

Finally, IBM DWS comes with a
number of testing tools that you can use to quickly perform sanity and quality
assurance testing for your Web services. At this point, we’ve spent enough time
talking about IBM DWS; it’s time to start using it.

Things you have to do to follow the examples in this article…

If you’re just joining this
series, the good news is that you don’t have to do much to catch up. To follow
along, you need to know how to create database connection objects, work with
those connections, create database development projects, and create the SQL statement
shown below:

SELECT PAULZ.DEPARTMENT.LOCATION, PAULZ.EMPLOYEE.EMPNO, 
 PAULZ.EMPLOYEE.FIRSTNME, PAULZ.EMPLOYEE.LASTNAME, 
 PAULZ.EMPLOYEE.PHONENO
FROM PAULZ.DEPARTMENT, PAULZ.EMPLOYEE
WHERE PAULZ.DEPARTMENT.DEPTNO = PAULZ.EMPLOYEE.WORKDEPT 
AND PAULZ.EMPLOYEE.SEX = 'F' 
ORDER BY LASTNAME DESC, FIRSTNME DESC

These prerequisite tasks are described in part 1
and part
2
. This query returns a predefined number of attributes that are joined
from the EMPLOYEE and DEPARTMENT tables for all female employees registered for
a fictitious company.

In part 10
of this series, I showed you how to turn this FEMALEPERSONNEL SQL statement
into a stored procedure. Now we are going to wrap a Web service around this SQL
statement, which you created earlier in part 7
of this series; however, you could also wrap the Web service around the stored
procedure you created based on this SQL statement (and they would perform the
same operations).

If you’ve followed along in this
series, all of this logic should reside within the SAMPLE database (created
using the db2sampl –xml –sql command) and in a database development
project called DatabaseJournalProject.

If you’ve met all of these
requirements, your IBM Data Studio environment should
look similar to this:

IBM Data Studio environment

Paul Zikopoulos
Paul Zikopoulos
Paul C. Zikopoulos, BA, MBA is the Program Director for the DB2 Evangelist team at IBM. He is an award-winning writer and speaker with more than 14 years of experience with DB2. Paul has written more than 230 magazine articles and 11 books on DB2 including, Information on Demand: Introduction to DB2 9.5 New Features, DB2 9 Database Administration Certification Guide and Reference (6th Edition), DB2 9: New Features, Information on Demand: Introduction to DB2 9 New Features, Off to the Races with Apache Derby, DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Clusters) and a DB2 Certified Solutions Expert (BI and DBA). In his spare time, he enjoys all sorts of sporting activities, including running with his dog Chachi, avoiding punches in his MMA training, and trying to figure out the world according to Chloë - his daughter.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles