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: