Data Web Services: Build Web services the new way to access IBM database servers

Service-enabling your access to DB2 and Informix Dynamic Server

IBM® Data Studio is a unified tooling platform that contains a comprehensive set of features to develop, administer, and manage your database servers. The development IDE of Data Studio packs in a number of features to assist in all your data related development needs. An integral component of this product is the capability to generate Web services-based access to your database. In this article, get an overview of this exciting new technology called Data Web Services, and step through some of its features and capabilities.

Share:

Vijay Bommireddipalli (vijayrb@us.ibm.com), Data Server Solution Architect, IBM

Vijay Bommireddipalli photoVijay Bommireddipalli is a solution architect in the Data Servers Solutions team at IBM's Silicon Valley Lab in San Jose, CA, where he enables customers and partners on IBM data server technologies. Prior to joining this team, Vijay was a developer on the Warehouse Manager development team. He joined IBM in July 2000, after finishing his Masters degree in Electrical and Computer Engineering at University of Massachusetts - Dartmouth.



06 December 2007

Also available in Russian Vietnamese

Introduction

Web services have been established as a mainstream mechanism for data exchange, access, and manipulation. The underlying standards are mature, and some of the benefits, like loose coupling and virtualization, goes a long way towards achieving a Service-Oriented Architecture. A number of surveys indicate implementation difficulties as one of the main causes of delay or sometimes even failure of SOA projects. Many data-oriented Web services do one thing –- execute a statement against a database, or call a stored procedure, and, yet, you need to wrap these database operations in application code logic to accomplish the same. Developing data-oriented Web applications can be a challenge since they require both Web development and database expertise. Data access developers who develop these data-oriented applications can attest to the fact that it typically requires wrapping DML (data manipulation language) statements in a fair amount of code. Moreover, the logic of the code is repetitive, like: opening connection, executing statement, processing result set, closing connection, and so on. What is desirable is if one can take existing database operations, and make them available as Web services without needing to do the above. This is where Data Web Services comes to the rescue.


What is Data Web Services?

Data Web Services (DWS) is the next generation solution to significantly ease the development, deployment, and management of Web services-based access to DB2 and IDS database servers. DWS lets you take DML statements (like Select, Insert, Update, Delete, and XQuery) and stored procedure calls, and generate Web services without writing a single line of code. DWS provides a full Web service interface, including support for SOAP and REST-styled bindings. All this is part of Data Studio Developer, which means you can develop Web services and database applications in one environment. The generated Web services are packaged in the form of a ready-to-deploy Web application, which can then be deployed to supported application servers.

Key aspects of DWS

  • Creating Web services using DWS requires no programming!
    • DWS lets you create Web services using a drag-and-drop interface: Drag and drop any DML operation or stored procedure call into a Web service container to create ready-to-deploy Web services.
    • DWS also supports an integrated test environment that lets you deploy and test the generated services using a few clicks of the mouse.
  • DWS supports SOAP over HTTP and WSDL generation.
    • DWS automatically generates a Web Services Description Language (WSDL) file that contains a description of the Web services.
  • DWS supports REST-style service interface.
    • In addition to SOAP over HTTP, DWS supports the HTTP GET/POST binding for provisioning REST-styled services to your database server.
  • DWS can apply server-side XSLT to incoming and outgoing XML service requests and responses.
    • DWS lets you apply server-side XSLT to match any service format requirements that you may have. This has some interesting possibilities in the Web 2.0 world, as you will see in a later section.
  • No code generation!
    • DWS consists of a common metadata-driven run time, and there is no "black box" code that gets generated under the covers. This results in a reliable and lightweight application.

Developing Data Web Services

As shown in Figure 1, developing Data Web Services typically involves the following steps:

  1. Develop your SQL statement, stored procedure, or XQuery statement.
  2. Create a Web service.
  3. Drag and drop the required operations to this Web service.
  4. Deploy the service to the application server.
  5. Test the Web service using the integrated test environment or a Web service client.
Figure 1. Typical flow of development of Data Web Services
Typical flow of development of Data Web Services

