SQL Reference

CREATE PROCEDURE

This statement is used to register a stored procedure with an application server.

Invocation

This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).

Authorization

The privileges held by the authorization ID of the statement must include as least one of the following:

SYSADM or DBADM authority
IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the procedure does not exist
CREATEIN privilege on the schema, if the schema name of the procedure refers to an existing schema.

To create a not-fenced stored procedure, the privileges held by the authorization ID of the statement must also include at least one of the following:

CREATE_NOT_FENCED authority on the database
SYSADM or DBADM authority.

To create a fenced stored procedure, no additional authorities or privileges are required.

If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.

Syntax

>>-CREATE PROCEDURE--------------------------------------------->
 
>----procedure-name--(--+----------------------------------------------+---)->
                        |  .-,---------------------------------------. |
                        |  V .-IN----.                               | |
                        '----+-------+---parameter-name--data-type---+-'
                             +-OUT---+
                             '-INOUT-'
 
>----*----+--------------------------+--*----------------------->
          '-SPECIFIC--specific-name--'
 
      .- DYNAMIC RESULT SETS 0--------.  (1)
>-----+-------------------------------+---------*--------------->
      '-DYNAMIC RESULT SETS--integer--'
 
>----EXTERNAL--+----------------------+--*--LANGUAGE--+-C-----+->
               '-NAME--+-'string'---+-'               +-JAVA--+
                       '-identifier-'                 '-COBOL-'
 
>----*--PARAMETER STYLE--+-DB2DARI------------+--*-------------->
                         |            (2)     |
                         +-DB2GENERAL---------+
                         +-GENERAL------------+
                         +-GENERAL WITH NULLS-+
                         +-DB2SQL-------------+
                         '-JAVA---------------'
 
      .-NOT DETERMINISTIC--.       .-FENCED-----.
>-----+--------------------+--*----+------------+--*------------>
      '-DETERMINISTIC------'       '-NOT FENCED-'
 
                                                (3)
      .-NO DBINFO--.     .-CALLED ON NULL INPUT------.
>-----+------------+--*--+---------------------------+--*------->
      '-DBINFO-----'
 
>-----+------------------------+-------------------------------><
      '-PROGRAM TYPE--+-SUB--+-'
                      '-MAIN-'

Notes:

RESULT SETS may be specified in place of DYNAMIC RESULT SETS.
DB2GENRL may be specified in place of DB2GENERAL, SIMPLE CALL may be specified in place of GENERAL and SIMPLE CALL WITH NULLS may be specified in place of GENERAL WITH NULLS.
NULL CALL may be specified in place of CALLED ON NULL INPUT.

Description

procedure-name

Names the procedure being defined. It is a qualified or unqualified name that designates a procedure. The unqualified form of procedure-name is an SQL identifier (with a maximum length of 18). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

The name, including the implicit or explicit qualifiers, together with the number of parameters must not identify a procedure described in the catalog (SQLSTATE 42723). The unqualified name, together with the number of the parameters, while of course unique within its schema, need not be unique across schemas.

The a two-part name is specified, the schema-name cannot begin with "SYS". Otherwise, an error (SQLSTATE 42939) is raised.

( IN | OUT | INOUT parameter-name data-type,...)

Identifies the parameters of the procedure, and specifies the mode, name and data type of each parameter. One entry in the list must be specified for each parameter that the procedure will expect.

It is possible to register a procedure that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example,

  CREATE PROCEDURE SUBWOOFER() ...

No two identically-named procedures within a schema are permitted to have exactly the same number of parameters. Lengths, precisions and scales are not considered in this type comparison. Therefore CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3). There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature raises an SQL error (SQLSTATE 42723).

For example, given the statements:

  CREATE PROCEDURE PART (IN NUMBER INT, OUT PART_NAME CHAR(35)) ...
  CREATE PROCEDURE PART (IN COST DECIMAL(5,3), OUT COUNT INT) ...

the second statement will fail because the number of parameters of the procedure are the same even if the data types are not.

IN | OUT | INOUT

Specifies the mode of the parameter.

IN - parameter is input only
OUT - parameter is output only
INOUT - parameter is both input and output

parameter-name

Specifies the name of the parameter.

data-type

Specifies the data type of the parameter.

