The CREATE PROCEDURE (External) statement defines an external 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:
If the external program or service program exists, 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 about 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-' >--+-----------------------------------------+--option-list---->< '-(--+-------------------------------+--)-' | .-,-------------------------. | | V | | '-----parameter-declaration---+-'
parameter-declaration .-IN----. |--+-------+--+----------------+--data-type--+-----------------------+--| +-OUT---+ '-parameter-name-' '-AS -+-LOCATOR-------+-' '-INOUT-' '-XML-cast-type-' data-type |--+-built-in-type------+---------------------------------------| +-distinct-type-name-+ '-array-type-name----' XML-cast-type .-(--1--)-------. |--+-+-+-CHARACTER-+--+---------------+--+--------------+-----------------------------------------------+-+--| | | '-CHAR------' '-(--integer--)-' '-ccsid-clause-' | | | +-+-+-CHARACTER-+--VARYING-+--(--integer--)--+--------------+--------------------------------------+ | | | | '-CHAR------' | '-ccsid-clause-' | | | | '-VARCHAR----------------' | | | | .-(--1M--)-------------. | | | '-----+-+-CHARACTER-+--LARGE OBJECT-+------+----------------------+--+--------------+--+---------+-' | | | '-CHAR------' | '-(--integer--+---+--)-' '-ccsid-clause-' '-LOCATOR-' | | '-CLOB------------------------' +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | +-+---GRAPHIC----+---------------+--+--------------+--------------------+------------------------------+ | | '-(--integer--)-' '-ccsid-clause-' | | | +-+-GRAPHIC VARYING-+--(--integer--)--+--------------+----------------+ | | | '-VARGRAPHIC------' '-ccsid-clause-' | | | | .-(--1M--)-------------. | | | '---DBCLOB----+----------------------+--+--------------+--+---------+-' | | '-(--integer--+---+--)-' '-ccsid-clause-' '-LOCATOR-' | | +-K-+ | | +-M-+ | | '-G-' | | .-(--1--)-------. | '-+-+-BINARY--+---------------+---------+------------------------------+-------------------------------' | | '-(--integer--)-' | | | '-+-BINARY VARYING-+--(--integer--)-' | | '-VARBINARY------' | | .-(--1M--)-------------. | '---+-BLOB----------------+----+----------------------+--+---------+-' '-BINARY LARGE OBJECT-' '-(--integer--+---+--)-' '-LOCATOR-' +-K-+ +-M-+ '-G-'
option-list (1) |--+-----------------------+------------------------------------> '-LANGUAGE--+-C-------+-' +-C++-----+ +-CL------+ +-COBOL---+ +-COBOLLE-+ +-JAVA----+ +-PLI-----+ +-REXX----+ +-RPG-----+ '-RPGLE---' .-PARAMETER STYLE SQL--------------------. >--+----------------------------------------+-------------------> '-+-PARAMETER STYLE GENERAL------------+-' +-PARAMETER STYLE GENERAL WITH NULLS-+ +-PARAMETER STYLE JAVA---------------+ '-PARAMETER STYLE DB2GENERAL---------' .-NOT DETERMINISTIC-. .-MODIFIES SQL DATA-. >--+-------------------+--+-------------------+-----------------> '-DETERMINISTIC-----' +-READS SQL DATA----+ +-CONTAINS SQL------+ '-NO SQL------------' .-CALLED ON NULL INPUT . .-INHERIT SPECIAL REGISTERS-. >--+----------------------+--+---------------------------+------> .-DYNAMIC RESULT SETS--0-------. .-NO DBINFO-. >--+------------------------------+--+-----------+--------------> '-DYNAMIC RESULT SETS--integer-' '-DBINFO----' .-FENCED-----. >--+---------------------+--+------------+----------------------> +-DISALLOW DEBUG MODE-+ '-NOT FENCED-' +-ALLOW DEBUG MODE----+ '-DISABLE DEBUG MODE--' >--+-------------------+----------------------------------------> +-PROGRAM TYPE MAIN-+ '-PROGRAM TYPE SUB--' .-EXTERNAL-----------------------------. >--+--------------------------------------+---------------------> '-EXTERNAL NAME--external-program-name-' .-OLD SAVEPOINT LEVEL-. >--+-------------------------+--+---------------------+---------> '-SPECIFIC--specific-name-' '-NEW SAVEPOINT LEVEL-' .-COMMIT ON RETURN NO--. >--+----------------------+-------------------------------------| '-COMMIT ON RETURN YES-'
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--+---------+-' | +---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 maximum number of parameters allowed in CREATE PROCEDURE depends on the language and the parameter style:
The maximum number of parameters is also limited by the maximum number of parameters allowed by the licensed program used to compile the external program or service program.
A DataLink or a distinct type based on a DataLink cannot be specified as an output parameter.
A DataLink or a distinct type based on a DataLink cannot be specified as an input and output parameter.
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. Array types are only supported for LANGUAGE
JAVA. To use an array type as a parameter for a Java™ stored procedure, the parameter style must
be JAVA.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 before 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 invoked.
Some data types are not supported in all languages. For details on the mapping between the SQL data types and host language data types, see Embedded SQL Programming topic collection. Built-in data type specifications can be specified if they correspond to the language that is used to write the procedure.
Any parameter that has an XML
type must specify either the XML-cast-type clause or the AS
LOCATOR clause.
AS XML-cast-type
Specifies the data type passed to the procedure for a parameter
that is XML type or a distinct type based on XML type. If LOCATOR
is specified, the parameter is a locator to the value rather than
the actual value.
If a CCSID value is specified, only
Unicode CCSID values can be specified for graphic data types. If a
CCSID value is not specified, the CCSID is established at the time
the procedure is created according to the SQL_XML_DATA_CCSID QAQQINI
option setting. The default CCSID is 1208. See XML Values for a description of this option.

