CREATE PROCEDURE

The following types of procedures can be defined:

• External

  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).

• SQL

  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).

Notes

Choosing data types for parameters: For portability of procedures across platforms that are not DB2® for i, 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 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 or XML data type or a distinct type based on a LOB or XML 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 specific-name is not specified, it is the same as the procedure name. If a function or procedure with that specific name already exists, a unique name is generated similar to the rules used to generate unique table names.

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.

Restore considerations: When a procedure's associated program or service program is saved and subsequently restored and the object was updated with the procedure attributes when the procedure was created, the saved attributes will be processed and possibly changed during the restore.

If the 'Saved library' (SAVLIB) of the program or service program is different from the 'Restore to library' (RSTLIB), the procedure's schema name, specific schema name, and the external name may be changed as a result of the restore.

• If the saved procedure schema name and the library name of the saved object match, the procedure schema will be changed to the 'Restore to library' (RSTLIB). Otherwise, the procedure schema name is the saved procedure schema name.
• The specific schema name is always the same as procedure schema name.
• If the saved EXTERNAL NAME library and the library name of the saved object match, the EXTERNAL NAME library will be changed to the 'Restore to library' (RSTLIB). Otherwise, the EXTERNAL NAME library is the saved library name. If the saved EXTERNAL NAME library is *LIBL, it will not change.

If the same procedure name and number of parameters already exists in the catalog:

• If the external program name or service program name is the same as the one that already exists in the catalog, the information in the catalog for that procedure will be replaced with the saved attributes (including the specific name).
• Otherwise, the saved attributes are not restored, and a warning (SQL9015) is issued.

If the same specific name already exists in the catalog, a warning is issued and a new specific name is generated. Otherwise, the specific name of the procedure is preserved.