The CREATE PROCEDURE (SQL) statement creates an SQL procedure at the current server.
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
The privileges held by the authorization ID of the statement must include at least one of the following:
If SQL names are specified and a user profile exists that has the same name as the library into which the procedure is created, and that name is different from the authorization ID of the statement, then the privileges held by the authorization ID of the statement must include at least one of the following:
If a distinct type or array type is referenced,
the privileges held by the authorization ID of the statement must
include at least one of the following: 
For each distinct type or array type identified in
the statement:
The USAGE privilege on the type, and
The system authority *EXECUTE on the library containing
the distinct type or array type

To replace an existing procedure, the privileges
held by the authorization ID of the statement must include at least
one of the following: 

The system authority *READ to the SYSPROCS catalog
view and SYSPARMS catalog table
For information on the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Function or Procedure and Corresponding System Authorities When Checking Privileges to a Distinct Type.
>>-CREATE--+------------+--PROCEDURE--procedure-name------------> '-OR REPLACE-' >--+-----------------------------------------+------------------> '-(--+-------------------------------+--)-' | .-,-------------------------. | | V | | '-----parameter-declaration---+-' >--option-list--+----------------------+--SQL-routine-body----->< '-SET OPTION-statement-'
parameter-declaration .-IN----. |--+-------+--parameter-name--data-type-------------------------| +-OUT---+ '-INOUT-' data-type |--+-built-in-type------+---------------------------------------| +-distinct-type-name-+ '-array-type-name----'
option-list .-LANGUAGE SQL-. (1) .-NOT DETERMINISTIC-. |--+--------------+------+-------------------+------------------> '-DETERMINISTIC-----' .-MODIFIES SQL DATA-. .-CALLED ON NULL INPUT . >--+-------------------+--+----------------------+--------------> +-READS SQL DATA----+ '-CONTAINS SQL------' .-INHERIT SPECIAL REGISTERS-. >--+---------------------------+--------------------------------> .-DYNAMIC RESULT SETS--0-------. >--+------------------------------+-----------------------------> '-DYNAMIC RESULT SETS--integer-' >--+-------------------------+--+---------------------+---------> '-SPECIFIC--specific-name-' +-DISALLOW DEBUG MODE-+ +-ALLOW DEBUG MODE----+ '-DISABLE DEBUG MODE--' .-FENCED-----. .-PROGRAM TYPE MAIN-. >--+------------+--+-------------------+------------------------> '-NOT FENCED-' '-PROGRAM TYPE SUB--' .-OLD SAVEPOINT LEVEL-. .-COMMIT ON RETURN NO--. >--+---------------------+--+----------------------+------------> '-NEW SAVEPOINT LEVEL-' '-COMMIT ON RETURN YES-' >--+---------------------------------------------------------------+--| | .-DEFAULT---------------------. | '-CONCURRENT ACCESS RESOLUTION--+-+-USE CURRENTLY COMMITTED-+-+-' | '-U-----------------------' | '-+-WAIT FOR OUTCOME-+--------' '-W----------------'
SQL-routine-body |--+-SQL-control-statement--------------------------+-----------| +-ALLOCATE CURSOR-statement----------------------+ +-ALLOCATE DESCRIPTOR-statement------------------+ +-ALTER FUNCTION-statement-----------------------+ +-ALTER PROCEDURE-statement----------------------+ +-ALTER SEQUENCE-statement-----------------------+ +-ALTER TABLE-statement--------------------------+ +-ASSOCIATE LOCATORS-statement-------------------+ +-COMMENT-statement------------------------------+ +-COMMIT-statement-------------------------------+ +-CONNECT-statement------------------------------+ +-CREATE ALIAS-statement-------------------------+ +-CREATE FUNCTION (External Scalar)-statement----+ +-CREATE FUNCTION (External Table)-statement-----+ +-CREATE FUNCTION (Sourced)-statement------------+ +-CREATE INDEX-statement-------------------------+ +-CREATE PROCEDURE (External)-statement----------+ +-CREATE SCHEMA-statement------------------------+ +-CREATE SEQUENCE-statement----------------------+ +-CREATE TABLE-statement-------------------------+ +-CREATE TYPE-statement--------------------------+ +-CREATE VIEW-statement--------------------------+ +-DEALLOCATE DESCRIPTOR-statement----------------+ +-DECLARE GLOBAL TEMPORARY TABLE-statement-------+ +-DELETE-statement-------------------------------+ +-DESCRIBE-statement-----------------------------+ +-DESCRIBE CURSOR-statement----------------------+ +-DESCRIBE INPUT-statement-----------------------+ +-DESCRIBE PROCEDURE-statement-------------------+ +-DESCRIBE TABLE-statement-----------------------+ +-DISCONNECT-statement---------------------------+ +-DROP-statement---------------------------------+ +-EXECUTE IMMEDIATE-statement--------------------+ +-GET DESCRIPTOR-statement-----------------------+ +-GRANT-statement--------------------------------+ +-INSERT-statement-------------------------------+ +-LABEL-statement--------------------------------+ +-LOCK TABLE-statement---------------------------+ +-MERGE-statement--------------------------------+ +-REFRESH TABLE-statement------------------------+ +-RELEASE-statement------------------------------+ +-RELEASE SAVEPOINT-statement--------------------+ +-RENAME-statement-------------------------------+ +-REVOKE-statement-------------------------------+ +-ROLLBACK-statement-----------------------------+ +-SAVEPOINT-statement----------------------------+ +-SELECT INTO-statement--------------------------+ +-SET CONNECTION-statement-----------------------+ +-SET CURRENT DEBUG MODE-statement---------------+ +-SET CURRENT DECFLOAT ROUNDING MODE-statement---+ +-SET CURRENT DEGREE-statement-------------------+ +-SET CURRENT IMPLICIT XMLPARSE OPTION-statement-+ +-SET DESCRIPTOR-statement-----------------------+ +-SET ENCRYPTION PASSWORD-statement--------------+ +-SET PATH-statement-----------------------------+ +-SET RESULT SETS-statement----------------------+ +-SET SCHEMA-statement---------------------------+ +-SET TRANSACTION-statement----------------------+ +-UPDATE-statement-------------------------------+ '-VALUES INTO-statement--------------------------'
built-in-type |--+-+---SMALLINT---+-------------------------------------------------------------------------------------+--| | +-+-INTEGER-+--+ | | | '-INT-----' | | | '---BIGINT-----' | | .-(5,0)------------------------. | +-+-+-DECIMAL-+-+--+------------------------------+----------------------------------------------------+ | | '-DEC-----' | | .-,0--------. | | | '-+-NUMERIC-+-' '-(--integer--+-----------+--)-' | | '-NUM-----' '-, integer-' | | .-(--53--)------. | +-+-FLOAT--+---------------+-+-------------------------------------------------------------------------+ | | '-(--integer--)-' | | | +-REAL---------------------+ | | | .-PRECISION-. | | | '-DOUBLE--+-----------+----' | | .-(--34--)-. | +---DECFLOAT--+----------+-----------------------------------------------------------------------------+ | '-(--16--)-' | | .-(--1--)-------. | +-+-+-+-CHARACTER-+--+---------------+----------+--+----------------+---------------------+------------+ | | | '-CHAR------' '-(--integer--)-' | +-FOR BIT DATA---+ | | | | '-+-+-CHARACTER-+--VARYING-+--(--integer--)-' +-FOR SBCS DATA--+ | | | | | '-CHAR------' | +-FOR MIXED DATA-+ | | | | '-VARCHAR----------------' '-ccsid-clause---' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+----------------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' +-FOR SBCS DATA--+ | | '-CLOB------------------------' +-K-+ +-FOR MIXED DATA-+ | | +-M-+ '-ccsid-clause---' | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+-------+--+--------------+-------------------------------------------+ | | '-(--integer--)-' | '-ccsid-clause-' | | +-+-GRAPHIC VARYING-+--(--integer--)---+ | | | '-VARGRAPHIC------' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+-' | | '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-+-NATIONAL CHARACTER-+--+---------------+----------+---------------------+--+------------------+-+ | | | +-NATIONAL CHAR------+ '-(--integer--)-' | | '-normalize-clause-' | | | | '-NCHAR--------------' | | | | | '-+-+-NATIONAL CHARACTER-+--VARYING-+--(--integer--)-' | | | | | +-NATIONAL CHAR------+ | | | | | | '-NCHAR--------------' | | | | | '-NVARCHAR------------------------' | | | | .-(--1M--)-------------. | | | '-----+-+-NATIONAL CHARACTER-+--LARGE OBJECT-+------+----------------------+-' | | | '-NCHAR--------------' | '-(--integer--+---+--)-' | | '-NCLOB--------------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+-+-BINARY--+---------------+---------+-----------------+--------------------------------------------+ | | | '-(--integer--)-' | | | | | '-+-BINARY VARYING-+--(--integer--)-' | | | | '-VARBINARY------' | | | | .-(--1M--)-------------. | | | '---+-BLOB----------------+----+----------------------+-' | | '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' | | +-K-+ | | +-M-+ | | '-G-' | +-+-DATE-------------------+---------------------------------------------------------------------------+ | | .-(--0--)-. | | | +-TIME--+---------+------+ | | | .-(--6--)-. | | | '-TIMESTAMP--+---------+-' | | .-(--200--)-----. | +---DATALINK--+---------------+--+--------------+------------------------------------------------------+ | '-(--integer--)-' '-ccsid-clause-' | +---ROWID----------------------------------------------------------------------------------------------+ '---XML------------------------------------------------------------------------------------------------' ccsid-clause |--CCSID--integer--+------------------+-------------------------| '-normalize-clause-' normalize-clause .-NOT NORMALIZED-. |--+-NORMALIZED-----+-------------------------------------------|
OR REPLACE
Specifies
to replace the definition for the procedure if one exists at the current
server. The existing definition is effectively dropped before the
new definition is replaced in the catalog with the exception that
privileges that were granted on the procedure are not affected. This
option is ignored if a definition for the procedure does not exist
at the current server. To replace an existing procedure, the specific-name and procedure-name of
the new definition must be the same as the specific-name and procedure-name of
the old definition, or the signature of the new definition must match
the signature of the old definition. Otherwise, a new procedure is
created.
For SQL naming, the procedure will be created in the schema specified by the implicit or explicit qualifier.
For system naming, the procedure will be created in the schema specified by the qualifier. If no qualifier is specified:
The schema-name cannot be QSYS2, QSYS, QTEMP, or SYSIBM.
The maximum number of parameters allowed in an SQL procedure is 1024.
If the name of the distinct type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.
array-type-name
Specifies an array type.If the name of the array type is unqualified, the database manager resolves the schema name by searching the schemas in the SQL path.

