Stored procedures

A stored procedure is a compiled program that can execute SQL statements and is stored at a local or remote DB2® server. You can invoke a stored procedure from an application program or from the command line processor. A single call to a stored procedure from a client application can access the database at the server several times.

A typical stored procedure contains two or more SQL statements and some manipulative or logical processing in a host language or SQL procedure statements. You can call stored procedures from other applications or from the command line. DB2 provides some stored procedures, but you can also create your own.

A stored procedure provides a common piece of code that is written only once and is maintained in a single instance that can be called from several different applications. Host languages can easily call procedures that exist on a local system, and SQL can call stored procedures that exist on remote systems. In fact, a major benefit of procedures in SQL is that they can be used to enhance the performance characteristics of distributed applications. With stored procedures, you can avoid network transfer of large amounts of data obtained as part of intermediate results in a long sequence of queries.

The following diagram illustrates the processing for an application that does not use stored procedures. The client application embeds SQL statements and communicates with the server separately for each statement. This application design results in increased network traffic and processor costs.
Figure 1. Processing without stored procedures
Begin figure summary.A diagram shows the interaction between embedded SQL in a client application and DB2. Detailed description available.
The following diagram illustrates the processing for an application that uses stored procedures. Because a stored procedure is used on the server, a series of SQL statements can be executed with a single send and receive operation, reducing network traffic and the cost of processing these statements.
Figure 2. Processing with stored procedures
Begin figure summary.The figure shows communication between a client application and the stored procedures region.Detailed description available.
Stored procedures are useful for client/server applications that do at least one of the following things:
  • Execute multiple remote SQL statements. Remote SQL statements can create many network send and receive operations, which results in increased processor costs. Stored procedures can encapsulate many of your application's SQL statements into a single message to the DB2 server, reducing network traffic to 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.
  • Access tables from a dynamic SQL environment where table privileges for the application that is running are undesirable. Stored procedures allow static SQL authorization from a dynamic environment.
  • Access host variables for which you want to guarantee security and integrity. Stored procedures remove SQL applications from the workstation, which prevents workstation users from manipulating the contents of sensitive SQL statements and host variables.
  • Create a result set of rows to return to the client application.
Stored procedures that are written in embedded static SQL provide the following additional advantages:
  • Better performance because static SQL is prepared at precompile time and has no run time overhead for access plan (package) generation.
  • Encapsulation enables programmers to write applications that access data without knowing the details of database objects.
  • Improved security because access privileges are encapsulated within the packages that are associated with the stored procedures. You can grant access to run a stored procedure that selects data from tables, without granting SELECT privilege to the user.
You can create one of the following types of stored procedures:
External stored procedures
A procedure that is written in a host language.
External SQL procedures
A procedure whose body is written entirely in SQL, but is created, implemented, and executed like other external stored procedures.
Native SQL procedures
A procedure with a procedural body that is written entirely in SQL and is created by issuing a single SQL statement, CREATE PROCEDURE. Native SQL procedures do not have an associated external application program.

DB2 also provides a set of stored procedures that you can call in your application programs to perform a number of utility, application programming, and performance management functions. These procedures are called supplied stored procedures. Typically, you create these procedures during installation or migration.