Stored procedures

A procedure (often called a stored procedure) is a program that can be called to perform operations. A procedure can include both host language statements and SQL statements. Procedures in SQL provide the same benefits as procedures in a host language.

DB2® stored procedure support provides a way for an SQL application to define and then call a procedure through SQL statements. Stored procedures can be used in both distributed and nondistributed Db2 applications. One of the advantages of using stored procedures is that for distributed applications, the processing of one CALL statement on the application requester, or client, can perform any amount of work on the application server.

You may define a procedure as either an SQL procedure or an external procedure. An external procedure can be any supported high level language program (except System/36 programs and procedures) or a REXX procedure. The procedure does not need to contain SQL statements, but it may contain SQL statements. An SQL procedure is defined entirely in SQL, and can contain SQL statements that include SQL control statements.

Coding stored procedures requires that the user understand the following:

  • Stored procedure definition through the CREATE PROCEDURE statement
  • Stored procedure invocation through the CALL statement
  • Parameter passing conventions
  • Methods for returning a completion status to the program invoking the procedure.

You may define stored procedures by using the CREATE PROCEDURE statement. The CREATE PROCEDURE statement adds procedure and parameter definitions to the catalog tables SYSROUTINES and SYSPARMS. These definitions are then accessible by any SQL CALL statement on the system.

To create an external procedure or an SQL procedure, you can use the SQL CREATE PROCEDURE statement.

The following sections describe the SQL statements used to define and call the stored procedure, information about passing parameters to the stored procedure, and examples of stored procedure usage.

For more information about stored procedures, see Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal Database for iSeriesLink to PDF