The CREATE PROCEDURE statement defines a procedure at the current server.
The following types of procedures can be defined:
The procedure program or service program is written in a programming language such as C, COBOL, or Java™. The external executable is referenced by a procedure defined at the current server along with various attributes of the procedure. See CREATE PROCEDURE (External).
The procedure is written exclusively in SQL. The procedure body is defined at the current server along with various attributes of the procedure. See CREATE PROCEDURE (SQL).
Choosing data types for parameters: For portability of procedures across platforms that are not DB2 UDB for iSeries, do not use the following data types, which might have different representations on different platforms:
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 based on a LOB data type.
AS LOCATOR cannot be specified for SQL procedures.
Determining the uniqueness of procedures in a schema: At the current server, each procedure signature must be unique. The signature of a procedure is the qualified procedure name combined with the number of the parameters (the data types of the parameters are not part of a procedure's signature). This means that two different schemas can each contain a procedure with the same name that have the same number of parameters. However, a schema must not contain two procedures with the same name that have the same number of parameters.
The specific name for a procedure: When defining multiple procedures with the same name and schema (with different number of parameters), it is recommended that a specific name also be specified. The specific name can be used to uniquely identify the procedure when dropping, granting to, revoking from, or commenting on the procedure.
If the SPECIFIC clause is not specified, a specific name is generated.
Special registers in procedures: The settings of the special registers of the invoker are inherited by the procedure when called and restored upon return to the invoker.