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^® UDB 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 UDB applications. One of the advantages of using stored procedures is that for distributed applications, the execution 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 iSeries™ PDF

• Defining an external procedure
  The CREATE PROCEDURE statement for an external procedure names the procedure, defines the parameters and their attributes, and provides other information about the procedure that the system uses when it calls the procedure.

• Defining an SQL procedure
  The CREATE PROCEDURE statement for an SQL procedure names the procedure, defines the parameters and their attributes, provides other information about the procedure that is used when the procedure is called, and defines the procedure body.

• Calling a stored procedure
  The SQL CALL statement calls a stored procedure.

• Returning result sets from stored procedures
  In addition to returning output parameters, a stored procedure can return a result set (that is, a result table associated with a cursor opened in the stored procedure) to the application that issues the CALL statement. The application can then issue fetch requests to read the rows of the result set cursor.

• Parameter passing conventions for stored procedures and user-defined functions
  The CALL statement and a function call can pass arguments to programs written in all supported host languages and REXX procedures.

• Indicator variables and stored procedures
  Indicator variables can be used with the CALL statement to pass additional information to and from a procedure, provided that host variables are used for the parameters.

• Returning a completion status to the calling program
  SQL and external procedures return status information to the calling program in different ways.

 

Parent topic:

Routines

 

Related concepts

Stored procedures
Java SQL routines

 

Related reference

DRDA stored procedure considerations
CREATE PROCEDURE