Data Web Services on DB2 for z/OS, Part 1: Unlock business functions using DB2 for z/OS stored procedures and Data Web Services

It’s easy with Data Studio Developer

Is your organization using or planning to use DB2® for z/OS® stored procedures to provide business functions? Have you ever wondered how to reuse stored procedures without programming effort in Web services? If so, this article will be of interest to you.

Share:

Josef Klitsch (Josef.Klitsch@ch.ibm.com), IT Specialist, IBM

Josef Klitsch photoJosef 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.



07 May 2009

Also available in Spanish

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.


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.

Figure 1. Existing Account Inquiry stored procedures
Diagram illustrating three applications accessing DB2 tables through calling stored procedures

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).

Figure 2. ListCustom input and output interface
db2 query and result set for customer names starting with P

AccountSummary

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

Figure 3. AccountSummary input and output interface
db2 query and result set for a list of accounts belonging to a single customer

AccountDetail

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

Figure 4. AccountDetail input and output interface
db2 query and result set for activities of a single account

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
Figure 5. IBM Data Web Services (DWS) runtime environment overview
Diagram showing Web Service Client, WAS z/OS 6.1 and DB2 9 for z/OS

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.

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.
Figure 6. Set up database connection
New connection screen
  1. Establish a connection to that database server.
Figure 7. Establish database connection
Properties for DBLN screen
  1. Use Data Source Explorer to list the stored procedures you want to work with.
Figure 8. List stored procedures
Data Source Explorer screen with stored procedures highlighted
  1. Use Data Project Explorer -> New -> Data Development Project to create the Data_Account_Inquiry project.
Figure 9. Create Data_AccountInquiry Project
Data Project Explorer screen
  1. Use Data Project Explorer -> Web Services -> right click -> New
    to create the AccountInquiry Data Web Service project.
Figure 10. Create AccountInquiry DWS Project
Data Project Explorer screen with new AccountInquiry Data Web Service project
  1. Drag and drop the stored procedures that are to be made available as SOAP operations into the AccountInquiry Data Web Service project.
Figure 11. Drag and drop stored procedures
Stored procedures being dragged from Data Source Explorer into AccountInquiry project
  1. 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.
Figure 12. DWS AccountInquiry operations
Data Project Explorer with stored procedures listed under AccountInquiry as Web service operations
  1. 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.
Figure 13. ListCustomer operation SQL CALL statement
Edit Operation screen with detail for LISTCUTOMER operation

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.
Figure 14. Build WAR File
Data Project Explorer screen showing Build and Deploy being selected for AccountInquiry Web service
  1. 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.
Figure 15. Build WAR file parameters
Deploy Web Service screen with WAS z/OS v6.1 selected as Web server and Include Data Web Services test client selected
  1. Click Finish to create the WAR file.
  2. 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.
Figure 16 War file location
Navigator tab screen with properties showing for AccountInquiry WAR file

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.

Figure 17. ListCustomer Data Web Service via SOAPUDF
SQL statement to invoke the ListCustomer Data Web Service. P1 marker points to SOAP endpoint, P2 marker points to SOAP action, P3 marker points to SOAP request

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.

Figure 18. ListCustomer SOAPUDF VARCHAR output
SOAP response document

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).

Figure 19. ListCustomer SOAP response XML formatted
Data Project Explorer screen showing selections to format the SOAP response document

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.

Figure 20. AccountSummary SOAP UDF query
SQL SOAP UDF query for AccountSummary
Figure 21. AccountDetail SOAP UDF query
OAP UDF query for AccountDetail

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.

Figure 22. DWS runtime environment with XSLT processing
Diagram showing runtime environment with XSLT processing. Detailed steps are explained below.

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
Figure 23. ListCustomerHTMLtransformation
Listing of ListCustomertoHTML.xsl file contained in sample download
Figure 24. ListCustomer XML response
The XML response from ListCustomer
Figure 25. ListCustomer XSLT transformed HTML
The HTML that is created by transforming the XML response
  1. XML-to-HTML output transformation of AccountSummary response
Figure 26. AccountSummary HTMLtransformation
Listing of AccountSummarytoHTML.xsl file contained in sample download
Figure 27. AccountSummary XML Response
The XML response from AccountSummary
Figure 28. AccountSummary XSLT transformed HTML
The HTML that is created by transforming the XML response
  1. XML to HTML output transformation of AccountDetail response
Figure 29 AccountDetail HTMLtransformation
Listing of AccountDetailtoHTML.xsl file contained in sample download
Figure 30 AccountDetail XML Response
The XML response from AccountDetail
Figure 31 AccountDetail XSLT transformed HTML
The HTML that is created by transforming the XML 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.

Table 1 Assign style sheets to DWS operations
Web Service Operation Style Sheet File Name
ListCustomer ListCustomertoHTML.xsl
AccountSummary AccountSummarytoHTML.xsl
AccountDetail AccountDetailtoHTML.xsl

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).
Figure 32. Manage XSLT to Web service Operation
Data Project Explorer screen with Manage XSLT selected
  1. 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.
Figure 33. Add style sheet to output Messages
Configure XSL transformation screen with one of the xsl files selected
  1. 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.

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.

Figure 34. ListCustomer XSLT transformed HTML Web page
Customer list HTML page

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).

Figure 35. AccountSummary XSLT transformed HTML Web page
Account summary HTML page

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).

Figure 36. AccountDetail XSLT transformed HTML Web page
Account detail HTML page

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.


Download

DescriptionNameSize
Sample code for this articleAccountInquiry.zip303KB

Resources

Learn

Get products and technologies

Discuss

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
ArticleID=387933
ArticleTitle=Data Web Services on DB2 for z/OS, Part 1: Unlock business functions using DB2 for z/OS stored procedures and Data Web Services
publish-date=05072009