Benefits of Using Stored Procedures

Using DB2 stored procedures has many benefits. The major advantage to stored procedures comes when they are implemented in a client/server application that must issue several remote SQL statements. The network overhead involved in sending multiple SQL commands and receiving result sets is quite significant. Therefore, proper use of stored procedures to accept a request, process it with encapsulated SQL statements and business logic, and return a result will lessen the traffic across the network and reduce the application overhead, as shown in Figure 13-1.

Figure 13-1. Stored-procedure network improvements

Stored procedures can also provide access to host-server data that is required to be secure. By using a stored procedure, the client will need to have authority to execute only the stored procedures and will not need authority to the DB2 tables that are accessed from the stored procedures. They can also help simplify development and maintenance and improve availability. By removing client dependency on database design at the server, the client code can continue to run while changes are made to the underlying databases. Business logic can be incorporated into the stored procedures, minimizing changes to client code, and changes to stored procedures can be implemented while the client code is still executing.

Encapsulation of core business functions is also a great way to implement stored procedures. This allows functions to be programmed once and executed by any and all processes required for a business process. Keep in mind that simple SQL statements should not be used in a stored procedure to act as an I/O module. This should never be done, as the performance overhead is enormous.

Another nice feature about using stored procedures is that they provide access to non-DB2 data for use in DB2 applications. Stored procedures can be used to retrieve data from a VSAM or IMS data store to be used by a DB2 client. One way to do this is by retrieving the data into a global or declared temporary table in the stored procedure and returning that to the caller as a result set. Applications can then use SQL and result-set logic for row retrieval of this data. Stored procedures can also execute a CICS transaction on behalf of a client.

In summary, stored procedures have the following benefits:

  • Reduce network traffic

  • Simplify development and maintenance

  • Remove client dependency on database design at the server

  • Allow for the ability to dynamically change application programs

  • Changed and refreshed code at the server

  • No need to change client code

  • Can continue to run client code while changes are made

  • Allow most of an application to exist at the server, not the client

  • Need less code to be globally changed at all client locations

  • Reusable code

  • Improve security

  • Eliminate the need for end user table authority

  • Move processing away from end users

  • Provide the ability to access and update data that is not stored in DB2, such as VSAM IMS data

DB2 for z. OS Version 8 DBA Certification Guide
DB2 for z/OS Version 8 DBA Certification Guide
ISBN: 0131491202
EAN: 2147483647
Year: 2003
Pages: 175
Authors: Susan Lawson © 2008-2017.
If you may any questions please contact us: