IBM Data Studio Data Web Services, Part 1: IBM Data Studio: Get started with Data Web Services

Develop your first IBM Data Studio Data Web Service

The IBM® Data Studio solution includes many new features, among these is the Data Web Services support. Data Web Services is the next generation Web service provider for IBM database servers. Data Web Services provides a full Web service interface, that supports both REST-style (HTTP GET/POST binding) as well as SOAP over HTTP and automatic WSDL generation. Data Web Services supports the IBM DB2® family of databases, IBM Informix® Dynamic Server (IDS), and many popular Web servers, such as Apache Tomcat v5.5, and IBM WebSphere® Application Server V6.1 and Community Edition V1.1.

Share:

Michael L. Pauser (pauser@us.ibm.com), Developer, IBM

Michael is the technical lead for Data Studio's Data Web Services UI. He has extensive experience in UI development for both client-side applications as well as Web-based applications.



December 2008 (First published 08 November 2007)

Also available in Vietnamese

Before you start

Learn what to expect from this tutorial, and how to get the most out of it.

About this tutorial

This tutorial provides a step-by-step guide to creating your first Data Web Service using IBM Data Studio.

Objectives

In this tutorial, you learn how do the following:

  • Create a database connection
  • Create a Data development project
  • Create an SQL script
  • Create a Web service
  • Add SQL scripts
  • Add stored procedures to the Web service
  • Add a Web server
  • Deploy a Web service
  • Test the Web service

Prerequisites

This tutorial assumes that you have the following products installed:

  • IBM DB2 9.5 for Linux, Unix, and Windows. However, you can use any supported Data Web Service DB2 database. In addition, you must have the DB2 SAMPLE database installed. If you do not have the SAMPLE database installed, refer to your DB2 Information Center for instructions on installation.
  • IBM Data Studio V1.1. During installation, ensure that the Data Web Services Development component is selected.
  • IBM WebSphere Application Server Community Edition V1.1, a free Web server from IBM. A WebSphere Application Server Community Edition installation image may be bundled with your IBM Data Studio installation. If not, refer to the WebSphere Application Server Community Edition Web page for additional information or to download the WebSphere Application Server Community Edition installation image.

Create a database connection

Before you can create a Web service, you first need to create a connection to your database.

  1. From the IBM Data Studio Database Explorer, expand the Connections node. If you see an existing connection to your SAMPLE database, then proceed to the Create a data development project section.
  2. Select the Connections node, right-click and select New Connection… This launches the New Connection wizard.
  3. Complete the New Connection wizard by providing the required information. In most cases, you will only need to modify the Host, User ID, and Password values.
    Figure 1. Connection parameters
    Connection parameters
  4. You can optionally test your connection by clicking Test Connection.
  5. Click Finish. The New Connection wizard closes and your newly created SAMPLE database connection appears in the Database Explorer.
    Figure 2. New SAMPLE database connection
    New SAMPLE database connection

Create a data development project

Now that you have created a connection to your SAMPLE database, you need to create a data development project. Data development projects are used for database application development. This type of project is associated with a single connection in the Database Explorer. You can use data development projects to develop the following resources:

  • Develop, test, and deploy SQL and Java™ stored procedures and user-defined functions.
  • If the target server supports XML, you can develop XML files and artifacts for XML applications.
  • Develop and test SQL queries.
  • Develop and deploy Web services that access data by using SQL scripts or stored procedures.

Follow these steps to create a data development project:

  1. From the IBM Data Studio menu bar, navigate to File > New > Data Development Project. This launches the New Data Development Project wizard.
  2. Change the Project name field to Demo.
    Figure 3. Data development project
    Data development project
  3. Click Next.
  4. Select the existing SAMPLE connection.
    Figure 4. Select connection
    Select connection
  5. Click Finish. A new data development project named "Demo" is shown in the Data Project Explorer.
  6. Select the existing SAMPLE connection.
    Figure 5. New demo data project
    New demo data project

Create an SQL script or stored procedure