SQL data type specifications and abbreviations which may be specified in the data-type definition of a CREATE TABLE statement and have a correspondence in the language that is being used to write the procedure may be specified. See the language-specific sections of the Application Development Guide for details on the mapping between the SQL data types and host language data types with respect to stored procedures.
User-defined data types are not supported (SQLSTATE 42601).

SPECIFIC specific-name

Provides a unique name for the instance of the procedure that is being defined. This specific name can be used when dropping the procedure or commenting on the procedure. It can never be used to invoke the procedure. The unqualified form of specific-name is an SQL identifier (with a maximum length of 18). The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another procedure instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised.

The specific-name may be the same as an existing procedure-name.

If no qualifier is specified, the qualifier that was used for procedure-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of procedure-name or an error (SQLSTATE 42882) is raised.

If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmsshhn.

DYNAMIC RESULT SETS integer

Indicates the estimated upper bound of returned result sets for the stored procedure. Refer to Returning Result Sets from Stored Procedures for more information.

The value RESULT SETS may be used as a synonym for DYNAMIC RESULT SETS for backwards and family compatabality.

EXTERNAL

This clause indicates that the CREATE PROCEDURE statement is being used to register a new procedure based on code written in an external programming language and adhering to the documented linkage conventions and interface.

If NAME clause is not specified "NAME procedure-name" is assumed.

NAME 'string'

This clause identifies the name of the user-written code which implements the procedure being defined.

The 'string' option is a string constant with a maximum of 254 characters. The format used for the string is dependent on the LANGUAGE specified.

For LANGUAGE C:
The string specified is the library name and procedure within the library, which the database manager invokes to execute the stored procedure being CREATEd. The library (and the procedure within the library) do not need to exist when the CREATE PROCEDURE statement is performed. However, when the procedure is called, the library and procedure within the library must exist and be accessible from the database server machine.
```
>>-'--+-library_id-------+---+-------------+---'---------------><
      '-absolute_path_id-'   '-!--proc_id--'
 
```
The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.

library_id
Identifies the library name containing the procedure. The database manager will look for the library in the .../sqllib/function/unfenced directory and the .../sqllib/function directory (UNIX-based systems), or ...\instance_name\function\unfenced directory and the ...\instance_name\function directory (OS/2, Windows 95 and Windows NT as specified by the DB2INSTPROF registry variable), where the database manager will locate the controlling sqllib directory which is being used to run the database manager. For example, the controlling sqllib directory in UNIX-based systems is /u/$DB2INSTANCE/sqllib.
If 'myproc' were the library_id in a UNIX-based system it would cause the database manager to look for the procedure in library /u/production/sqllib/function/unfenced/myfunc and /u/production/sqllib/function/myfunc, provided the database manager is being run from /u/production.
For OS/2, Windows 95 and Windows NT, the database manager will look in the LIBPATH or PATH if the library_id is not found in the function directory, and will be run as fenced.
Stored procedures located in any of these directories do not use any of the registered attributes.
absolute_path_id
Identifies the full path name of the procedure.
In a UNIX-based system, for example, '/u/jchui/mylib/myproc' would cause the database manager to look in /u/jchui/mylib for the myproc procedure.
In OS/2, Windows 95 and Windows NT 'd:\mylib\myproc' would cause the database manager to load the myproc.dll file from the d:\mylib directory.
If absolute path is specified, the procedure will run as fenced, ignoring the FENCED or NOT FENCED attribute.
! proc_id
Identifies the entry point name of the procedure to be invoked. The ! serves as a delimiter between the library id and the procedure id. If ! proc_id is omitted, the database manager will use the default entry point established when the library was linked.
In a UNIX-based system, for example, 'mymod!proc8' would direct the database manager to look for the library $inst_home_dir/sqllib/function/mymod and to use entry point proc8 within that library.
In OS/2, Windows 95 and Windows NT 'mymod!proc8' would direct the database manager to load the mymod.dll file and call the proc8() procedure in the dynamic link library (DLL).