Taking it step by step

This article is not a comprehensive tutorial, but let's look at the steps listed above in the context of an example.

Installation and setup

Note: The steps detailed here give you a better idea of the development process with Data Web Services. This is not a comprehensive tutorial with a complete description of the steps. To try out the following steps on your computer requires setting up IBM Data Studio and the test environment with IBM WebSphere® Application Server - Community Edition. It also requires DB2 with the sample database installed.

Check out the tutorial "IBM Data Studio: Get started with Data Web Services" (developerWorks, November 2007), a step-by-step guide on Data Web Services, and follow it to get up and running with your first Data Web Service.

The resources section also has links for free downloads of DB2 Express C, WebSphere Application Server - Community edition, and Data Studio. Check out the IBM Data Studio home page for additional information related to Data Studio.

  1. Start in the IBM Data Studio: Create a New Data Development Project. For this example, title the project Demo.
    Figure 2. Create a new Data Development Project in Data Studio
    New Data Development Project
  2. Next, create a simple SQL select statement that returns an employee record. You can use either the SQL editor/builder within Data Studio or simply copy-paste an existing statement into the editor. Data Studio provides an integrated query builder and can give context-based assistance (as seen in Figure 3) while typing the statement. Save this statement as getmyEmp.sql.
    Figure 3. Create statement getmyEmp.sql
    Context assist for SQL in Data Studio
  3. Now make this SQL statement available as a Web Service. To do so, right-click on the Web Services folder, select New Web Service, and name it myfirstService. This Web service, myfirstService, is just a container that will house the operations that are exposed as Web services.
    Figure 4. Web Service container
    Web Service container that will house the SQL operation
  4. Next, drag and drop the SQL statement that you created onto the Web service that has been created. The Web service myfirstService now contains the operation getmyEmp.sql. In this case, the operation is the SQL statement typed above (getmyEmp.sql); however, you can choose to drag more than one operation (including stored procedures) onto this Web service.
  5. Right-click on the Web Service myfirstService, and select Build and Deploy.
    Figure 5. Building and deploying the Web service onto WAS-CE
    Deploying the Web service to WAS-CE
  6. Next, select the WAS-CE server that you have installed on your system as the target to deploy your Web service. By default, Data Web Services generates both REST and SOAP binding. Of course, that can be changed to generate only one binding. Optionally, you can also select Launch Web Services Explorer after deployment so that Data Studio launches using the built-in Web services explorer to test the services.
  7. Finally, select Finish.

    Data Web Services creates a Web application and generates configuration files that are specific to the Web server you selected. The Web application is then deployed to your local WAS-CE server.
    Figure 6. Deploying the Web Service onto WAS-CE
    Deploying the Web Service to WAS-CE

    Note: You could have instead chosen to generate a Web application (.war) file and deploy it yourself. Data Web Services would then have generated a ready-to-deploy Web application specific to the application server using intelligent defaults. Of course, all of this can be fully customized before deploying.
  8. After deploying the Web service, Data Studio launches the Web services explorer, which you can use to test both REST and SOAP end points. Select the SOAP bindings, and specify a value for the empno parameter.

    Note: The select statement specified in this example has a parameter marker called :empno. This value is sent in as part of the Web service request message. The response of the Web service is displayed, as seen in Figure 7. You can click on the source link in the Web Services Explorer to view the source XML.
    Figure 7. Testing the Web service using Web Services Explorer
    Testing the Web service using Web Services Explorer
  9. To test the REST bindings, while you can use the Web Services Explorer to test the same, let's use a Web browser instead. Browsers have built-in support for GET. Point your browser to the URL shown in Listing 1 below. The Web server returns an XML response that contains the result of invoking the getmyEmp SQL statement with a value of 000130 for the empno parameter.
    Listing 1. Using the browser to test REST binding
    http://localhost:8080/DemomyfirstService/rest/myfirstService/getmyEmp?empno=000130

Architecture

This article mentioned earlier that Data Web Services does not require any programming to generate Web services; neither does it generate any code. So you may wonder -- what is the magic here, and how are the Web Services executed ?