Web services are comprised of SQL scripts and stored procedures that you want to expose as Web services. To create an SQL script, follow these steps:

  1. From the Data Project Explorer, select the SQL Scripts node, right-click and select New > SQL or XQuery Script. This launches the New SQL or XQuery Script wizard.
  2. Change the Name field to getAllEmp.
    Figure 6. Name and tool
    Name and tool
  3. You have two different editor options. The SQL editor provides a context-sensitive text area for you to enter your SQL statement. The SQL build provides an interactive method for constructing your SQL statement. In this example, use the default option SQL editor.
  4. Click Finish. The wizard closes and a new tab named "getAllEmp.sql" appears, containing the context-sensitive text area for you to enter your SQL statement.
  5. In the getAllEmp.sql tab text area, enter the following SQL statement:
    select * from employee
    Figure 7. getAllEmp.sql
    getAllEmp.sql
  6. Finally, save your SQL statement by selecting File > Save or pressing CTRL+S. Your SQL script getAllEmp.sql now appears in the Data Project Explorer under the SQL Scripts node.
    Figure 8. SQL Scripts: getAllEmp.sql
    SQL Scripts: getAllEmp.sql

Creating stored procedures is done in much the same fashion. From the Data Project Explorer, select the Stored Procedures node, right-click and select New > Stored Procedure. The New Stored Procedure wizard appears and guides you through the process of creating a stored procedure. However, for this example the stored procedure you expose already exists in the SAMPLE database — so you do not need to create a new stored procedure.


Create a SQL Web service

Now that you have created an SQL script, you can create a Web service.

  1. From the Data Project Explorer, select the Web Services node, right-click and select New Web Service.... This launches the New Web Service wizard.
  2. Change the Name field to MyWebService.
    Figure 9. Define a new Web service
    Define a new Web service
  3. Click Finish. Your newly created Web service "MyWebService" is listed under the Web Services node in the Data Project Explorer.
    Figure 10. New MyWebService in Data Project Explorer
    New MyWebService in Data Project Explorer

Add an SQL script to a Web service

To add an SQL script to your Web service, follow these steps:

  1. From the Data Project Explorer, select the SQL Script. In this example, select getAllEmp.sql.
  2. Drag-and-drop getAllEmp.sql onto the Web service MyWebService.
  3. Your Web service MyWebService now has an operation named "getAllEmp."
    Figure 11. New operation: getAllEmp
    New pperation: getAllEmp

Add a stored procedure to a Web service

Adding a stored procedure to a Web service is done in much the same way as adding an SQL script. If your Data Project Explorer Stored Procedures node had one or more stored procedures, you could simply select the stored procedures and drag-and-drop them onto the desired Web service. However, in this example you are going to use a stored procedure that is deployed to your SAMPLE database, but does not exist in your Data Project Explorer. To add a deployed stored procedure to our Web service, follow these steps:

  1. From the Data Project Explorer, expand SAMPLE > SAMPLE > Schemas > {schema id} > Stored Procedures. In this example, the {schema id} is DB2ADMIN — your schema ID may be different. You should see a stored procedure named "BONUS_INCREASE." If you do not see this stored procedure, then perhaps you created the SAMPLE database using a different schema ID.
    Figure 12. Database Explorer: BONUS_INCREASE stored procedure
    Database Explorer: BONUS_INCREASE stored procedure
  2. Drag-and-drop BONUS_INCREASE onto the Data Project Explorer Web service MyWebService.
  3. Your Web service MyWebService now has an operation named "BONUS_INCREASE."
    Figure 13. New operation: BONUS_INCREASE
    New operation: BONUS_INCREASE

Add a Web server instance