If a CCSID is specified, the parameter will be converted to that CCSID prior to passing it to the procedure. If a CCSID is not specified, the CCSID is determined by the default CCSID at the current server at the time the procedure is called.
Result sets are returned in the order in which the corresponding cursors are opened, unless a SET RESULT SETS statement is executed in the procedure. If the number of cursors still open for result sets when the procedure ends exceeds the maximum number specified on the DYNAMIC RESULT SETS clause, a warning is returned on the CALL statement and the number of result sets specified on the DYNAMIC RESULT SETS clause is returned.
If the SET RESULT SETS statement is issued, the number of results returned is the minimum of the number of result sets specified on this keyword and the SET RESULT SETS statement. If the SET RESULT SETS statement specifies a number larger than the maximum number of result sets, a warning is returned. Note that any result sets from cursors that have a RETURN TO CLIENT attribute are included in the number of result sets of the outermost procedure.
The result sets are scrollable if the cursor is used to return a result set and the cursor is scrollable. If a cursor is used to return a result set, the result set starts with the current position. Thus, if 5 FETCH NEXT operations have been performed prior to returning from the procedure, the result set will start with the 6th row of the result set.
For more information about result
sets, see SET RESULT SETS.
If DEBUG MODE is not specified, but a DBGVIEW option in the SET OPTION statement is specified, the procedure cannot be debugged by the Unified Debugger, but may be debugged by the system debug facilities. If neither DEBUG MODE nor a DBGVIEW option is specified, the debug mode used is from the CURRENT DEBUG MODE special register.
PROGRAM TYPE SUB procedures usually
perform slightly better than PROGRAM TYPE MAIN procedures. If the
number of parameters exceeds 400, PROGRAM TYPE MAIN will be used instead
of PROGRAM TYPE SUB.
The commit operation includes the work that is performed by the calling application process and the procedure.
If the procedure returns result sets, the cursors that are associated with the result sets must have been defined as WITH HOLD to be usable after the commit.
CONCURRENT ACCESS RESOLUTION
Specifies whether the database manager should wait for data that
is in the process of being updated. DEFAULT is the default.
SET OPTION DBGVIEW = *SOURCE
The default values for the options depend the options
in effect at create time.
For more information, see SET OPTION. The options CLOSQLCSR, CNULRQD, COMPILEOPT, NAMING, and SQLCA are not allowed in the CREATE PROCEDURE statement.
CONNECT, SET CONNECTION, RELEASE, DISCONNECT, and SET TRANSACTION statements are not allowed in a procedure that is running on a remote application server. COMMIT and ROLLBACK statements are not allowed in an ATOMIC SQL procedure or in a procedure that is running on a connection to a remote application server.
ALTER PROCEDURE (SQL), ALTER FUNCTION (SQL Scalar), and ALTER FUNCTION (SQL Table) with a REPLACE keyword are not allowed in an SQL-routine-body.
General considerations for defining procedures: See CREATE PROCEDURE for general information on defining procedures.
Procedure ownership: If SQL names were specified:
If system names were specified, the owner of the procedure is the user profile or group user profile of the job executing the statement.
Procedure authority: If SQL names are used, procedures are created with the system authority of *EXCLUDE on *PUBLIC. If system names are used, procedures are created with the authority to *PUBLIC as determined by the create authority (CRTAUT) parameter of the schema.
If the owner of the procedure is a member of a group profile (GRPPRF keyword) and group authority is specified (GRPAUT keyword), that group profile will also have authority to the procedure.


