CREATE PROCEDURE

Invocation

This statement can only be dynamically prepared, but the DYNAMICRULES run behavior must be specified implicitly or explicitly. It is intended to be processed using one of the following methods:

• JCL
• The DB2 UDB for z/OS SQL procedure processor (DSNTPSMP)

Issuing the CREATE PROCEDURE statement from another context will result in an incomplete procedure definition even though the statement processing returns without error.

Authorization

The privilege set that is defined below must include at least one of the following:

• The CREATEIN privilege on the schema
• SYSADM or SYSCTRL authority

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

The privilege set is the privileges that are held by the SQL authorization ID of the process.

If the statement is dynamically prepared, the privilege set is the privileges that are held by the SQL authorization ID of the process. The specified procedure name can include a schema name (a qualifier). However, if the schema name is not the same as the SQL authorization ID, one of the following conditions must be met:

• The privilege set includes SYSADM or SYSCTRL authority.
• The SQL authorization ID of the process has the CREATEIN privilege on the schema.

The authorization ID that is used to create the stored procedure must have authority to create programs that are to be run either in the DB2-established stored procedure address space or the specified workload manager (WLM) environment.

Syntax

Bold text indicates clauses or options that are supported only syntactically.

CREATE PROCEDURE procedure-name
    [(parameter-declaration [,...])] LANGUAGE SQL
    option-list SQL-routine-body

parameter-declaration

[IN | OUT | INOUT] parameter-name parameter-type

parameter-type

{built-in-type | TABLE LIKE table-name AS LOCATOR}

built-in-type

[SMALLINT]
[INTEGER | INT]
[DECIMAL | DEC | NUMERIC} [(5,0) | (integer [,integer])]
[FLOAT [(53) | (integer)] | REAL [PRECISION] | DOUBLE}]
[{{CHARACTER | CHAR} [(1) | (integer)] | {CHARACTER | CHAR} VARYING (integer) | VARCHAR (integer) } 
    [FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII | UNICODE}] |
    {{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(1M) | (integer [K | M | G])] } 
    [FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII | 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}]]
[MODIFIES SQL DATA | READS SQL DATA | CONTAINS SQL]
[DETERMINISTIC | NOT DETERMINISTIC]
[FENCED]
[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]