Creating stored procedures

The process that you follow to create a stored procedure depends on the type of stored procedure that you want to create.

Before you begin

You must complete some configuration tasks for the Db2 environment before you can use any of the following types of procedures:

  • External stored procedures
  • Native SQL procedures that satisfy any of the following conditions:
    • Calls at least one external stored procedure, external SQL procedure, or user-defined function.
    • Defined with ALLOW DEBUG MODE or DISALLOW DEBUG MODE.
  • External SQL procedures (deprecated)
  • Db2-supplied stored procedures

For instructions, see Installation step 21: Configure Db2 for running stored procedures and user-defined functions or Migration step 22: Configure Db2 for running stored procedures and user-defined functions (optional).

Procedure

Begin general-use programming interface information.To create stored procedures, complete the following steps:

Follow the process for the type of stored procedure that you want to create, and issue a CREATE PROCEDURE statement to register the stored procedure with a database server.
You can create the following types of stored procedures:
Native SQL procedures
The procedure body is written exclusively in SQL statements, including SQL procedural language (SQL PL) statements. The procedure body is contained and specified in the procedure definition along with various attributes of the procedure. A package is generated for a native SQL procedure. It contains the procedure body, including control statements. It might sometimes also include statements generated by Db2. Each time that the procedure is invoked, the package executes one or more times.

All SQL procedures that are created with a CREATE PROCEDURE statement that does not specify the FENCED or EXTERNAL options are native SQL procedures. More capabilities are supported for native SQL procedures, they usually perform better than external SQL procedures, and no associated C program is generated for them.

For more information, see Creating native SQL procedures.

External stored procedures
The procedure body is an external program that is written in a programming language such as C, C++, COBOL, or Java and it can contain SQL statements. The source code for an external stored procedure is separate from the procedure definition and is bound into a package. The name of the external executable is specified as part of the procedure definition along with various attributes of the procedure. All programs must be designed to run using Language Environment. Your COBOL and C++ stored procedures can contain object-oriented extensions. Each time that the stored procedure is invoked, the logic in the procedure controls whether the package executes and how many times.

For more information, see Creating external stored procedures.

External SQL procedures (deprecated)
The procedure body is written exclusively in SQL statements, including SQL procedural language (SQL PL) statements. The procedure body is specified in the procedure definition along with various attributes of the procedure. A C program and an associated package are generated for an external SQL procedure. It contains the procedure body, including control statements. It might sometimes also include statements generated by Db2.Each time that the procedure is invoked, the package executes one or more times.

Native SQL procedures are more fully supported, easier to maintain, and typically perform better than external SQL procedures, which are deprecated.

For more information, see Creating external SQL procedures (deprecated).

End general-use programming interface information.