Skip to content

Personal tools
You are here: Home » DB2 » DB2 Mainframe Articles Archive » Use (and Abuse!) of DB2 for z/OS Stored Procedures - Part 1
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3554

Use (and Abuse!) of DB2 for z/OS Stored Procedures - Part 1

by Dan Luksetich and Susan Lawson

Part 1  |  Part 2


In Version 4 of DB2 on the mainframe, stored procedures were introduced. Their purpose was initially to help to lessen the traffic over the network by having a single call execute a stored procedure on the server to process the requests and then return the result to the client. Today, stored procedures are becoming more prevalent on the mainframe, and can be part of a valuable implementation strategy. Stored procedures can be a performance benefit for distributed applications, and even local applications, but more often than not they are becoming a performance problem. Part 2 of this article will offer more detail about the potential pitfalls of using stored procedures.

Most Beneficial Uses of Stored Procedures

In the following paragraphs we are going to look at some of the benefits to using stored procedures properly in a DB2 for z/OS environment. These benefits include:

      • Improved performance across a network
      • Security
      • Availability
      • Reduced dependency on client code
      • Reusability
      • Access to non-DB2 data

Improved Performance Across a Network

The major advantage to stored procedures happens when they are implemented in a client/server application that must issue several remote SQL statements. The network overhead involved in sending multiple remote SQL statements and receiving result sets is quite significant. Therefore, the proper use of stored procedures to accept a request, process that request with encapsulated SQL statements and business logic, and return a result will lessen the traffic across the network and reduce the application overhead.

The performance advantage of stored procedures is best realized when multiple SQL statements, as well as business logic, are incorporated into the stored procedure program. This enables the database work to happen close to the database server. The stored procedure is simply returning a result to the client for display. Client programs that contain application logic, issuing multiple SQL statements to the remote server per transaction, perform significantly slower than when a stored procedure is called to do the equivalent work. Likewise, stored procedure programs written as I/O modules (i.e. single-statement stored procedures) perform even worse then when client programs issue multiple statements. Overall, the best performing stored procedure is the one that contains multiple statements, business logic, and returns only a result to the client for display.

There are many other reasons why stored procedures are utilized, and when balanced with the proper implementation of embedded logic and SQL calls within the stored procedure, can improve application design, security, and flexibility without compromising performance. Using stored procedures for reasons other than performance is important. There can also be a performance advantage when business logic is also encapsulated within the stored procedure. The code path of calling the procedure and executing the statement, as opposed to simply issuing the statement is approximately 30,000 instructions longer.


Stored procedures can also provide access to data on the host server that is required to be secure. By using a stored procedure, the client will only need to have authority to execute the stored procedures and will not need authority to the DB2 tables that are accessed from the stored procedures. This is especially important for two-tier applications, or for three- tier applications where the end user id is used for data access. The owner of the stored procedure has authority to the underlying tables that are accessed, and grants execute privilege on the stored procedure to the application or end user id. Without this level of security, the user could access tables directly via any connection to the database outside of application control. If the stored procedure is used to only protect data, there may be an impact to performance if all that the procedure does is table access. The developer, in that case, has to be willing to sacrifice performance and cost for security.


A properly implemented stored procedure can help improve availability. Stored procedures can be stopped, queuing all requestors. A change can be implemented while access is prevented, and the procedures restarted once the change has been made. The applications using the stored procedures experience nothing more than a slowdown, as opposed to an outage. If the stored procedures are properly implemented outages can be eliminated. This involves moving more SQL access and logic into the stored procedures, eliminating the dependencies from the client code.

Reduced Dependency on Client Code

If business logic and SQL access are encapsulated within stored procedures, there is less dependency on client or application server code for business processes. That is, the client takes care of things like display logic and edits, and the stored procedure contains the business logic. When changes to business logic need to occur, only the stored procedure needs to be changed. This relieves the change control process from having to push the changes out to many clients, and only the logic at the server needs changing. This becomes more significant, as the number of clients increases. Why push a change out to 10,000 client programs if the change can be made to a single server program?


Stored procedures could contain centrally located business logic. These common business functions (e.g. create purchase order) can be reused by applications that need access to that logic. From an enterprise perspective, this would reduce the amount of code to be maintained. It would also reduce the possibility of common business functionality being out of sync across applications.

Access to Non-DB2 Data

DB2 for z/OS stored procedures run in stored procedure address spaces. These address spaces have access to all of the resources that any other address space in the system can access. This can include:

      • VSAM files
      • Flat files
      • IMS transactions and data
      • CICS transactions
      • MQ messaging
      • Etc.

Data from legacy data stores can return by the stored procedures via result sets. This enables legacy data to be returned to clients via a standard method. Since a stored procedure call is standard SQL access, accessing legacy resources via a stored procedure program can Web-enable this data extremely quickly.


In summary, there are many benefits to using stored procedures. The benefits include lessening network traffic, improving security, encapsulating code and access to non-relational data. In part 2, you will see some of the common misuses of stored procedures that can often lead to obvious and not-so-obvious problems.


Susan Lawson and Dan Luksetich are DB2 Performance Specialists for YL&A. They both have several years of experience working with a variety of DB2 clients and have been designing and tuning some of the world’s largest and most complex DB2 databases.

Contributors : Dan Luksetich, Susan Lawson
Last modified 2006-01-04 03:36 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone