Creating Web Services for DB2 UDB for OS/390 Stored Procedures Using WebSphere Studio Version 5

With WebSphere Studio, turning your DB2 UDB for z/OS stored procedures into Web services is easier than you think. This article takes you step by step through the process.

Peter Xu, Senior Consulting I/T Specialist, EMC

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.



10 April 2003

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 DatabaseTM for z/OSTM 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, JavaTM, 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.


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. Web services object runtime framework (WORF)
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).

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:


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:

Figure 2. Entering input data
executing stored procedure

And here is the execution result you will see:

Figure 3. Results from stored proceudure
results

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.


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.


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
    Configuring 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
    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
    the 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
    navigation shows new dadx group

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.

Figure 8. Sample DADX file
sample DADX file

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.


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
    create 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
    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
    choosing 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
    find your DADX
  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
    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
    generate the proxy
  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
    testing the proxy

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
    our test environment
  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
    data result from 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
    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.


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.


Acknowledgements

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

Resources

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, WebSphere
ArticleID=14036
ArticleTitle=Creating Web Services for DB2 UDB for OS/390 Stored Procedures Using WebSphere Studio Version 5
publish-date=04102003