Let's take a look at the architecture of DWS to understand what is happening under the covers:

DWS supports both SOAP and REST-styled end points, as you have seen. Incoming Web service requests are handled by code that is specific to end points. So, whenever a request comes in, as REST or SOAP, DWS processes it to produce a common representation of the request. This request is then processed by the common metadata-driven run time, which determines how to map the Web service message to a database request. There is no intermediate mapping of parameters and results in these Web service messages to Java data types as is typically done in Web service generation. Here, the XML data types in the Web service messages are directly mapped to the database data types. Skipping multiple mappings of data types from one format to another results in an efficient and better-performing application.

A Data Web Services application is packaged as a J2EE Web application, which can be deployed onto supported application servers. Deploying it in your environment is just like deploying any other Web application. You can take full advantage of infrastructure that you may already have in place. The generated application consists of a common run time JAR file and configuration files. These XML configuration files contain application server-specific information amongst other information. One of the configuration files contains information specific to the Web Service operations that you have selected. The run time dynamically determines how to execute the services based on DML operations that have been selected to be exposed as Web services. The common run time, in combination with this configuration file, creates a lightweight application that has a reliable and consistent execution behavior since there is no black-box code generated every time you expose new Web services. This common metadata-driven code results in a robust, yet lightweight run time.

Figure 8. Architectural overview of Data Web Services
Architectural overview of DWS

Customizing Web services using XSLT

An interesting and powerful feature of DWS is the capability to apply server-side XSL transformations to Web service requests and responses. This feature allows you to customize the format of the messages that the client sees. This is particularly useful in cases where enterprise or industry Web service format requirements dictate what the Web services messages should look like.

DWS has a default message format when it comes to Web service request and response messages. The message format and the XML tag names in those messages depends on the Web service operation name, resultset column-names, type of binding used, and so on. In some cases, you may not want the client to see the default tag names, either due to message format requirements, or for simply masking the default tag names. Using the server-side XSLT feature, incoming XML requests and outgoing results can be made to look different than the Data Web Services default message format.

In general, Web services can be built using either a top-down or bottom-up approach. In a top-down approach, you would start with a service specification (for example, a WSDL file ) and then implement the underlying code to match that specification. In a bottom-up approach, you would start with a Java bean or SQL statement and expose the functionality as Web services. In DWS, one starts with the DML operations (like queries) and exposes them as Web Services. So the development is certainly the bottom-up style of Web services. However, by applying XSLT to the service messages, in many cases, you can map the service format of the bottom-up style messages to the top-down design. This will alleviate some of the top-down service format requirements while allowing you to develop bottom-up Web services. The transformation can also be used to deliver data to the client in human readable formats like HTML, XHTML, or any text format that can result from an XSL transform.

Figure 9. Customizable delivery formats - Applying XSLT
Applying XSLT to input and output messages

SOA

When it comes to SOA environments, databases do not jump to the forefront of many people's minds. However, a key benefit of SOA is the re-use of existing components. Enterprises that have built rich business logic into their stored procedures and high-performance queries now have the capability to instantly make them available in an SOA environment using DWS. Re-using existing DML operations and stored procedures that have been developed over time to encapsulate business logic and are known to perform well will save you the headache of diagnosing potential problems in newly developed applications. Also, Web services virtualize access for any consumer, which makes it easier to plug your database server into an SOA environment.

However, merely Web service enabling does not solve anything and will not get you any closer to a Service-Oriented Architecture. SOA discussions must start at a business level and then arrive at components required to achieve that end. Business discussions to define composite, reusable services at a business level and a solid design are very much components of creating an SOA. Web services could then be a way to implement these services. The ease of development using Data Studio's integrated query and stored procedure development environment, coupled with the capability to expose these artifacts as Web services then help you achieve that goal.

Serving up data in a Web 2.0 world

