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.
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
- Start Rational Application Developer. Open a new workspace, and close the welcome screen.
- 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
- 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
- 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
- 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.
- 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
- 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
- Select Statement1, and click on SQL Assist.
Figure 6. Create stored procedure III
- Select the table EMPLOYEE.
Figure 7. Create stored procedure IV
- Select the columns you want to return in the result set.
Figure 8. Create stored procedure V
- Figure 9 depicts how the select statement inside the stored procedure looks:
Figure 9. Create stored procedure VI
After reviewing, click Next.
- Leave the defaults for Jar ID and Java Package. Click Finish.
Figure 10. Create stored procedure VII
Test the Java stored procedure
- 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
- 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
- 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
- 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
- Expand Web services, select Web Service DADX Group Configuration, and click Next.
- Click on Add group, and give it the name sampleDB. Click OK, and then click Next.
Figure 15. Create DADX group II
- 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
- Click OK, and then click Finish to exit this wizard.
Create a DADX file
- Select the Web project Db2serviceWeb. Right-click, and select New > Other...
Figure 17. Create DADX file I
- Select DADX File, and click Next.
- 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
- 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
- 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
- Bring up the New.. dialog box again, and select the Web Service wizard, then click Next.
Figure 20. Create DADX service I
- Choose the Web service type to be DADX Web services. Click the check boxes as shown below, then
Figure 21. Create DADX service II
- 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
- 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
- The data in this page is populated from the DADX group properties you configured in
Figure 24. Create DADX service V
- Click Finish, and let the code generation process finish.
Test the stored procedure Web service
- Once the wizard finishes, the Web services explorer will be open.
Figure 25. Test DADX service I
- 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
- The stored procedure service is invoked, and resultset is brought back and displayed below:
Figure 27. Test DADX service III
- 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.
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.
- "DB2 Web Service for UDB Practioners" (devleloperWorks, May, 2004): Find more information on using Web services with DB2.
- New to DB2 and Web Services page: Get an introduction to using Web services with DB2.
- developerWorks Information Management and SOA page: Find articles and tutorials about using Web services with DB2.
- developerWorks SOA and Web services zone: Increase your Web services knowledge.
- developerWorks Information Management and Rational application development page: Learn more about developing for DB2 using Rational Application Developer.
- developerWorks Information Management zone: Find more resources for DB2 UDB developers and administrators.
- Stay current with developerWorks technical events and webcasts.
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.
- Participate in the discussion forum.
- developerWorks blogs: Get involved in the developerWorks community.