A Web server instance is simply a definition to an existing Web server. If this is your first Web service, chances are you do not have any Web server instances defined in your IBM Data Studio workspace. To add a Web server instance to your workspace, follows these steps:

  1. From the IBM Data Studio menu bar, navigate to Window > Show View > Other... > Server, and select the Servers node.
    Figure 14. Show view
    Show view
  2. Click OK. A Servers tab now appears in your workspace.
    Figure 15. Servers tab
    Servers tab
  3. From the Servers tab, right-click in the whitespace and select New > Server. The New Server wizard appears.
  4. On the Define a New Server page, select the desired Data Web Services supported Web server. In this example, select WebSphere Application Server Community Edition V1.1.
    Figure 16. Define a new server
    Define a new server
  5. Click Next.
  6. On the Runtime page, specify the directory where your existing Web server is installed. In this example, the WebSphere Application Server Community Edition V1.1 Web server is installed in c:\wasce\.
    Figure 17. New WebSphere Community Edition Server V1.1 runtime
    New WebSphere Community Edition Server V1.1 runtime
  7. Click Next. Note: It is very important that you click Next through the remaining New Server wizard pages. Failure to do so may result in your Web server instance being invalid.
  8. The Server page lists various ports and the administrator user ID and password. In this example, just accept the defaults.
    Figure 18. New WebSphere Community Edition Server V1.1 Server
    New WebSphere Community Edition Server V1.1 Server
  9. Click Next.
  10. From the Add and Remove Projects page, click Next.
  11. Finally, on the Select Tasks page, click Finish to create the Web server instance. The Servers tab now lists your Web server instance.
    Figure 19. New WebSphere Application Server Community Edition V1.1 server instance
    New WebSphere Application Server Community Edition V1.1 server instance
  12. Test that your Web server instance is valid by starting the instance. To do so, select the Web server instance, right-click, and select Start. The Web server instance is started and should display a Started status.
    Figure 20. New WebSphere Application Server Community Edition V1.1 Server instance started
    New WebSphere Application Server Community Edition V1.1 Server instance started

Deploy a Web service

Now that you have the Web service MyWebService, with two operations BONUS_INCREASE and getAllEmp, and a Web server instance, you are ready to deploy MyWebService to your WebSphere Application Server Community Edition V1.1 Web server instance. To deploy a Web service, follow these steps:

  1. From the Data Project Explorer, select the Web service to deploy. In this example, select MyWebService. Right-click and select Build and Deploy... The Deploy Web Services wizard appears.
    Figure 21. Deploy Web service
    Deploy Web service
  2. Select the Web server type and instance. In this example, you want to deploy to a Web server type of WebSphere Application Server Community Edition V1.1.
  3. Select the WebSphere Application Server Community Edition V1.1 Web server instance by selecting the Web server Server radio button. This specifies that deployment is to be done to the selected Web server. Since you have previously defined your Web server instance, it appears in the Server drop-down box.
    Figure 22. Select Web server
    Select Web server
  4. Next, select the desired message protocols — either Web-access (REST-style), Web service (SOAP), or both REST-style and SOAP. In this example, select both.
    Figure 23. Select message protocols
    Select message protocols
  5. The Parameters section is used to specify additional properties for the Web service. In general, you can ignore this section. However, if you want to use a specific context root or a different SOAP engine, then you may use this group to modify those values.
    Figure 24. Parameters
    Parameters
  6. Additional options during deployment are available:
    • By selecting the Register database connection with Web server check box, you are specifying that Data Web Services should automatically register your selected database connection with the Web server. In this example, select this check box. If you uncheck this option, you must manually create the database pool in the Web server as well as complete the additional properties that appear in the Parameters group.
      Figure 25. Register database connection
      Register database connection
    • IBM Data Studio includes a Web Services Explorer plug-in that allows you to view and test Web services. During Data Web Services deployment, you can select the Launch Web Services Explorer after deployment check box. Upon completion of deployment, the Web Services Explorer is automatically launched.
      Figure 26. Launch Web Services Explorer
      Launch Web Services Explorer
  7. Click Finish. The Web service deployment process begins. Upon completion, the "dirt" flag (*) of MyWebService is reset and MyWebService lists the deployed Web server instance in its node label.
    Figure 27. Deployed Web service
    Deployed Web service
  8. Since Launch Web Services Explorer after deployment was selected, the Web Services Explorer appears.
    Figure 28. Web Services Explorer
    Web Services Explorer

