Introduction

As more people appreciate the tremendous benefits Web services technologies bring to distributed computing, and as those technologies mature, more companies are now seriously are looking at how they can leverage Web services.

A key area in which Web services technology can play is in using Web services for integrating with information assets on the mainframe, such as IBM® DB2® Universal Database^TM for z/OS^TM and OS/390® stored procedures, and IMS® and CICS® transactions. By doing this you basically provide a lightweight, open, standard interface to all these assets. The benefit is enormous: it is much easier to access and integrate these traditional information assets in larger business processes. See Related content for more information about how Web services can help integrate legacy applications into new business processes.

However, the reality is that backend developers usually have very strong COBOL and PL/1 skills, but they may have limited knowledge on Web services, Java^TM, and J2EE. . This is typically true in lots of large enterprises. This is also one of reasons preventing them from adopting Web Service technology faster. Fortunately, IBM provides the tooling and runtime to help enterprise backend developers; thereby making the transition to Web services technology easier.

The target audience for this article are those developers that have limited Java knowledge. You will learn how to create a Web service for DB2 UDB for OS/390 or z/OS stored procedures. Although we are focusing on mainframe stored procedures, the same basic approach applies to DB2 stored procedures on Linux, UNIX®, and Windows®.

In this article, I will use the Application Developer configuration of WebSphere® Studio V5. These tasks can also be accomplished using other WebSphere Studio configurations.

Back to top

Background

An IBM technology called WORF (which stands for Web services Object Runtime Framework) provides the environment to easily create XML-based Web services that access DB2 UDB. WORF uses Apache Simple Object Access Protocol (SOAP) 2.2 or later and the Document Access Definition Extension (DADX). A DADX document specifies a Web service using a set of operations that are defined by SQL statements or by XML Extender® Document Access Definition (DAD) documents. Web services, or functions invoked over the Internet, specified in a DADX file are called DADX Web services, also referred to as DB2 Web services. Figure 1 illustrates the WORF architecture.

Figure 1 shows how WORF processes a Web service request: service request.

1. WORF receives an HTTP SOAP, GET or POST service request.

   The URL of the Web service request includes the name of the Web service's resource file and a command. The command is either a built-in command or specifies an operation of the Web service. The built-in commands include TEST, WSDL, and XML schema files (XSD), in which case WORF generates an HTML test page, a WSDL document, or an XML schema file, respectively. If the request is a SOAP request, then the operation name is specified in the request body. Otherwise, the command is the operation name. In either case, WORF invokes the specified operation of the Web service.

2. WORF perform the following steps in response to a Web Service request:
   1. Loads the DADX file specified in the request.
   2. Loads a DAD file, if requested.
   3. Replaces query parameters with requested values
   4. Connects to DB2 and runs any SQL statements, including SQL calls.
   5. Commits the database transaction.
   6. Formats the result into XML, converting types as necessary.
3. WORF returns the response to the service requestor (not shown in Figure 1).

Back to top

Before we begin

We will be calling a DB2 for z/OS stored procedure from WebSphere Studio on our desktop. To do that, we must first install the DB2 UDB Application Development Client on our machine. And then we need to configure an alias for our remote DB2 for z/OS using the DB2 Client Configuration Assistant. For detailed information on configuring database alias on your local development machine for a remote DB2 for z/OS database, see the DB2 Information Center.

The DB2 stored procedure we use here is written in PL/I.It takes a telephone number, start date, end date, etc as input parameters, and return all calls made between start and end dates plus some output parameters. Here is the DDL for this stored procedure:

Back to top

Listing 1. DDL for the stored procedure used in this article

 
CREATE PROCEDURE SYSPROC.VAMU302( 
IN APP_USER_ID CHAR(8), 
IN DEBUG_LEVEL CHAR(1), 
IN WTN   CHAR(10), 
IN DATE-START CHAR(10), 
IN DATE-END  CHAR(10), 
IN REQUESTE-ITEM INTEGER, 
OUT RETURN_CODE INTEGER, 
OUT REASON_CODE CHAR(8), 
OUT ERROR_TEXT VARCHAR(2000), 
OUT SP_SQLCODE INTEGER, 
OUT SP_SQLTOKENS CHAR(70) 
OUT SP_SQLSTATE CHAR(5) 
) 
 
