Use of an application program as a stored procedure

A stored procedure is a compiled program that can execute SQL statements.

Stored procedures are stored at theDB2® local or remote server where they run. A typical stored procedure contains two or more SQL statements and some manipulative or logical processing in a program. A client application program uses the SQL CALL statement to invoke the stored procedure.

Consider using stored procedures for a client/server application that does at least one of the following things:

• Executes multiple remote SQL statements.

  Remote SQL statements can result in several send and receive operations across the network, which increases processor costs and elapsed times.

  Stored procedures can encapsulate many SQL statements into a single message to the DB2 server. The network traffic of stored procedures is a single send and receive operation for a series of SQL statements.

  Locks on DB2 tables are not held across network transmissions, which reduces contention for resources at the server.

• Accesses tables from a dynamic SQL environment in which table privileges for the application that is running are undesirable.

  Stored procedures allow static SQL authorization from a dynamic environment.

• Accesses host variables for which you want to check security and integrity.

  Stored procedures remove SQL applications from the workstation, preventing workstation users from manipulating the contents of sensitive SQL statements and host variables.

• Creates a result set of rows to return to the client application.