If the string is not properly formed, an error (SQLSTATE 42878) is raised.
The body of every stored procedure should be in a directory which is mounted and available on every partition of the database.
For LANGUAGE JAVA:
The string specified contains the optional jar file identifier, class identifier and method identifier, which the database manager invokes to execute the stored procedure being CREATEd. The class identifier and method identifier do not need to exist when the CREATE PROCEDURE statement is performed. If a jar_id is specified, it must exist when the CREATE PROCEDURE statement is performed. However, when the procedure is called, the class identifier and the method identifier must exist and be accessible from the database server machine, otherwise an error (SQLSTATE 42884) is raised.
```
>>-'----+----------+--class_id--+-.-+---method_id--'-----------><
        '-jar_id :-'            '-!-'
 
```
The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.

jar_id
Identifies the jar identifier given to the jar collection when it was installed in the database. It can be either a simple identifier, or a schema qualified identifier. Examples are 'myJar' and 'mySchema.myJar'.
class_id
Identifies the class identifier of the Java object. If the class is part of a package, the class identifier part must include the complete package prefix, for example, 'myPacks.StoredProcs'. The Java virtual machine will look in directory '../myPacks/StoredProcs/' for the classes. In OS/2 and Windows 95 and Windows NT, the Java virtual machine will look in directory '..\myPacks\StoredProcs\'.
method_id
Identifies the method name with the Java class to be invoked.

NAME identifier

This identifier specified is an SQL identifier. The SQL identifier is used as the library-id in the string. Unless it is a delimited identifier, the identifier is folded to upper case. If the identifier is qualified with a schema name, the schema name portion is ignored. This form of NAME can only be used with LANGUAGE C.

LANGUAGE

This mandatory clause is used to specify the language interface convention to which the stored procedure body is written.

C: This means the database manager will call the stored procedure as if it were a C procedure. The stored procedure must conform to the C language calling and linkage convention as defined by the standard ANSI C prototype.
JAVA: This means the database manager will call the stored procedure as a method in a Java class.
COBOL: This means the database manager will call the procedure as if it were a COBOL procedure.

PARAMETER STYLE

This clause is used to specify the conventions used for passing parameters to and returning the value from stored procedures.

DB2DARI

This means that the stored procedure will use a parameter passing convention that conforms to C language calling and linkage conventions. This can only be specified when LANGUAGE C is used.

DB2GENERAL

This means that the stored procedure will use a parameter passing convention that is defined for use with Java methods. This can only be specified when LANGUAGE JAVA is used.

The value DB2GENRL may be used as a synonym for DB2GENERAL.

GENERAL

This means that the stored procedure will use a parameter passing mechanism where the stored procedure receives the parameters specified on the CALL. The parameters are passed directly as expected by the language, the SQLDA structure is not used. This can only be specified when LANGUAGE C or COBOL is used.

Null indicators are NOT directly passed to the program.

The value SIMPLE CALL may be used as a synonym for GENERAL.

GENERAL WITH NULLS

In addition to the parameters on the CALL statement as specified in GENERAL, another argument is passed to the stored procedure. This additional argument contains a vector of null indicators for each of the parameters on the CALL statement. In C, this would be an array of short ints. This can only be specified when LANGUAGE C or COBOL is used.

The value SIMPLE CALL WITH NULLS may be used as a synonym for GENERAL WITH NULLLS.

DB2SQL

In addition to the parameters on the CALL statement, the following arguments are passed to the stored procedure:

a NULL indicator for each parameter on the CALL statement
the SQLSTATE to be returned to DB2
the qualified name of the stored procedure
the specific name of the stored procedure
the SQL diagnostic string to be returned to DB2

This can only be specified when LANGUAGE C or COBOL is used.

JAVA

This means that the stored procedure will use a parameter passing convention that conforms to the Java language and SQLJ Routines specification. IN/OUT and OUT parameters will be passed as single entry arrays to facilitate returning values. This can only be specified when LANGUAGE JAVA is used.

PARAMETER STYLE JAVA procedures do not support the DBINFO or PROGRAM TYPE clauses.

Refer to Application Development Guide for details on passing parameters.

DETERMINISTIC or NOT DETERMINISTIC

This clause specifies whether the function always returns the same results for given argument values (DETERMINISTIC) or whether the function depends on some state values that affect the results (NOT DETERMINISTIC). That is, a DETERMINISTIC function must always return the same result from successive invocations with identical inputs.

This clause is not used by DB2 Universal Database.

FENCED or NOT FENCED

This clause specifies whether or not the stored procedure is considered "safe" to run in the database manager operating environment's process or address space (NOT FENCED), or not (FENCED).

If a stored procedure is registered as FENCED, the database manager insulates its internal resources (e.g. data buffers) from access by the procedure. All procedures have the option of running as FENCED or NOT FENCED. In general, a procedure running as FENCED will not perform as well as a similar one running as NOT FENCED.

If the stored procedure is located in .../sqllib/function/unfenced directory and the .../sqllib/function directory (UNIX-based systems), or ...\instance_name\function\unfenced directory and the ...\instance_name\function directory (OS/2, Windows 95 and Windows NT), then the FENCED or NOT FENCED registered attribute (and every other registered attribute) will be ignored.
Warning: Use of NOT FENCED for procedures not adequately checked out can compromise the integrity of DB2. DB2 takes some precautions against many of the common types of inadvertent failures that might occur, but cannot guarantee complete integrity when NOT FENCED stored procedures are used.

To change from FENCED to NOT FENCED, the procedure must be re-registered (by first dropping it and then re-creating it). Either SYSADM authority, DBADM authority or a special authority (CREATE_NOT_FENCED) is required to register a stored procedures as NOT FENCED.

NO DBINFO or DBINFO

Specifies whether specific information known by DB2 is passed to the stored procedure when it is invoked as an additional invocation-time argument (DBINFO) or not (NO DBINFO). NO DBINFO is the default.

If DBINFO is specified, then a structure is passed to the stored procedure which contains the following information:

Data base name - the name of the currently connected database.
Application ID - unique application ID which is established for each connection to the database.
Application Authorization ID - the application run-time authorization ID.
Code page - identifies the database code page.
Schema name - not applicable to stored procedures.
Table name - not applicable to stored procedures.
Column name - not applicable to stored procedures.
Database version/release - identifies the version, release and modification level of the database server invoking the stored procedure.
Platform - contains the server's platform type.
Table function result column numbers - not applicable to stored procedures.

DBINFO is not supported for PARAMETER STYLE JAVA.

Please see the Application Development Guide for detailed information on the structure and how it is passed to the stored procedure.

CALLED ON NULL INPUT

CALLED ON NULL INPUT always applies to stored procedures. This means that regardless if any arguments are null, the stored procedure is called. It can return a null value or a normal (non-null) value. Responsibility for testing for null argument values lies with the stored procedure.

The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatability.

PROGRAM TYPE

Specifies whether the stored procedure expects parameters in the style of a main routine or a subroutine.

SUB: The stored procedure expects the parameters to be passed as separate arguments.
MAIN: The stored procedure expects the parameters to be passed as an argument counter, and a vector of arguments (argc, argv). The name of the stored procedure to be invoked must also be "main". Stored procedures of this type must still be built in the same fashion as a shared library as opposed to a standalone executable.

The default for PROGRAM TYPE on is SUB. PROGRAM TYPE MAIN is only valid for LANGUAGE C and PROGRAM STYLE GENERAL, GENERAL WITH NULLS and DB2SQL.

Notes

For information on creating the programs for a stored procedure, see the Application Development Guide.
Creating a procedure with a schema name that does not already exist will result in the implicit creation of that schema provided the authorization ID of the statement has IMPLICIT_SCHEMA authority. The schema owner is SYSIBM. The CREATEIN privilege on the schema is granted to PUBLIC.

Examples

Example 1: Create the procedure definition for a stored procedure, written in Java, that is passed a part number and returns the cost of the part and the quantity that are currently available.

   CREATE PROCEDURE PARTS_ON_HAND (IN  PARTNUM  INTEGER, 
                                   OUT COST     DECIMAL(7,2), 
                                   OUT QUANTITY INTEGER)
                    EXTERNAL NAME 'parts.onhand' 
                    LANGUAGE JAVA PARAMETER STYLE JAVA

Example 2: Create the procedure definition for a stored procedure, written in C, that is passed an assembly number and returns the number of parts that make up the assembly, total part cost and a result set that lists the part numbers, quantity and unit cost of each part.

   CREATE PROCEDURE ASSEMBLY_PARTS (IN  ASSEMBLY_NUM  INTEGER, 
                                    OUT NUM_PARTS     INTEGER, 
                                    OUT COST DOUBLE)
                    EXTERNAL NAME 'parts!assembly' 
                    RESULT SETS 1 NOT FENCED
                    LANGUAGE C PARAMETER STYLE GENERAL

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]