Test deployed Web services using Web Services Explorer

Using the Web Services Explorer, you can test your deployed Web services. Remember, during deployment you can select the Launch Web Services Explorer after deployment check box to launch the Web Services Explorer. Additionally, you can select a Web service, right-click and select Launch Web Services Explorer. Using either method, the Web Services Explorer tab appears.

Figure 29. Web Services Explorer
Web Services Explorer

Shown in the Bindings area are the Web service bindings. Since you had selected the Web access (REST-style) and Web service (SOAP) message protocols during deployment, your bindings are listed: REST-style consists of HTTP GET and HTTP POST type and SOAP consists of the SOAP type. To test a Web service binding type, follow these steps:

  1. Click MyWebServiceSOAP binding. The WSDL Binding Details screen is displayed.
    Figure 30. WSDL Binding Details screen
    WSDL Binding Details screen
  2. Under Operations, you see your two Web service operations — BONUS_INCREASE, which represents your stored procedure, and getAllEmp, which represents the SQL script. Click BONUS_INCREASE.
  3. From Invoke a WSDL Operation screen, you see that your BONUS_INCREASE operation has two import parameters: P_BONUSFACTOR and P_BONUSMAXSUMFORDEPT. For P_BONUSFACTOR, specify a value of 1.1. For P_BONUSMAXSUMFORDEPT, specify a value of 1000.
    Figure 31. Invoke a WSDL Operation screen
    Invoke a WSDL Operation screen
  4. Click Go.
  5. The Status area displays the results of executing the operation. By double-clicking the Status title bar, you can maximize this area.
    Figure 32. Web Services Explorer Status
    Web Services Explorer Status
    Double-click the Status title bar again to return this pane to its normal size. Other options include clicking the Source hyperlink to see the SOAP request and SOAP response envelopes.

Test a Web service using a Web browser

The Web service WSDL file contains, among other details, the various invocation points for a Web service. You can access the contents of the WSDL file by accessing the following URL from any Web browser. In this example, since you deployed to a local WebSphere Application Server Community Edition V1.1 Web server, you open the URL http://localhost:8080/DemoMyWebService/wsdl.

Near the bottom of the WSDL is a <wsdl:service name=”MyWebService”>. This section defines the various HTTP locations and invocation points for your Web service.

Figure 33. MyWebService WSDL
MyWebService WSDL

In the above <http:address location=…, you can see the various invocation points. For SOAP binding, the URL format is http://{host}:{port}/{project name}{Web service name}/services/{Web service name}. For REST GET and POST bindings, the URL format is http://{host}:{port}/{project name}{Web service name}/rest/{Web service name}. In this example, host=localhost, port=8080, project name=Demo and Web service name=MyWebService.

To execute the BONUS_INCREASE operation as a REST-style, you specify: http://localhost:8080/DemoMyWebService/rest/MyWebService/BONUS_INCREASE?P_BONUSFACTOR=1.1&P_BONUSMAXSUMFORDEPT=1000, where P_BONUSFACTOR=1.1 is the first input parameter value of 1.1 and P_BONUSMAXSUMFORDEPT=1000 is the second input parameter value of 1000.

The result of executing the BONUS_INCREASE operation is then displayed in the Web browser.

Figure 34. MyWebService REST-style
MyWebService REST-style

Conclusion

With IBM Data Studio, whether leveraging your existing SQL scripts and stored procedures or creating new scripts and stored procedures, exposing these through a Data Web Service is easy to do. Using simple drag-and-drop, you can add any number of SQL scripts or stored procedures to a Data Web Service. When ready to deploy, with one-click you can deploy those operations to a Web server and expose them as Web services.

Future articles will discuss applying XSLT to both input and output messages, security, support for the other supported Web servers, creating clients that consume Web services, and other Web 2.0 topics.

Resources

Learn

Get products and technologies

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=267721
ArticleTitle=IBM Data Studio Data Web Services, Part 1: IBM Data Studio: Get started with Data Web Services
publish-date=12082008