CREATE PROCEDURE statement (overview)
The CREATE PROCEDURE statement registers a stored procedure with a database server. You can register the following types of procedures with this statement, each of which is described separately.
- 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.
- 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.
See CREATE PROCEDURE statement (SQL - external procedure) (deprecated).
Notes for ALL procedure types
- Owner privileges
- The owner is authorized to call the procedure (EXECUTE privilege) and grant others the privilege to call the procedure. See GRANT statement (function or procedure privileges). For more information about ownership of the object, see Authorization, privileges, permissions, masks, and object ownership.
- Defining the parameters
- The input parameters for the procedure are specified as a list within parentheses.
A procedure can have no input parameters. In this case, an empty set of parentheses can be specified or omitted, for example:
CREATE PROCEDURE ASSEMBLY_PARTS()or
CREATE PROCEDURE ASSEMBLY_PARTS- Choosing data types for parameters:
-
When you choose the data types of the parameters for your stored procedure, consider the rules of promotion that can affect the values of the parameters. (See Promotion of data types). For example, a constant that is one of the input arguments to the stored procedure might have a built-in data type that is different from the data type that the procedure expects, and more significantly, might not be promotable to that expected data type. Based on the rules of promotion, using the following data types for parameters is recommended:
- INTEGER instead of SMALLINT
- DOUBLE instead of REAL
- VARCHAR instead of CHAR
- VARGRAPHIC instead of GRAPHIC
- VARBINARY instead of BINARY
For portability of functions across platforms that are not Db2 for z/OS®, do not use the following data types, which might have different representations on different platforms:
- FLOAT. Use DOUBLE or REAL instead.
- NUMERIC. Use DECIMAL instead.
- Specifying the encoding scheme for parameters
- The encoding scheme of all of the parameters with a character or graphic string data type (both input and output parameters) must be the same—either all ASCII, all EBCDIC, or all UNICODE. If you specify the encoding scheme on the individual parameters, instead of using the PARAMETER CCSID to specify it for all parameters at once or allowing the encoding scheme to default to the system value, ensure that they all agree.
- Specifying AS LOCATOR for a parameter
- Passing a locator instead of a value can result in fewer bytes being passed in or out of the
procedure. This can be useful when the value of the parameter is very large. The AS LOCATOR clause
specifies that a locator to the value of the parameter is passed instead of the actual value.
Specify AS LOCATOR only for parameters with a LOB data type or a distinct type that is based on a
LOB data type.
AS LOCATOR cannot be specified for SQL procedures.
- Accessing result sets from nested stored procedures
- A stored procedure, user-defined function, or trigger cannot call a stored procedure that is defined with the COMMIT ON RETURN clause.
- Special registers in procedures
- The settings of the special registers of the caller are inherited by the procedure when called and restored upon return to the caller. Special registers may be changed within a procedure, but these changes do not affect the caller.
- Global variables in procedures
- The content of global variables that are referenced in routines is inherited from the caller.
Global variables can be modified in stored procedures, except when the stored procedure is called by
a trigger or a function.
If the procedure contains references to global variables, the level of SQL data access must be at least CONTAINS SQL. If the procedure contains SQL statements that modify global variables, the level of SQL data access must be MODIFIES SQL DATA.