Create Web services for DB2 stored procedures using Rational Application Developer V6

Create DB2® stored procedures, and then expose them as Web services using IBM® Rational® Application Developer V6 or IBM WebSphere® Developer for zSeries® V6.

Share:

Introduction

The article "Creating Web Services for DB2 UDB for OS/390 Stored Procedures Using WebSphere Studio, Version 5" (developerWorks, April 2003) introduced you to creating Web services for stored procedures running on mainframe DB2 systems. Since then, the stored procedure Web service runtime has changed a bit, and the tooling support has also been greatly improved to make the development process easier.

This article demonstrates these improvements by building an example, creating a Web service for a Java™ stored procedure in DB2 Universal Database™ (UDB) for Linux®, UNIX®, and Windows® using Rational Application Developer V6. The same development process could also be used if you want to expose Web services stored procedures running on a mainframe using WebSphere Developer for zSeries V6.

Prerequisite

This article assumes you have read the article "Creating Web Services for DB2 UDB for OS/390 Stored Procedures Using WebSphere Studio, Version 5" and understand the DB2 Web service provider architecture.

To follow the example in this article, you need to have Rational Application Develoer or WebSphere Developer for zSeries v6 installed. You should also have access to a DB2 server and database. This article uses the default SAMPLE databases bundled with the DB2 installation.


Set up the WorkSpace

  1. Start Rational Application Developer. Open a new workspace, and close the welcome screen.
  2. Switch to J2EE perspective, and create a new enterprise application Db2service with just a Web module Db2serviceWeb.
    Figure 1. Create application with a Web module
  3. In Project Exlorer view, select Database Servers. Right-click, and select New Connection... Create a connection by specifying database manager and JDBC driver:
    Figure 2. Establish DB connection
  4. Select the right type for the database you are connecting to. Since we are using JDBC type 4 connection, select IBM DB2 Universal Driver, and specify the right server, port number, and database.
    Figure 3. Connection parameters
  5. Enter the user ID and password, press Test Connection, if it is successful, and then click the Finish button.

Create and test a Java stored procedure

Develop a Java stored procedure

In this section, we develop a simple Java stored procedure that does a query againt the EMPLOYEE table and returns a result set.

  1. In the Project Explorer view, expand Databases. Expand SAMPLE you just created, then expand the schema, and select Stored Procedures. Right-click on it, and select New->Java Stored Procedure.
    Figure 4. Create stored procedure I
  2. Give the Java stored procedure the name SELJAVA. Make sure build and enable for use in DADX Web service are selected, and click Next.
    Figure 5. Create stored procedure II
  3. Select Statement1, and click on SQL Assist.
    Figure 6. Create stored procedure III
  4. Select the table EMPLOYEE.
    Figure 7. Create stored procedure IV
  5. Select the columns you want to return in the result set.
    Figure 8. Create stored procedure V
  6. Figure 9 depicts how the select statement inside the stored procedure looks:
    Figure 9. Create stored procedure VI

    After reviewing, click Next.
  7. Leave the defaults for Jar ID and Java Package. Click Finish.
    Figure 10. Create stored procedure VII

Test the Java stored procedure

  1. To test the just created stored procedure by select it, right-click, and slect Run. You will see the following output:
    Figure 11. Test stored procedure
  2. At this point, the Java stored procedure is tested and ready to be used in our project. In Project Explorer view, expand Database Servers, and expand the connection Con1 you just created. Select the database SAMPLE, and right-click on it to select Copy to Project.
    Figure 12. Copy stored procedure to Web project I
  3. Click the Browse button, and navigate to the Web project Db2serviceWeb you created before. Put this database information under WebContent/WEB-INF/db directory, and select Finish.
    Figure 13. Copy stored procedure to Web project II

Develop a Web service for the Java stored procedure

Create a DADX group

  1. In Project Explorer view, expand Dynamic Web Project, and select Db2serviceWeb. Right-click, and select New > Other to bring up the wizard:
    Figure 14. Create DADX group I
  2. Expand Web services, select Web Service DADX Group Configuration, and click Next.
  3. Click on Add group, and give it the name sampleDB. Click OK, and then click Next.
    Figure 15. Create DADX group II
  4. Select the newly created group, sampleDB. Click on Group properties, and enter the values, as shown in the dialog box:
    Figure 16. Create DADX group III
  5. Click OK, and then click Finish to exit this wizard.