The user may set the SQLSTATE to any valid value in the external program to return an error or warning from the procedure.
PARAMETER STYLE SQL cannot be used with LANGUAGE JAVA.
PARAMETER STYLE DB2GENERAL can only be specified with LANGUAGE JAVA. For details on passing parameters in JAVA, see the IBM® Developer Kit for Java.
PARAMETER STYLE GENERAL cannot be used with LANGUAGE JAVA.
PARAMETER STYLE GENERAL WITH NULLS cannot be used with LANGUAGE JAVA.
PARAMETER STYLE JAVA can only be specified with LANGUAGE JAVA. For increased portability, you should write Java procedures that use the PARAMETER STYLE JAVA conventions. For details on passing parameters in JAVA, see the IBM Developer Kit for Java topic collection.
Note that the language of the external procedure determines how the parameters are passed. For example, in C, any VARCHAR or CHAR parameters are passed as NUL-terminated strings. For more information, see the SQL Programming topic collection. For Java routines, see the IBM Developer Kit for Java topic collection.
If no DYNAMIC RESULT SETS clause is specified, result sets are returned for all cursors that remain open when the procedure ends.
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 that are 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 a 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 before returning from the procedure, the result set starts with the 6th row of the result set.
Cursor result
sets are only returned if the external program does not have an attribute
of ACTGRP(*NEW).
For more information about result sets, see SET RESULT SETS.
DEBUG MODE can only be specified with LANGUAGE JAVA.
The argument is a structure that contains information such as the name of the current server, the application run-time authorization ID, and identification of the version and release of the database manager that called the procedure. See Table 1 for further details. Detailed information about the DBINFO structure can be found in include sqludf in the appropriate source file in library QSYSINC. For example, for C, sqludf can be found in QSYSINC/H.
DBINFO is only allowed with PARAMETER STYLE SQL.
| Field | Data Type | Description |
|---|---|---|
| Relational database | VARCHAR(128) | The name of the current server. |
| Authorization ID | VARCHAR(128) | The run-time authorization ID. |
| CCSID information | INTEGER
INTEGER
INTEGER
INTEGER
CHAR(8) |
The CCSID information of the job.
Three sets of three CCSIDs are returned. The following information
identifies the three CCSIDs in each set:
Each set of CCSIDs is for a different encoding scheme (EBCDIC, ASCII, and Unicode). If a CCSID is not explicitly specified for a parameter on the CREATE PROCEDURE statement, the input string is assumed to be encoded in the CCSID of the job at the time the procedure is executed. If the CCSID of the input string is not the same as the CCSID of the parameter, the input string passed to the external procedure will be converted before calling the external program. |
| Target column | VARCHAR(128) VARCHAR(128) VARCHAR(128) |
Not applicable for a call to a procedure. |
| Version and release | CHAR(8) | The version, release, and modification level of the database manager. |
| Platform | INTEGER | The server's platform type. |
COMMENT, GRANT, LABEL, or REVOKE
operations
are performed on the procedure.The validity of the name is checked at the application server. If the format of the name is not correct, an error is returned.
The external program or service program need not exist at the time the procedure is created, but it must exist at the time the procedure is called.
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.
The commit operation includes the work that is performed by the calling application process and the procedure.2
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.
General considerations for defining procedures: See CREATE PROCEDURE for general information about defining procedures.
Language considerations: For information needed to create the programs for a procedure, see Embedded SQL Programming.


Error handling considerations: Values of arguments passed to a procedure which correspond to OUT parameters are undefined and those which correspond to INOUT parameters are unchanged when an error is returned by the procedure.
Creating the procedure: When an external procedure associated with an ILE external program or service program is created, an attempt is made to save the procedure's attributes in the associated program or service program object. If the *PGM object is saved and then restored to this or another system, the catalogs are automatically updated with those attributes.
The attributes can be saved for external procedures subject to the following restrictions:
If system naming is specified and the external program name is not qualified, the external program must be found in the library list.
If the object cannot be updated, the procedure will still be created.
During restore of the procedure:
Calling 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.
When an external procedure is invoked, it runs in whatever activation group was specified when the external program or service program was created. However, ACTGRP(*CALLER) should normally be used so that the procedure runs in the same activation group as the calling program.
Notes for Java procedures: To be able to run Java procedures, you must have the IBM Developer Kit for Java installed on your system. Otherwise, an SQLCODE of -443 will be returned and a CPDB521 message will be placed in the job log.
If an error occurs while running a Java procedure, an SQLCODE of -443 will be returned. Depending on the error, other messages may exist in the job log of the job where the procedure was run.
Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:
Example 1: Create the procedure definition for a 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) LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'parts.onhand'
Example 2: Create the procedure definition for a 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) LANGUAGE C PARAMETER STYLE GENERAL DYNAMIC RESULT SETS 1 FENCED EXTERNAL NAME ASSEMBLY