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

Learn how to take business logic such as stored procedures, functions, or SQL statements and turn them into a Web service.

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:

Building your first Web service

To transform the FEMALEPERSONNEL SQL statement into a Web service, perform the following steps:

Web server

Data handler

Message protocols

Parameters

Test

8.  The Web Services Explorer window opens.

Test a SOAP invocation of the Web service as follows:

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 server’s 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 didn’t 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, I’m 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

Trademarks

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.

Disclaimers

The opinions, solutions, and advice in this article are from the author’s 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 author’s knowledge at the time of writing.