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).
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--'
.- DYNAMIC RESULT SETS 0--------. (1)
>-----+-------------------------------+---------*--------------->
'-DYNAMIC RESULT SETS--integer--'
.-MODIFIES SQL DATA--. .-NOT DETERMINISTIC--.
>-----+--------------------+--*----+--------------------+--*---->
| (2) | '-DETERMINISTIC------'
+-NO SQL-------------+
+-CONTAINS SQL-------+
'-READS SQL DATA-----'
(3)
.-CALLED ON NULL INPUT------.
>----+---------------------------+---*-------------------------->
>-----+-LANGUAGE--+-C-----+--*----| external-procedure-options |--*--+>
| +-JAVA--+ |
| +-COBOL-+ |
| '-OLE---' |
'-LANGUAGE--SQL---*----| SQL-procedure-body |------------------'
>--------------------------------------------------------------><
external-procedure-options
.-FENCED-----.
|---*---EXTERNAL--+----------------------+--*----+------------+->
'-NAME--+-'string'---+-' '-NOT FENCED-'
'-identifier-'
>----*---PARAMETER STYLE--+-DB2DARI------------+--*------------->
| (4) |
+-DB2GENERAL---------+
+-GENERAL------------+
+-GENERAL WITH NULLS-+
+-DB2SQL-------------+
'-JAVA---------------'
.-NO DBINFO--.
>-----+------------------------+--*----+------------+--*--------|
'-PROGRAM TYPE--+-SUB--+-' '-DBINFO-----'
'-MAIN-'
SQL-procedure-body
|---SQL-procedure-statement-------------------------------------|
Notes:
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 a two-part 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.
The value RESULT SETS may be used as a synonym for DYNAMIC RESULT SETS for backwards and family compatibility.
The following table indicates whether or not an SQL statement (specified in
the first column) is allowed to execute in a stored procedure with the
specified SQL data access indication. If an executable SQL statement is
encountered in a stored procedure defined with NO SQL, SQLSTATE 38001 is
returned. For other executions contexts, SQL statements that are not
supported in any context return SQLSTATE 38003. For other SQL
statements not allowed in a CONTAINS SQL context, SQLSTATE 38004 is returned
and in a READS SQL DATA context, SQLSTATE 38002 is returned. During
creation of an SQL procedure, a statement that does not match the SQL data
access indication will cause SQLSTATE 42895 to be returned.
Table 21. SQL Statement and SQL Data Access Indication
| SQL Statement | NO SQL | CONTAINS SQL | READS SQL DATA | MODIFIES SQL DATA |
|---|---|---|---|---|
| ALTER... | N | N | N | Y |
| BEGIN DECLARE SECTION | Y(1) | Y | Y | Y |
| CALL | N | Y(4) | Y(4) | Y(4) |
| CLOSE CURSOR | N | N | Y | Y |
| COMMENT ON | N | N | N | Y |
| COMMIT | N | N | N | N |
| COMPOUND SQL | N | Y | Y | Y |
| CONNECT(2) | N | N | N | N |
| CREATE | N | N | N | Y |
| DECLARE CURSOR | Y(1) | Y | Y | Y |
| DECLARE GLOBAL TEMPORARY TABLE | N | Y | Y | Y |
| DELETE | N | N | N | Y |
| DESCRIBE | N | N | Y | Y |
| DISCONNECT(2) | N | N | N | N |
| DROP ... | N | N | N | Y |
| END DECLARE SECTION | Y(1) | Y | Y | Y |
| EXECUTE | N | Y(3) | Y(3) | Y |
| EXECUTE IMMEDIATE | N | Y(3) | Y(3) | Y |
| EXPLAIN | N | N | N | Y |
| FETCH | N | N | Y | Y |
| FREE LOCATOR | N | Y | Y | Y |
| FLUSH EVENT MONITOR | N | N | N | Y |
| GRANT ... | N | N | N | Y |
| INCLUDE | Y(1) | Y | Y | Y |
| INSERT | N | N | N | Y |
| LOCK TABLE | N | Y | Y | Y |
| OPEN CURSOR | N | N | Y | Y |
| PREPARE | N | Y | Y | Y |
| REFRESH TABLE | N | N | N | Y |
| RELEASE CONNECTION(2) | N | N | N | N |
| RELEASE SAVEPOINT | N | N | N | Y |
| RENAME TABLE | N | N | N | Y |
| REVOKE ... | N | N | N | Y |
| ROLLBACK | N | Y | Y | Y |
| ROLLBACK TO SAVEPOINT | N | N | N | Y |
| SAVEPOINT | N | N | N | Y |
| SELECT INTO | N | N | Y | Y |
| SET CONNECTION(2) | N | N | N | N |
| SET INTEGRITY | N | N | N | Y |
| SET special register | N | Y | Y | Y |
| UPDATE | N | N | N | Y |
| VALUES INTO | N | N | Y | Y |
| WHENEVER | Y(1) | Y | Y | Y |
Notes:
LANGUAGE OLE is only supported for stored procedures stored in DB2 for Windows 32-bit operating systems.
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.
For OS/2, Windows 32-bit operating systems, 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.
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 32-bit operating systems 'd:\mylib\myproc' would cause the database manager to load the myproc.dll file from the d:\mylib directory.
If an 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 32-bit operating systems '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 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.
The string specified is the OLE programmatic identifier (progid) or class identifier (clsid), and method identifier (method_id), which the database manager invokes to execute the stored procedure being created by the statement. The programmatic identifier or class identifier, and the method identifier do not need to exist when the CREATE PROCEDURE statement is executed. However, when the procedure is used in the CALL statement, the method identifier must exist and be accessible from the database server machine, otherwise an error results (SQLSTATE 42724).
>>-'--+-progid-+---!--method_id--'-----------------------------><
'-clsid--'
The name must be enclosed in single quotes. Extraneous blanks are not permitted within the single quotes.
A progid is not interpreted by the database manager, but only forwarded to the OLE automation controller at run time. The specified OLE object must be creatable and support late binding (also known as IDispatch-based binding). By convention, progids have the following format:
<program_name>.<component_name>.<version>
Since it is only a convention, and not a rule, progids may in fact have a different format.
{nnnnnnnn-nnnn-nnnn-nnnn-nnnnnnnnnnnn}
where 'n' is an alphanumeric character. A clsid is not interpreted by the database manager but only forwarded to the OLE APIs at run time.
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 32-bit operating systems), then the FENCED or NOT FENCED registered attribute (and every other registered attribute) will be ignored.
| Note: | 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. Only FENCED can be specified for a stored procedure with LANGUAGE OLE.
The value DB2GENRL may be used as a synonym for DB2GENERAL.
Null indicators are NOT directly passed to the program.
The value SIMPLE CALL may be used as a synonym for GENERAL.
The value SIMPLE CALL WITH NULLS may be used as a synonym for GENERAL WITH NULLLS.
This can only be specified when LANGUAGE C, COBOL or OLE 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.
The default for PROGRAM TYPE is SUB. PROGRAM TYPE MAIN is only valid for LANGUAGE C or COBOL and PARAMETER STYLE GENERAL, GENERAL WITH NULLS or DB2SQL.
This clause currently does not impact processing of the stored procedure.
The value NULL CALL may be used as a synonym for CALLED ON NULL INPUT for backwards and family compatibility.
If DBINFO is specified, then a structure is passed to the stored procedure which contains the following information:
Please see the Application Development Guide for detailed information on the structure and how it is passed to the stored procedure.
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 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'
DYNAMIC RESULT SETS 1 NOT FENCED
LANGUAGE C PARAMETER STYLE GENERAL
Example 3: Create an SQL procedure that returns the median staff salary. Return a result set containing the name, position, and salary of all employees who earn more than the median salary.
CREATE PROCEDURE MEDIAN_RESULT_SET
(OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;
END