Error handling in procedures: Consideration should be given to possible exceptions that can occur for each SQL statement in the body of a procedure. Any exception SQLSTATE that is not handled within the procedure using a handler within a compound statement results in the exception SQLSTATE being returned to the caller of the procedure. Values of arguments passed to a procedure that correspond to OUT parameters are undefined and those that correspond to INOUT parameters are unchanged when an error is returned by the procedure.
Creating the procedure: When an SQL
procedure is created, SQL creates a temporary source file that will
contain C source code with embedded SQL statements. A program or service
program object is then created using the CRTPGM or CRTSRVPGM command.
The SQL options used to create the program are the options that are
in effect at the time the CREATE PROCEDURE statement is executed.
The program is created with ACTGRP(*CALLER).
When an SQL procedure is created, the procedure's
attributes are stored in the created program object. If the *PGM or
*SRVPGM object is saved and then restored to this or another system,
the catalogs are automatically updated with those attributes.
During restore of the procedure:
The specific procedure name is used as the name of the member in the source file and the name of the program object, if it is a valid system name. If the procedure name is not a valid system name, a unique name is generated. If a source file member with the same name already exists, the member is overlaid. If a module or a program with the same name already exists, the objects are not overlaid, and a unique name is generated. The unique names are generated according to the rules for generating system table names.
Invoking the procedure: If a DECLARE PROCEDURE statement defines a procedure with the same name as a created procedure, and a static CALL statement where the procedure name is not identified by a variable is executed from the same source program, the attributes from the DECLARE PROCEDURE statement will be used rather than the attributes from the CREATE PROCEDURE statement.
The CREATE PROCEDURE statement applies to static and dynamic CALL statements as well as to a CALL statement where the procedure name is identified by a variable.
SQL procedures must be called using the SQL CALL statement. When called, the SQL procedure runs in the activation group of the calling program.
Dependent objects: An SQL routine is dependent on objects that are referenced in the SQL-routine-body. The names of the dependent objects are stored in catalog view SYSROUTINEDEP. If the object reference in the SQL-routine-body is a fully qualified name or, in SQL naming, if an unqualified name is qualified by the current schema, then the schema name of the object in SYSROUTINEDEP will be set to the specified name or the value of the current schema. Otherwise, the schema name is not set to a specific schema name. If a name is not set to a specific schema name, then DROP and ALTER statements will not be able to determine whether the routine is dependent on the object being altered or dropped.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
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 DECIMAL(7,2)) LANGUAGE SQL DYNAMIC RESULT SETS 1 BEGIN DECLARE v_numRecords INTEGER DEFAULT 1; DECLARE v_counter INTEGER DEFAULT 0; DECLARE c1 CURSOR FOR SELECT salary FROM staff ORDER BY salary; DECLARE c2 CURSOR WITH RETURN FOR SELECT name, job, salary 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