LANGUAGE PLI 
EXTERNAL NAME VAMU302 
PARAMETER STYLE GENERAL 
FENCED 
MODIFIES SQL DATA 
NO DBINFO 
COLLID USAGE_PACKAGE 
WLM ENVIRONMENT VAMD38X 
ASUTIME NO LIMIT 
STAY RESIDENT NO 
PROGRAM TYPE MAIN 
SECURITY DB2 
COMMIT ON RETURN YES 
RESULT SETS 1 

You will also need to know the structure of results sets. In our case, it just consists of two columns:

• CALL_TIMESTAMP is of type TIMESTAMP
• EMI_LINE is a VARCHAR(200)

Using the DB2 Stored Procedure Builder, you can execute this procedure, click Run, and you will be prompted to enter the input data:

And here is the execution result you will see:

Now we are ready to continue to turn this stored procedure into a Web service. By using WebSphere Studio, we can do this with relative ease.

Back to top

Create an enterprise application

We will need a J2EE enterprise application to hold the generated Web services. We'll use WebSphere Studio to do this.

1. Click File > New > Enterprise Application Project.
2. We will name our enterprise application SP, and it contains a web application called SPWeb.

For detailed information on creating J2EE application, see WebSphere Studio documentation in Related content.

Back to top

Create a DADX group

1. In the Navigator view, select the Web project in which you want to create the DADX group.
2. Click File > New > Other > Web Services in order to display the various Web service wizards.
3. Select the Web Services DADX Group Configuration wizard. Click Next.
   Figure 4. Configuring the DADX
   
   
4. Select your Web project. Click Add group. Enter a name for your DADX group DB2SPGrp. Click OK.
   Figure 5. Entering a DADX group name
   
   
5. Expand your Web project to display your DADX group. Select your DADX group. Select Group Properties. Change the DB URL to jdbc:db2:dbname where dbname is the name of your database. You will also need to enter User ID and Password Click OK.

   Important: The default for the enableXMLClob field is "true". You must also select "true" forUse document style. This enables document style message encoding and this is recommended when working with stored procedures that have multiple output parameters or result sets. For a detailed discussion of the benefits using the document style, see the article Reap the benefits of document style Web services

   
   Figure 6. Entering DADX group properties
   
   
6. Click Finish. Your DADX group is generated in: WebProject\Java Source\groups, and your workspace should look like the following:
   Figure 7.New DADX group is added
   
   

Back to top

Create a DADX file

WebSphere Studio provides tooling for creating DADX file for SQL queries (select, update, delete). For stored procedures, you must manually create the DADX files. Fortunately, DADX syntax is very easy to understand, and you can use any text editor to create it. Create or import your DADX file into your DADX group (WebProject\Java Source\groups\DADXGroup) where DADXGroup is the group that you just created in the steps above. Figure 8 shows the DADX file for our stored procedure discussed above.

Let's take a closer look at the DADX file in Figure 8 and see how it maps to our stored procedure.

• The DADX file starts like any normal XML file by specifying the namespaces. You can then have an optional documentation element, which explains what this DADX file is about.
• Next is the result_set_metadata element. In one DADX file, you can specify 0 to n of these result_set_metadata elements. These are global elements that can be referenced later in various operations. When specifying a result_set_metadata, you give it a name (callInfo), and match each column with its corresponding SQL type.
• Then we come to the operation elements. One DADX file can have multiple operations, each having a logical name (in our case, VAMU302) and representing a stored procedure call or an SQL query. For stored procedures, you start with <SQL_CALL> stanza, specifying the actual stored procedure name (SYSPROC.VAMU302) and all the parameters, including both inputs and outputs in the same order as is defined in stored procedure DDL. You continue to map each parameter with an XSD type. Finally you need to specify the result set, referencing the one we have already defined above (callInfo).

That's all you have to do to map the stored procedure (and its result set) to a DADX operation.