The Web is undergoing one of the greatest transformations since its inception. Web applications that infuse human intelligence back into applications using items such tagging, social collaboration, and rich internet applications that interact with the back end asynchronously are changing end-user experience for the better. Collectively labeled as Web 2.0, these applications are changing the way Web sites deliver content to end users. While a detailed discussion of Web 2.0 is beyond the scope of this article, there are some technologies that are important enablers of Web 2.0-style applications. REST-styled Web services, standard feed formats like RSS and ATOM, and lightweight formats like JSON are some of the technologies that seem to be very prevalent and favored by Web 2.0 clients. Provisioning data to these Web 2.0 clients from data repositories is key to enabling the next generation of applications.

Data Web Services tooling inherently supports REST-styled services. DWS supports both GET and POST requests. As you saw in Listing 1, the URL encoded GET allows you to invoke the Web service and view the results in a browser. Similarly, URL encoded POST, which is widely used by Web forms, is also supported. HTTP POST XML binding is another binding that is supported to allow clients that like to send and receive user payload as XML. This is particularly useful with clients like XForms, AJAX clients, and so on. In summary, DWS supports three kinds of REST-styled bindings:

  • HTTP GET binding
  • HTTP POST binding
  • HTTP POST XML binding

Furthermore, the capability to apply server-side XSLT to the Web service messages can be used to deliver the result data in various Web 2.0 formats like JSON, RSS feeds, and so on.

Security

Trusted context

Check out the article "Use trusted context in DB2 client applications" (developerWorks, September 2006) to get a good understanding of trusted context and its capabilities.

Also, check out the "Enabling trusted context for DB2 databases" topic (IBM, WebSphere Application Server documentation, November 2007) for information on how to enable trusted context between WebSphere Application Server and DB2.

Security is always a concern when you enable Web services access to your database by means of Web services. Careful consideration must be given to what is exposed and with which binding when it comes to enterprise data. Data-oriented Web services exposed using Data Web Services are no different in that regards. The good news with Data Web Services is that the repository that serves up your data in this case (in other words, databases) have a robust security model that can enforce authentication and authorization, and give role-based access.

As mentioned earlier, with a DWS application, you get to take advantage of the mature J2EE infrastructure, and you can apply the same security policies that would apply to any Web application. In some cases, databases and application servers have tighter integration, in terms of security. For example, IBM WebSphere® Application Server and IBM DB2® have a feature called trusted context that provides end-to-end authentication from the client to the database. With Data Web Services, trusted context allows for a Web service client to authenticate using a userid, and it allows the database to service this request using the authority of the same userid. Trusted context, as a feature, is applicable to more than just Data Web Services. For more information, check out the sidebar "Trusted context."

In some cases, audit or security requirements mandate passing additional information in the Web service request other than the SOAP body content. The SOAP message protocol defines a SOAP header section, where such information can be passed. DWS allows the passing of security header tokens in the SOAP message headers. The SOAP headers are generally processed by the SOAP engine, but, besides that, DB2 allows tags to collect additional client information like the client user ID, application, accounting information, and so on.

Security header tokens and trusted context are important features to consider when choosing and implementing the security model for your Web services environment.


Conclusion

Data Web Services lets you take your database operations and easily make them available as services. It is an easy-to-develop, lightweight, yet robust solution to enable Web services access to databases. Your database server can now become a Web service provider to traditional SOA clients as well as Web 2.0 clients. Unlocking the data and business logic in your data repositories using standards-based Web services allows for a variety of data-consumption scenarios. Your database server can now actively participate in your SOA infrastructure.

Acknowledgements

The author would like to thank Michael Schenker for all the help with Data Web Services and illustrations. The author would also like to thank all his colleagues who patiently reviewed this article and gave valuable feedback.

Resources

Learn

Get products and technologies

  • IBM Data Studio: Download Data Studio for free.
  • DB2 Express-C: Now you can use DB2 for free. Download DB2 Express-C, a no-charge version of DB2 Express Edition for the community that offers the same core data features as DB2 Express Edition and provides a solid base to build and deploy applications.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management, SOA and web services
ArticleID=273573
ArticleTitle=Data Web Services: Build Web services the new way to access IBM database servers
publish-date=12062007