CREATE PROCEDURE is an executable command that can be issued interactively, embedded in a host language, or dynamically prepared.
To execute the CREATE PROCEDURE command, users must possess one (or more) privileges listed in the SYSADM or SYSCTRL authority.
Bold text indicates clauses or options that are supported only syntactically.
CREATE PROCEDURE procedure-name
[(parameter-declaration [,...])]
option-list
parameter-declaration
[IN | OUT | INOUT] [parameter-name] parameter-type
parameter-type
{data-type [AS LOCATOR] | TABLE LIKE {table-name | view-name} AS LOCATOR}
data-type
{built-in-type | distinct-type-name}
built-in-type
{SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [(5,0) | (integer [,integer])] |
{FLOAT [(1) | (integer)] | REAL [PRECISION] | DOUBLE} |
{{{CHARACTER | CHAR} [(1) | (integer)] | {{CHARACTER | CHAR} VARYING | VARCHAR} (integer)}
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {ASCII | EBCDIC | UNICODE}] |
{{CHARACTER |CHAR} LARGE OBJECT | CLOB} [(1M) | (integer [K | M | G])] [FOR {SBCS | MIXED} DATA]
[CCSID {ASCII | EBCDIC | UNICODE}] } |
{{GRAPHIC [(1) | (integer)] | VARGRAPHIC (integer) | DBCLOB [(1M) | (integer [K | M | G])]}
[CCSID {ASCII | EBCDIC | UNICODE}]} |
{{BINARY LARGE OBJECT | BLOB} [(1M) | (integer [K | M | G])]} |
{DATE | TIME | TIMESTAMP} |
ROWID}
option-list
[DYNAMIC RESULT SETS 0 | DYNAMIC RESULT SETS integer] [PARAMETER CCSID {ASCII | EBCDIC | UNICODE}] [EXTERNAL [NAME {'string' | identifier}]] [LANGUAGE {ASSEMBLE | C | COBOL | JAVA | PLI | REXX}] [MODIFIES SQL DATA | READS SQL DATA | CONTAINS SQL | NO SQL] [PARAMETER STYLE {SQL | GENERAL | GENERAL WITH NULLS | JAVA}] [DETERMINISTIC | NOT DETERMINISTIC] [FENCED] [DBINFO | NO DBINFO] [COLLID collection-id | NO COLLID] [WLM ENVIRONMENT {name | (name,*)}] [ASUTIME NO LIMIT | ASUTIME LIMIT integer] [STAY RESIDENT YES | STAY RESIDENT NO] [PROGRAM TYPE {SUB | MAIN}] [SECURITY {DB2 | USER | DEFINER}] [STOP AFTER SYSTEM DEFAULT FAILURES | STOP AFTER integer FAILURES | CONTINUE AFTER FAILURE] [RUN OPTIONS run-time-options] [COMMIT ON RETURN YES | COMMIT ON RETURN NO] [INHERIT SPECIAL REGISTERS | DEFAULT SPECIAL REGISTERS] [CALLED ON NULL INPUT]
| Procedure-name | If unqualified, the procedure name is any long identifier that uniquely names the procedure. A qualified procedure name is schema name (short identifier), a period, and then a long identifier. If unqualified, the schema name is the SQL authorization ID in the CURRENT SQLID special register.The schema name can be 'SYSIBM' or 'SYSPROC'. It can also be 'SYSTOOLS' if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with 'SYS' unless the schema name is 'SYSADM'. |
| Load-module-name | Load module name is a short identifier naming the procedure executable module. The load module name specifies the name of a DLL file. |
If no length is specified for CHAR, VARCHAR, GRAPHIC, or VARGRAPHIC, the default length is 1. If precision is not specified for DECIMAL, the default is DECIMAL (5,0).
Parameter-name:
A parameter name is a long identifier naming the parameter.
RESULT SETS clause:
Specifies the maximum number of result sets that the stored procedure can return to the invoking program. The default is RESULT SETS 0, indicating there are no result sets being returned.
PARAMETER STYLE clause:
Identifies the convention used to pass arguments to the stored procedure. When using GENERAL, none of the arguments can be null. GENERAL WITH NULLS passes an additional argument after the arguments on the CALL PROCEDURE statement. The additional argument is an array of null indicators, one for each of the defined arguments for the stored procedure.
PROGRAM TYPE clause:
For stored procedures defined as LANGUAGE COBOL or PLI, there is no difference between MAIN or SUB.
COMMIT ON RETURN clause:
This default for this clause is NO. If COMMIT ON RETURN YES is specified, the SQL engine will issue a commit when the stored procedure returns if the transaction is not in a must rollback state. If the stored procedure uses the WITH RETURN clause to define cursors that will be returned as result sets, the cursors must also be defined as WITH HOLD if COMMIT ON RETURN YES is specified or the cursors will be closed by the SQL engine.