first ten parts of
this series, Ive 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, Ive
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
part, Im 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, youd be hard-pressed to avoid hearing or
reading about Web services. Theyve 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. Thats 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 wasnt well
supported for other IBM data servers such as Informix
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. Theres no DB2 in that title;
thats 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). Whats more, its 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 youre 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:
creation capabilities that include a drag-and-drop deployment infrastructure
(theres a new folder for Web services in IBM
Data Studio) without any programming
service interface support such as SOAP over HTTP, automatic WSDL generation, and more
service interface with HTTP GET/POST bindings
capabilities (for example, JSON, RSS, Feeds, AJAX with XSLT, and so on)
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 youre going to create, such as SQL scripts, routines, Web
services, and XML artifacts, as youve done thus far in this series.
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
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; youd 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
clients 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
Whats 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, weve spent enough time
talking about IBM DWS; its time to start using it.
Things you have to do to follow the examples in this article
If youre just joining this
series, the good news is that you dont 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
SELECT PAULZ.DEPARTMENT.LOCATION, PAULZ.EMPLOYEE.EMPNO,
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
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
If youve 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 youve met all of these
requirements, your IBM Data Studio environment should
look similar to this:
Building your first Web service
To transform the FEMALEPERSONNEL
SQL statement into a Web service, perform the following steps:
1. Right-click the Web Services folder
in the DatabaseJournalProject data development project, and select New Web
2. The New Web Service wizard opens. Type
a name for the Web service in the Name field, and then click Finish.
For this example, use SOA_FEMALEPERSONNEL, as shown below:
Since this Web service was created
from an existing data development project, the Project field is
automatically filled in for you. If you click the drop-down list for this
field, you can select to create your Web service in any existing project and
its associated database connection.
This is a very useful feature if
you want to deploy the Web service to a myriad of target data servers. For
example, perhaps your development environment is based on DB2 for Linux, but
you want to deploy the service-built application to a DB2 for z/OS data server.
This feature streamlines the deployment process; you can build and test in a Linux
environment and deploy to the DB2 for z/OS data server.
You can also use this wizard to
create a new data development project and link it to a different target IBM data server. Click New and follow the steps
in the Data Development Project wizard that you were introduced to at the start
of this series.
The Data Project Explorer view
should now look like this:
3. Select the FEMALEPERSONNEL
SQL statement and drag it onto the SOA_FEMALEPERSONNEL folder:
You should see that the selected SQL
statement has been added to the SOA_FEMALEPERSONNEL Web services
project, located in the Web Services folder.
If the logic you want to expose as
a Web service doesnt currently exist in your database or data development
project, you can define the logic for the Web service as you define it. Right-click
the Web service that you are creating, and select New Operation:
You get the content assist and
syntax colorization features of the SQL Editor (introduced earlier in this
series in part 7)
when you define business logic inline. I recommend that you always create a
separate SQL statement to add to a Web service. Doing this gives you a lot more
options to reuse and tune the SQL statement since it persists outside of the
Web service as its own artifact in a data development project. Whats more, using
this method gives you access to any of the wizards that assist you in building
4. Perform the following steps to create
a link to an application server where the Web service will be deployed:
a. Select the Servers tab,
right-click anywhere in the tabs white space, and select New>Server.
The New Server window opens.
b. Select the appropriate application
server. For this series, assuming you are using IBM Data Studio V1.2, select WebSphere Application Server Community Edition v1.1
Server, and click Next.
I selected WebSphere
Application Server Community Edition v1.1 Server because Im using IBM Data Studio Version 1.2. For IBM Data Studio V1.0, you would use WebSphere
Application Server Community Edition v1.0 Server. You can also see that
Ive got Apache Tomcat installed on my machine as well; I could choose to
deploy the Web service to this application server too (or a number of others
which arent installed on my system).
Note: This article assumes that you
are defining a link to a WebSphere Application Server/CE application that
resides locally on your workstation (the same one where the SAMPLE database was
created). When creating Web services with DB2 data servers, you dont need to
manually configure the target application server for use with IBM DWS; its done automatically for you. If youre using
DB2 for z/OS, DB2 for i, or an Informix Dynamic Server data server, you must
register the data server JAR files on the application server. Refer to the IBM
Data Studio help for more information.
In this article, I assume you have
a copy of Application Server/CE, which is included as part of the IBM Data Studio media, and that you know how to
install it on your server for simple testing purposes. I recommend using this included
copy since it is the correct version: there are compatibility requirements
between some versions of Application Server/CE and IBM Data Studio for IBM DWS.
In order to access the included
copy, you can either perform a Custom installation of IBM Data Studio and select Application Server/CE from
the list of installable components, or rerun the IBM Data Studio installation and just add this component. Both
these actions place an installation image of Application Server/CE on your
server; after the IBM Data Studio installation, you
just double-click the installation image to install the product. For more
information about installing Application Server/CE, click here.
You can use the Installed
Runtimes button (shown in the previous window) to manage, add, or search
your server for other supported application servers. You can use the Add function
in the Installed Runtimes window to download a copy of Application Server/CE if
you didnt include it in the installation of IBM Data Studio, as shown below:
c. Accept the default settings for
the application server, and click Finish.
Once the application server has
been added, the Servers tab should look similar to this:
Note that you can deploy existing
Web services to the newly defined application server during the definition of
the application server; to do this, you would click Next and add the
appropriate project to the Configured projects box using the Add button;
however, that task is outside the scope of this article.
5. To start the application server
where you want to deploy the SOA_FEMALEPERSONNEL Web service, right-click the
server, and select Start.
It might take a few minutes for Application
Server/CE to start. The Servers tab will give you visual clues as to the
state of the selected application server:
You can also look at the bottom-right
corner of the IBM Data Studio IDE for progress
Although I wont delve into the
details of application server management within IBM Data Studio, you should know that the Console tab
gives you granular information about the target application server:
6. Build the Web service by
right-clicking the SOA_FEMALEPERSONNEL Web service and selecting Build
7. The Deploy Web Service window opens.
Ensure that you select the same options as shown below, and then click Finish.
As IBM Data Studio builds and deploys the Web service to the
target application server, a progress window is displayed. (The example in this
article uses a simple Web service, which should take less than a minute to
You can click Run in Background
if you expect the deployment of your Web service to take a long time so
that you can continue working on other projects; this makes working in IBM Data Studio asynchronous in nature, which can give
you a big productivity boost when working with large projects or operations.
As you can see, the Deploy Web Service window
has a lot of options:
Use this box to select the target
application server where the Web service will be deployed. Specifically, the Server
radio button defines the actual application server where the Web service
will be deployed. (You can define multiple applications servers of the same
type.) If you have multiple application servers defined in the Servers tab,
you can select the target
application server from the Type drop-down list, as shown below:
Notice that DataPower is an
option? IBM Data Studio Version 1.2
introduced the option to specify the IBM
WebSphere DataPower XML Integration Appliance XI50 as a target Web server when
you are building Web services with IBM
Data Studio. When you specify DataPower as the target for your Web service, IBM Data Studio can only generate deployable XSLT files,
which you can then deploy to the DataPower server outside of the IBM Data Studio. The DataPower option is only supported
for DB2 databases even though IBM DWS
technology supports all mainstream IBM
relational data servers.
If you dont want to deploy and
test the Web service, you can select the Build deployable files only, do not
deploy to a Web server option and IBM
Data Studio will generate a WAR file that you can pass to the Web development
The Data handler box is
used to specify how the data will be handled. The default selection is JDBC.
If you are exposing logic built using the pureQuery API, you would use this drop-down list to declare the
kind of API the Web service is expecting to
use to retrieve the data:
The Register database connection
with Web server check box is available for DB2 data servers, and allows you
to deploy Web services to a target application server without any manual
The Message protocols box
allows you to select check boxes that correspond to the type of Web service you
want to create. For example, if you select REST (Web access) and SOAP over HTTP, IBM Data Studio will generate invocations for both service
styles. IBM Data Studio Version 1.2 added the
SOAP over JMS option, which is well suited for
enterprise-class applications that leverage Java Messaging Service (JMS) for
guaranteed message delivery.
The Parameters box shows
you the parameters that are part of the Web service build. This topic is
outside the scope of this article.
The Test box can be used to configure the type of
test interface that IBM Data Studio starts after
successfully building and deploying the Web service. As of IBM Data Studio 1.2, there are two types of test
clients: the IBM Data Web Services Test Client and
the Web Services Explorer. In IBM Data
Studio 1.1, the only option available was the Web Services Explorer.
The Web Services Explorer window opens.
Test a SOAP
invocation of the Web service as follows:
Expand the WSDL
Main tree and locate the SOA_FEMALEPERSONNELSOAP, as shown in
the previous figure.
Expand the SOA_FEMALEPERSONNELSOAP
group and select FEMALEPERSONNEL Web services and click Go.
(Notice that the Actions box changes when you select this Web service -
compare the Actions box in this step with the Actions box in the
The Status window shows the
results of the invoked Web service:
You can use the scroll bar in the Status
box to see all the results of the Web service.
If you were to run the FEMALEPERSONNEL
SQL statement in your DatabaseJournalProject, you will see that the result of
this query matches the output of the Web service (and it should because the Web
service you just created wraps this very SQL statement):
What about wrapping my stored procedures as Web services?
Earlier in this article, I mentioned that you could wrap Web services around stored procedures or SQL statements. In DB2 9.5 and IBM Data Studio Part 10: Building Stored Procedures I showed you how to create and deploy a stored procedure using the FEMALEPERSONNEL SQL statement built earlier in this series. The method by which you build and deploy a Web service that wraps a stored procedure is the same as shown earlier in this article for an SQL statement; the obvious difference is that you drag a stored procedure instead of an SQL statement.
If you completed all the steps in Part 10, when you expand the SAMPLE database in the Database Explorer view, the Stored Procedures folder should include the SP_FEMALEPERSONNEL stored procedure:
If you want to expose the SP_FEMALEPERSONNEL stored procedure through a Web service, just drag the stored procedure to the same folder you used for the SQL statement in Step 3 of this article. In this case, since you are working with a routine that has been deployed to a data server, expand that data servers connection object and locate the stored procedure in the Stored Procedures folder within the Data Project Explorer view:
To add this stored procedure to your Web service, you would simply drag the stored procedure to the SOA_FEMALEPERSONNEL folder, and then build and deploy the Web service in the same manner (and using the same options) as you did in Step 7.
Upon the successful redeployment of the Web service, you can now see that both the SQL statement and the stored procedure (which return the same result) are now part of the Web service and part of the Web Services Explorer:
You can see that the Navigator pane in the Web Services Explorer now includes the SP_FEMALEPERSONNEL stored procedure. If you wanted to test the Web service invocation of the stored procedure, you would simply follow the instructions in Step 8.
Wrapping it all up
In this article, I showed you how to take the FEMALEPERSONNEL SQL statement that you built earlier in this series and turn it into a Web service using mere clicks of a button. Did you notice that you didnt have to write a single line of code? In my opinion, IBM Data Web Services is truly unique in its capability and ease of use when trying to pursue a service-oriented architecture development paradigm.
I only showed you the most basic way to test a single (SOAP) invocation of your newly created Web service. In the next parts of this series, Im going to take you through the different test clients provided by IBM Data Studio for you to test your Web services, and explain how to test a RESTful invocation of your Web service directly using a Web browser.
» See All Articles by Columnist Paul C. Zikopoulos
IBM, DataPower, DB2, DB2 Universal Database, i5/OS, Informix, WebSphere, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the United States, other countries, or both.
Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.
Microsoft is a trademark of Microsoft Corporation in the United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other countries.
Other company, product, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2008.
The opinions, solutions, and advice in this article are from the authors experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the authors knowledge at the time of writing.