Create a DADX file

  1. Select the Web project Db2serviceWeb. Right-click, and select New > Other...
    Figure 17. Create DADX file I
  2. Select DADX File, and click Next.
  3. Make sure your Web project and DADX group are selected. Give the file the name selEmp.dadx, and choose the radio button Create DADX file from stored procedures, then click Next.
    Figure 18. Create DADX file II
  4. In this window, navigate the Db2serviceWeb Web project down to locate and select the newly created stored procedure SELJAVA.
    Figure 19. Create DADX file III
  5. Click Finish to exit and create the file.

Now let's look at the selEmp.dadx file:

Listing 1. Generated DADX file
<?xml version="1.0" encoding="UTF-8"?>
<dadx:DADX xmlns:dadx="http://schemas.ibm.com/db2/dxx/dadx"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
      xmlns:xsd="http://www.w3.org/2001/XMLSchema"
      xsi:schemaLocation="http://schemas.ibm.com/db2/dxx/dadx dadx.xsd">
    <dadx:result_set_metadata  name="resultSet1Metadata" 
    rowName="resultSet1MetadataRow">
        <dadx:column name="EMPNO" 
            type="CHAR" 
            nullable="false" 
            as="EMPNO" />
        <dadx:column name="FIRSTNME" 
            type="VARCHAR" 
            nullable="false" 
            as="FIRSTNME" />
        <dadx:column name="LASTNAME" 
            type="VARCHAR" 
            nullable="false" 
            as="LASTNAME" />
        <dadx:column name="SEX" 
            type="CHAR" 
            nullable="true" 
            as="SEX" />
        <dadx:column name="JOB" 
            type="CHAR" 
            nullable="true" 
            as="JOB" />
    </dadx:result_set_metadata>
    
    <dadx:operation name="SELJAVA">
        <dadx:documentation xmlns="http://www.w3.org/1999/xhtml">
            <![CDATA[
            
            ]]>
        </dadx:documentation>
        <dadx:call>
            <dadx:SQL_call>
                <![CDATA[
                CALL WASADMIN.SELJAVA()
                ]]>
            </dadx:SQL_call>
            <dadx:result_set name="resultSet1" metadata="resultSet1Metadata"/>
        </dadx:call>
    </dadx:operation>
</dadx:DADX>

As you can see here, the DADX file defines an operation element that will call the stored procedure, SELJAVA . It will return a result set, which is also described by a resultset metadata element. It's nice that this file can automatically be generated using tools now. You don't need to fully understand its syntax and manually edit it, as was the case in provious versions of the tool.

Create a Web service from DADX file

  1. Bring up the New.. dialog box again, and select the Web Service wizard, then click Next.
    Figure 20. Create DADX service I
  2. Choose the Web service type to be DADX Web services. Click the check boxes as shown below, then click Next.
    Figure 21. Create DADX service II
  3. In the Object Selection Page, navigate down the Web project to select the file selEmp.dadx. Click OK, and then click Next.
    Figure 22. Create DADX service III
  4. Choose Apache Axis 1.0 as the Web service runtime and WebSphere V6 as the application server in the service deployment configuration page, then click Next.
    Figure 23. Create DADX service IV
  5. The data in this page is populated from the DADX group properties you configured in previous step
    :
    Figure 24. Create DADX service V
  6. Click Finish, and let the code generation process finish.

Test the stored procedure Web service

  1. Once the wizard finishes, the Web services explorer will be open.
    Figure 25. Test DADX service I
  2. In the left pane of the explorer, expand theSoapBinding, and select SELJAVA. In the right pane, make sure SOAP endpoint is picked, and click GO.
    Figure 26. Test DADX service II
  3. The stored procedure service is invoked, and resultset is brought back and displayed below:
    Figure 27. Test DADX service III
  4. If you are not convinced yet that a SOAP call has been made, just click on the Source link in the screen:
    Figure 28. Test DADX service IV


    What you see here is the raw request and response SOAP messages exchanged over the wire.

Conclusions

You have successfully created a Web service for a Java stored procedure without writing a single line of Java code. Improved tooling support makes this happen. This feature has great appeal to Web service developers, especially those with limited Java experience.

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software , available for download directly from developerWorks.
  • 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 Edtion and provides a solid base to build and deploy applications.

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, Rational, WebSphere, DevOps
ArticleID=109781
ArticleTitle= Create Web services for DB2 stored procedures using Rational Application Developer V6
publish-date=04272006