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.
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)
Figure 1 shows how WORF processes a Web service request: service request.
- 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.
- WORF perform the following steps in response to a Web Service request:
- Loads the DADX file specified in the request.
- Loads a DAD file, if requested.
- Replaces query parameters with requested values
- Connects to DB2 and runs any SQL statements, including SQL calls.
- Commits the database transaction.
- Formats the result into XML, converting types as necessary.
- 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_TIMESTAMPis of type
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
And here is the execution result you will see:
Figure 3. Results from stored proceudure
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.
- Click File > New > Enterprise Application Project.
- 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
- In the Navigator view, select the Web project in which you want to create the DADX group.
- Click File > New > Other > Web Services in order to display the various Web service wizards.
the Web Services DADX Group Configuration wizard. Click Next.
Figure 4. Configuring the DADX
- Select your Web project. Click Add group. Enter a name for your
DADX group DB2SPGrp. Click OK.
Figure 5. Entering a DADX group name
your Web project to display your DADX group. Select your DADX group.
Select Group Properties. Change the DB URL to
dbnameis 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
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
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
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
- 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
- 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
- On Web Service Deployment Settings page, just leave as default
settings, and click Next.
Figure 11. Choose defaults for deployment options
- On the page of DADX file selection, browse to your DADX file,
and click OK and Next.
Figure 12. Choosing the DADX file
- 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
- On the Web
Service binding proxy generation page, select soap binding,and click Next
Figure 14. Choosing the SOAP binding
- 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
Test the Web service
- 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
- 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
- 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
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.
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.
The author would like to thank Arthur Ryman and Dirk Wollscheid for reviewing the article, and providing valuable suggestions and contributions.
- 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