Call Level Interface Guide and Reference

Using Stored Procedures

An application can be designed to run in two parts, one on the client and the other on the server. The stored procedure is the part that runs at the database within the same transaction as the application. Stored procedures can be written in either embedded SQL or using the DB2 CLI functions (see "Writing a Stored Procedure in CLI"). In general, stored procedures have the following advantages:

• Avoid network transfer of large amounts of data obtained as part of intermediate results in a long sequence of queries.

• Deployment of client database applications into client/server pieces.

In addition, stored procedures written in embedded static SQL have the following advantages:

• Performance - static SQL is prepared at precompile time and has no run time overhead of access plan (package) generation.

• Encapsulation (information hiding) - users' do not need to know the details about the database objects in order to access them. Static SQL can help enforce this encapsulation.

• Security - users' access privileges are encapsulated within the package(s) associated with the stored procedure(s), so there is no need to grant explicit access to each database object. For example, a user can be granted run access for a stored procedure that selects data from tables for which the user does not have select privilege.

[ DB2 List of Books | Search the DB2 Books ]