Overview

This article reviews DB2 for z/OS stored procedures and the benefits of wrapping them in Web services. It shows you how you can use IBM Data Studio Developer to create and deploy Web services using the Data Web Services feature. It also shows you how to use XSLT style sheet processing to transform the Web service XML response into HTML-based Web browser interactions.

This article is the first of a two-part series. Part two explores how you can use DB2 for z/OS user-defined functions (UDFs) to integrate a z/OS data set and UNIX® System Services (USS) file information into Data Web Services operations.

Prerequisites

This article assumes that you know what IBM Data Web Services are and that you are familiar with the basics of using IBM Data Studio Developer to create, test and deploy IBM Data Web Services. If you are unfamiliar with either of these topics, refer to the Resources section for links on these topics.

Also, you will likely find the article more useful if you first download and extract the sample code contained in the AccountInquiry.zip file in the Download section.

Introduction to stored procedures

Stored procedures are often used to consolidate and centralize logic that was originally implemented in applications. Large or complex processing that might require the execution of several SQL statements can be moved into server-side stored procedures to provide a centralized interface to enterprise applications.

DB2 for z/OS supports the following types of stored procedures:

• SQL procedures (external and native)
• External (written in a programming language such as C, COBOL, PL/I or Java®)

SQL procedures are written entirely in SQL and allow access to DB2 resources via SQL operations. Non-DB2 resources cannot be accessed from SQL procedures.

External DB2 for z/OS stored procedures are server-side applications that can be written in different programming languages, including COBOL. The stored procedure program can access DB2 data as well as traditional non-DB2 resources such as QSAM and VSAM data sets, message queues, CICS programs, IMS transactions and databases. Additionally, external stored procedure programs can reuse existing business logic by calling existing applications as subprograms.

Stored procedures can be invoked by other stored procedures and by DB2 client applications using embedded SQL, .NET, ODBC, or any Java application. The z/OS external stored procedure runtime environment is managed by z/OS Workload Manager (WLM).

Benefits of using stored procedures

Since stored procedures were introduced in DB2 for z/OS they have evolved into a mature and robust technology that is being used by many organizations to handle high-volume transactional workloads. Stored procedures in that context are typically used for at least one of the following reasons:

• Improved network performance for remote clients
  If a client/server application issues multiple remote SQL statements, the network overhead for sending statements and receiving results can be significant. Stored procedures can help to avoid network round trips by encapsulating multiple SQL statements.
• Secure access to data
  If a stored procedure contains embedded static SQL, the client only needs to have the privilege to execute the stored procedure. Authority to access the underlying DB2 tables is not required, thereby reducing risk and exposure.
• Ease of development and maintenance
  Using stored procedures can help to reduce client dependency on database design.
• Access to non-DB2 data
  External stored procedures written in COBOL, PL/I, C++ or Java can access non-DB2 resources like any other application program executing in the z/OS environment. For instance VSAM or IMS data can be retrieved into a global or declared temporary table and subsequently processed by client applications using SQL and result set logic.
• Availability and reusability
  External DB2 for z/OS stored procedures are managed by z/OS Workload Manager (WLM) thus providing scalability and availability. Having the stored procedure code centrally stored in a single place at the database server supports code reusability. DB2 for z/OS and WLM interfaces are available to apply stored procedure changes without impacting service availability.
• Encapsulation of business functions

External stored procedures are often used to access legacy data stores and legacy application systems. They encapsulate business logic and SQL statements, access non-DB2 resources, comply with security policies, reuse business logic by calling existing modules as subprograms, and optionally return result sets to client applications. This benefit is highlighted in the scenario described in this article.

Access to business functions encapsulated in stored procedures using Web services

DB2 for z/OS stored procedures that encapsulate business functions are well-suited to be reused in Data Web Services. They provide reusable services that expose the capabilities of existing systems in a carefully controlled way. For instance, the services interface, the most critical component in an SOA, already exists (although not as a Web services interface), and is being used by enterprise applications across multiple departments and lines of businesses. The quality analysis of the underlying data has been successfully completed. For existing stored procedures, the code has been developed, tested, and is already a well recognized part of the IT infrastructure that complies with existing service level agreements (SLAs). The ability to take these stored procedures and expose them using a Web services interface makes it accessible to the larger application infrastructure.

Back to top

An existing sample business scenario

To illustrate the value and usage of DB2 for z/OS stored procedures, this article assumes an existing sample business scenario that provides three banking applications: Call Center, Bank Branch and Internet Banking. Each of these applications utilizes business functions that are encapsulated in the following DB2 for z/OS stored procedures:

1. ListCustomer
2. AccountSummary
3. AccountDetail

The usage of these stored procedures by each banking application is illustrated in Figure 1.

The Call Center and Bank Branch applications invoke stored procedures 1, 2 and 3 to determine the customer number, the account number, and detailed account information, respectively. The Internet Banking application only invokes stored procedures 2 and 3, because the customer provides a customer number during authentication.

The client-side application code invokes the stored procedures, links up customer-name, customer-number, and account-number, and does the data presentation. The stored procedure type and programming language are transparent to the stored procedure client.

The sample in this article uses DB2 9 for z/OS native SQL procedures to access banking information that is stored in DB2 tables. You might wonder why stored procedures are used? Why not use straight SQL queries to provide the same information? As mentioned earlier, stored procedures provide many benefits. In this case:

• The SQL stored procedures already exist and are available as reusable assets.
• The stored procedures make the client application independent from the underlying database design and keep complexity away from the client, encapsulated in the database layer.

In other scenarios the use of external stored procedures might be required because of IT infrastructure complexity. For instance, the stored procedure might have to interface with remote back-end systems to retrieve the information from CICS programs, IMS databases and transactions, or might even have to interface via WebSphere® MQ with non-IBM platforms.

Following are details about the input and output interfaces of the stored procedures used by the sample business scenario.

ListCustomer

The ListCustomer stored procedure provides a list of eligible customers by customer name, customer number, and date of birth (see Figure 2).

AccountSummary

The AccountSummary stored procedure retrieves a list of bank accounts for a given customer number (see Figure 3).

AccountDetail

The AccountDetail stored procedure retrieves detailed account statement information for a given account number (see Figure 4).

Back to top

Sample business scenario with IBM Data Web Services

This part of the article describes a sample Data Web Services runtime environment and explains how you can expose stored procedures as Web services with just a few mouse clicks. You will notice that exposing stored procedures as Data Web Services alone does not provide client-side application logic that is required to run these Web services in a business context. So the articled also illustrates how XSLT style sheets can help to turn the previously deployed AccountInquiry Data Web Service into a HTML-based Web application.

IBM Data Web Service runtime environment

The sample Data Web Service (DWS) runtime environment is illustrated in Figure 5. It uses:

• WebSphere Application Server for z/OS Version 6.1 (WAS) as the J2EE application server to host the AccountInquiry Web service. The IBM DWS runtime environment, which includes the common DWS servlet, is an integral part of the AccountInquiry DWS. You do not have to perform any other WAS implementation steps to enable WAS for IBM Data Web Services.
• DB2 9 for z/OS as the database server.

For the purpose of testing the Web service, you can use any of the following:

• IBM Data Studio Developer (specifically the Data Web Services explorer)
• DB2 for z/OS SOAP UDFs as the client interface for driving Data Web Services requests
• The test client using a Web browser
• Any client that can invoke Web services

The scenario shown in Figure 5 was used to deploy a Data Web Service named AccountInquiry as a J2EE application in WebSphere Application Server. For Web Service invocation, you have a choice of three Web service consumer applications: DB2 for z/OS SOAP UDF, Data Web Service Test Client, Data Studio Web Services Explorer. In the sample scenario DB2 for z/OS SOAP UDFs is used as the Web service client.

Following is an outline of the processing flow of the Data Web Service runtime environment shown in Figure 5:

1. The Web service client issues SOAP/HTTP requests to consume Web services.
2. WebSphere Application Server, which implements the SOAP endpoint, receives the SOAP request and passes control to the IBM Data Web Services runtime environment.
3. The Data Web Services common Servlet translates the SOAP operations into database operations and uses JDBC to issue corresponding SQL requests.
4. Depending on the J2EE and DB2 infrastructure implementation the JNDI data source definition uses JDBC type 2 or JDBC type 4 for Java database connectivity.
5. The XML-encoded output message is returned to the Web service client application.