During the code generation process, the DADX file will be transformed to a WebSphere Description Language (WSDL) file to represent the stored procedure Web service. For exact syntax, and XML schema for the DADX file, the documentation in Related content.

Back to top

Create Web services from the DADX file

1. Click File > New > Other. Select Web Services in order to display the various Web service wizards. Select the Web Service wizard. Click Next.
   Figure 9. Creating a new web service
   
   
2. Follow the instructions in the wizard to configure your DADX Web service. On the Web Services page of the wizard, for web service type, select DADX Web Service. Also select Generate a proxy. Click Next.
   Figure 10. Specifying DADX Web service
   
   
3. On Web Service Deployment Settings page, just leave as default settings, and click Next.
   Figure 11. Choose defaults for deployment options
   
   
4. On the page of DADX file selection, browse to your DADX file, and click OK and Next.
   Figure 12. Choosing the DADX file
   
   
5. On Web Service DADX group properties page, leave everything as it is, since we have already defined all properties.
   Figure 13. The DADX group properties
   
   
6. On the Web Service binding proxy generation page, select soap binding,and click Next
   Figure 14. Choosing the SOAP binding
   
   
7. On this test page, check the box for Test the generated proxy box. This creates a simple Web application using the generated proxy to invoke our stored procedure Web service. Click Finish, and wait for the code generation process to end.
   Figure 15. Generating a test proxy
   
   

Back to top

Test the Web service

1. After the code is generated, the WebSphere test environment server will start automatically and load the test application, as shown in Figure 16.
   Figure 16. The test application
   
   
2. In the Methods pane, select the method we will be testing, and fill in all the needed parameters. When you click Invoke , you will be invoking a Web Service, and displaying the response from the service in the browser. What's happening here behind the scene is that a JSP page at the server gets called. This JSP page is a Web service client, and it uses a Web service proxy to access the actual stored procedure Web service. The proxy also serializes and deserializes between Java objects and XML. When the proxy call returns, the JSP page displays the results as shown in Figure 17.
   Figure 17. The result of our Web service
   
   
3. If you are really curious to know how data flows on the wire, you can configure a TCP/IP Monitoring Server inside WebSphere Studio to see the request and response transferred in SOAP messages. First, be sure you are actually making a Web service call. Here is a snapshot for the SOAP call we made above:
   Figure 18. Watching the SOAP messages
   
   

Congratulations! At this point, you have successfully built and test a stored procedure Web service in WebSphere Studio. You can easily package this up, and deploy it to run in WebSphere Application Server.

Back to top

Conclusion

WORF and DADX are powerful techniques for enabling DB2 stored procedure as Web service, and WebSphere Studio provides excellent tools to help us achieve this fast. As you have seen during this tutorial, minimal programming is needed. Using this tool, backend developers with little Java and JDBC experience can turn a DB2 stored procedure into Web service in minutes. This is really exciting, since it opens up and may revolutionize the way how DB2 stored procedures on mainframes are accessed and reused.

Back to top

Acknowledgements

The author would like to thank Arthur Ryman and Dirk Wollscheid for reviewing the article, and providing valuable suggestions and contributions.

Resources

• DB2 Web services object runtime framework
  
  
• White paper: Dynamic e-business with DB2 and Web Services
  
  
• DB2 and Web services
  
  
• Reap the benefits of document style Web services
  
  
• Developing and Testing a Complete "Hello World" J2EE Application with WebSphere Studio
  
  

About the author

Peter Xu is a Senior Consultant with IBM Software Services for WebSphere group. Consultants with Software Services for WebSphere help customers deploy IBM products into their organizations. Peter provides consulting services, education, and mentoring on J2EE technologies, and specifically WebSphere and WebSphere Studio products to Fortune 500 clients. You can reach him at peteryxu@us.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Background
• Before we begin
• Listing 1. DDL for the stored procedure used in this article
• Create an enterprise application
• Create a DADX group
• Create a DADX file
• Create Web services from the DADX file
• Test the Web service
• Conclusion
• Acknowledgements
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers