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.
Authorization
The privileges held by the authorization ID of the statement must include as least one of the following:
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:
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--'
.-RESULT SETS 0---------.
>-----+-----------------------+--*------------------------------>
'-RESULT SETS--integer--'
>----EXTERNAL--+----------------------+--*--LANGUAGE--+-C----+-->
'-NAME--+-'string'---+-' '-JAVA-'
'-identifier-'
>----*--PARAMETER STYLE--+-DB2DARI----+--*---------------------->
'-DB2GENERAL-'
.-NOT DETERMINISTIC--. .-FENCED-----.
>-----+--------------------+--*----+------------+--------------->
'-DETERMINISTIC------' '-NOT FENCED-'
.-NULL CALL-.
>----*--+-----------+--*---------------------------------------><
|
Description
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 result can name is specified, the schema-name cannot begin with "SYS". Otherwise, an error (SQLSTATE 42939) is raised.
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.
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.
If NAME clause is not specified "NAME procedure-name" is assumed.
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.
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.
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.
Stored procedures located in any of these directories do not use any of the registered attributes.
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.
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.
The string specified is the 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. 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--'--------------------------------><
The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
Refer to Embedded SQL Programming Guide for details on passing parameters.
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 ...\sqllib\function\unfenced directory and the ...\sqllib\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.
Notes
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 DB2GENERAL
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 DB2DARI