Back to top

Exposing existing stored procedures as Data Web Services

Create the AccountInquiry Data Web Services with IBM Data Studio

IBM Data Web Service creation and deployment is fully integrated in IBM Data Studio Developer, making it easy to develop a Web service with just a few mouse clicks. This part of the article describes how you can use Data Studio Developer to expose existing DB2 for z/OS stored procedures as Web services. For more detailed information, see IBM Data Studio V2.1: Getting Started with Web Services on DB2 for z/iOS, which is linked to in the Resources section.

1. Set up a connection to the DB2 for z/OS database server.

2. Establish a connection to that database server.

3. Use Data Source Explorer to list the stored procedures you want to work with.

4. Use Data Project Explorer -> New -> Data Development Project to create the Data_Account_Inquiry project.

5. Use Data Project Explorer -> Web Services -> right click -> New
   to create the AccountInquiry Data Web Service project.

6. Drag and drop the stored procedures that are to be made available as SOAP operations into the AccountInquiry Data Web Service project.

7. Upon successful completion of the drag and drop operation you see the stored procedures listed as Web service operations in Data Project Explorer -> Data_Account_Inquiry -> Web Services -> AccountInquiry. The (*) asterisk behind the Web service name indicates that the Web service has not yet been built.

8. From the AccountInquiry Web Services project, double-click on LISTCUSTOMER to display the stored procedure SQL CALL statement that is going to be executed whenever the Web service AccountInquiry executes the LISTCUSTOMER operation. You can display the SQL CALL statements of the other Web Service operations by double clicking the ACCOUNTDETAIL and ACCOUNTSUMMARY operations.

Build Web Application Archive (WAR) file

You can perform the following Data Studio activities to create the Web Application Archive file:

1. After creating the Data Web Service, build the Web Application Archive (WAR) file that you can install on WebSphere Application Server for z/OS as a J2EE application. Do this by going to Data Project Explorer -> Data_Account_Inquiry -> Web Services -> AccountInquiry -> right click -> Build and Deploy as shown in Figure 14.

2. On the Deploy Web Service screen, specify the Web server target environment and indicate that the Data Web Service test client application should be included in the WAR file creation process. The Data Web Service test client application can be extremely useful because it provides a Web browser interface for test driving Web service operations. For the data handler, you also can use pureQuery to take advantage of static SQL in DB2 for better performance and security and ease of database administration.

3. Click Finish to create the WAR file.
4. From the Navigator tab, select Data_Account_Inquiry -> DataServerWebServices -> AccountInquiry -> Data_Account_InquiryAccountInquiry.war. Then right click on Properties to determine the location of the WAR file. You need the WAR file location information for the application installation in WebSphere Application Server for z/OS.

Data Web Service installation in WebSphere Application Server for z/OS

This article does not focus on details for using the WebSphere Application Server Integrated Solution Console (ISC); it just outlines the tasks to install Data Web Services. For more information on WebSphere Application Server for z/OS configuration and Data Web Service installation, refer to DB2 9 for z/OS: Deploying SOA Solutions, Chapter 7.7, Setting up WebSphere Application Server, which is linked to in the Resources section.

To install the AccountInquiry Web service using the Integrated Solution Console (ISC), do the following:

1. Define a JNDI data source for connecting to the DB2 for z/OS database server. The sample scenario uses these JNDI data source attributes:
• Provider: DB2 Universal JDBC Driver Provider
• Name: DBLN
• JNDI Name: jdbc/DBLN
• DB2 Location Name: RDBNDBLN
• DB2 TCP/IP Port: 446
• JDBC Type 4
• Server Name: ZNTC
2. In the WebSphere Application Server ISC do the following:
1. Select Applications -> Install New Application to upload the Data Studio generated WAR file (see Figure 16) in order to install the Data Web Service as a J2EE application.
2. Select Enterprise Applications -> Applications -> Start to start the new J2EE application.
   

After the application starts successfully, the AccountInquiry Data Web Service is available to serve Web service requests.

Testing the AccountInquiry Data Web Service

Once the AccountInquiry DWS is implemented in WebSphere Application Server, the Web service becomes available to client applications. DB2 for z/OS and Data Studio Developer provide Web service client applications that can be used to consume Web services via SOAP/HTTP.

The available client applications are:

• DB2 for z/OS SOAP/HTTP User Defined Functions (SOAP UDFs)
• Data Studio Web Services Explorer
• Data Web Service Test Client

For additional information on the Web service client applications provided by DB2 for z/OS and Data Studio, refer to DB2 9 for z/OS: Deploying SOA Solutions, which is linked to in the Resources section.

The sample scenario uses the DB2 for z/OS SOAP UDFs from SQL to invoke the AccountInquiry Web services operations. Before you can use the DB2 for z/OS SOAP UDFs, additional customization steps are required in DB2 for z/OS. For details on these customization steps and on additional SOAP/HTTP UDF usage scenarios refer to Chapter 8. “DB2 as a Web services consumer with SOAP UDFs: Scenario 3” of DB2 9 for z/OS: Deploying SOA Solutions, which is linked to in the Resources section.

ListCustomer

Figure 17 shows an SQL statement that invokes the ListCustomer Data Web Service.

The SQL statement passes three input parameters: the SOAP Endpoint (P1), the SOAP Action (P2), and the SOAP request document (P3) to the DB2 SOAP UDF program. The DB2 SOAP UDF program uses these input parameters to launch a SOAP/HTTP request on behalf of the SQL client. Upon completion of the SOAP/HTTP request, the SOAP response document is passed back to the SQL client as a VARCHAR SQL scalar value as shown in Figure 18.

Store the VARCHAR data (Figure 18) in a Data Studio XML document. Select Data Project Explorer -> Data_Account_Inquiry ->XML -> XML Documents -> ListCustomer -> right click -> Source -> Format to format the SOAP XML Response document (see Figure 19).

As you can see in Figure 19, the ListCustomer DWS returned the information of two eligible customers.

AccountSummary and AccountDetail

Repeat the above steps you just performed for the AccountSummary and AccountDetail DWS operations. The SQL SOAP UDF query for AccountSummary is shown in Figure 20. The SQL SOAP UDF query for AccountDetail is shown in Figure 21.

Back to top

XSLT style sheets: Turn your DWS into an HTML-based Web application

In this part of the article, you learn how to use XSLT style sheets to turn the AccountInquiry DWS into an HTML-based Web browser application that uses HTML hyperlinks to provide drilldown capability.

Background

The eXtensible Style Sheet Transformation (XSLT) language can transform an XML document’s source tree into a different result tree. In the sample scenario for this article, the AccountInquiry Data Web Service response represents an XML message that can be transformed by XSLT into HTML format. As a result, you can invoke the AccountInquiry Web service operations directly through a Web browser and receive an HTML formatted response.

Target runtime environment

Figure 22 depicts a scenario in which the AccountInquiry Web service is a deployed as a J2EE application. The deployment includes rules for XML-to-HTML output transformation, which enables you to invoke the Web service from a Web browser like an ordinary HTML-based application.

Following are details about the major steps of the processing flow:

1. The Web service client issues a REST-style HTTP RPC request to consume Web services.
2. WebSphere Application Server, which implements the HTTP RPC endpoint, receives the HTTP GET request and subsequently passes control to the Data Web Services runtime environment.
3. The Data Web Service common Servlet translates the HTTP RPC operations into database operations and uses JDBC to issue corresponding SQL requests.
4. Depending on the J2EE and DB2 infrastructure implementation, the JNDI data source definition uses JDBC type 2 or JDBC type 4 for Java database connectivity.
5. Because XSLT message output transformation is being used, the XML response message is transformed into HTML format and subsequently returned to the Web browser.

The XSLT style sheets

The sample code in the Download section contains three XSLT style sheets, one for each AccountInquiry operation. The XSLT uses HTML hyperlinks (href) to provide drilldown capability from ListCustomer to AccountSummary, and from AccountSummary to AccountDetail. Listed below are the XSLT style sheets for XML-to-HTML transformation, the Web service XML response documents that are to be transformed, and the transformed HTML documents:

1. XML to HTML output transformation of ListCustomer response

2. XML-to-HTML output transformation of AccountSummary response

3. XML to HTML output transformation of AccountDetail response

Assigning style sheets to Data Web Service operations

Now connect each style sheet to its corresponding Web service operation, as shown in Table 1.

To implement the above style sheet assignments, do the following once for each Web service operation:

1. From the Data Project Explorer, right click on the Web service operation and select Manage XSLT (Figure 32).

2. From the Configure XSL screen (see Figure 33), choose the appropriate file and click Finish. The XSLT document is now associated with the appropriate Web service operation.

3. To activate the XSLT style sheet transformation, redeploy the Data Web Service J2EE application. To do this, rebuild the WAR file in Data Studio and reinstall the J2EE application as described in Build Web Application Archive (WAR) File and Data Web Service Installation in WAS for z/OS.

Back to top

Running the HTML-based Web Service Application

Now that you have successfully installed the AccountInquiry application in WebSphere Application Server, it will use XSLT output message transformation. Web service output messages will be transformed into an HTML format. This enables Web browser applications to interact with AccountInquiry Web Service operations using REST-style HTTP RPC requests.

You can invoke the ListCustomer Web service using a REST style http URL. Special characters that are a part of the URL must be URL encoded. The invocation returns the HTML based Web page shown in Figure 34.

From the browser window shown in Figure 34, click Customer Number 36505633552 to bring up the account summary information for that customer number (see Figure 35).

From the browser window shown in Figure 35, click Account Number 9000000002 to bring up the account detail information for that account number (see Figure 36).

Back to top

Conclusion

With IBM Data Web Services you can expose existing DB2 for z/OS stored procedures as Web services with just a few mouse clicks. With IBM Data Web Services, existing DB2 for z/OS stored procedures can become a vital and reusable asset in a Service Oriented Architecture (SOA). Back-end developers with no Java experience can turn existing stored procedures into Web services (without programming effort). In addition, XSLT style sheets offer exciting new options for application development. Without having to write any application code, XSLT output message transformations can turn IBM Data Web services into full-blown HTML- based Web browser applications.

The second part of this article will show you how to use DB2 for z/OS user defined functions to integrate z/OS data set and USS file information with Data Web Services operations.

Acknowledgements

The author would like to thank Michael Schenker for providing his expertise on XSLT style sheet transformation. The author would also like to thank all his colleagues who patiently reviewed this article and gave valuable feedback.

Back to top

Download

Information about download methods

Resources

Learn

• "Data Web Services: Build Web services the new way to access IBM database servers": a developerWorks article that introduces Data Web Services.
  
  
• IBM Data Studio V2.1: Getting Started with Web Services on DB2 for z/OS: an IBM Redpaper that provides a step-by-step introduction on how to get started with Web services using Data Studio V2.1.
  
  
• IBM Data Web Services: a video showcasing Data Web Services.
  
  
• DB2 9 for z/OS: Deploying SOA Solutions: an IBM Redbook about get started with SOA using DB2 for z/OS.
  
  
• Learn more about Data Studio Developer at the IBM Integrated Data Management Information Center.
  
  

Get products and technologies

• Download IBM Data Studio Developer V2.1
  
  

Discuss

• Participate in the discussion forum.
  
  

About the author

Josef Klitsch is an IT Specialist with IBM Software Services, currently assigned to work for IBM PSSC Montpellier, New Technology Center. Josef provides consulting services related to DB2 for z/OS. You can reach him at josef.klitsch@ch.ibm.com.

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Overview
• Prerequisites
• Introduction to stored procedures
• Access to business functions encapsulated in stored procedures using Web services
• An existing sample business scenario
• Sample business scenario with IBM Data Web Services
• Exposing existing stored procedures as Data Web Services
• XSLT style sheets: Turn your DWS into an HTML-based Web application
• Running the HTML-based Web Service Application
• Conclusion
• Download
• 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