CREATE PROCEDURE statement

Related topics
CDL statements
ALTER-PROCEDURE-statement

The CREATE PROCEDURE statement defines a stored procedure.

CREATE PROCEDURE procedureOwner.procedureSchema.procedureName

This option specifies the fully qualified name of the new procedure.

VERSION versionNumber

This option specifies the version of the procedure.

ORIGIN

This option specifies the origin of the procedure. The following table lists valid values for the ORIGIN keyword.

ORIGIN keyword values

Value	Description
EXTERNAL	Specifies an external stored procedure
NATIVE	Specifies a native SQL stored procedure

PARAMETERS (ordinality, parmUsage, parmName, dataType, subType, locator)

This option specifies the parameters for a procedure.

Procedure parameter values

Value	Description
ordinality	Specifies the ordinal number of the parameter
parmUsage	Specifies whether the parameter is used as input, output, or both
parmName	Specifies the name of the parameter
dataType	Specifies the data type of the parameter
subType	Specifies the subtype of the distinct data type
locator	Indicates whether a locator to a value or the value itself is returned

ACTIVE

This option indicates whether to activate the version of the procedure.

ACTIVE keyword values

Value	Description
NO	Specifies that the version should not be recognized as the active version
YES	Specifies to activate the version

ADD_VERSION YES

This option specifies to create a new version of the procedure when you LIKE a native SQL stored procedure.

APPLCOMPAT

This option indicates the package compatibility level for SQL.

APPLCOMPAT keyword values

Value	Description
V10R1	Specifies that the SQL statements in the package are compatible with Db2 Version 10 Release 1
V11R1	Specifies that the SQL statements in the package are compatible with Db2 Version 11 Release 1
V12R1Mnnn	Specifies that the SQL statements in the package are compatible with Db2 Version 12 Release 1 Modification nnn
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

APPLICATION_ENCODING_SCHEME

This option specifies the encoding scheme for the procedure.

APPLICATION_ENCODING_SCHEME keyword values

Value	Description
ASCII	Specifies ASCII encoding
EBCDIC	Specifies EBCDIC encoding
UNICODE	Specifies Unicode encoding
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

ARCHIVE SENSITIVE

This option specifies whether references to archive-enabled tables in SQL statements are affected by the value of the SYSIBMADM.GET_ARCHIVE global variable.

ARCHIVE SENSITIVE keyword values

Value	Description
NO	Specifies that references are not affected
YES	Specifies that references are affected

ASUTIME n

This option specifies the number of CPU service units for the procedure. A value of 0 indicates that no limit exists on the number of units.

BUSINESS_TIME SENSITIVE

This option specifies whether references to application-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.

BUSINESS_TIME SENSITIVE keyword values

Value	Description
NO	Specifies that references are not affected
YES	Specifies that references are affected

COLLID

This option specifies the name of the package collection for the procedure. (The name of this collection is stored in the SYSIBM.SYSROUTINES table.)

COLLID keyword values

Value	Description
<NONE>	If a blank value is specified for the collection, this value specifies NO COLLID.
collectionID	Specifies the name of the package collection for the procedure

COMMENT

This option specifies the comments for a procedure.

COMMENT keyword values

Value	Description
remarks	Specifies a comment for the procedure
<NONE>	Specifies no comments for the procedure

COMMIT_ON_RETURN NO

This option specifies that Db2 should continue the unit of work.

COMMIT_ON_RETURN YES

This option specifies that Db2 should commit the unit of work immediately.

AUTONOMOUS

For native SQL procedures, this option specifies that Db2 should execute the SQL procedure in a unit of work that is independent from the calling application.

CONCENTRATE

(BMC.DB2.SPE2510)

This option specifies whether to cache a dynamic SQL statement that specifies literal constants as a separate unique statement entry in the dynamic statement cache instead of sharing an existing statement in the cache

CONCENTRATE keyword values

Value	Description
YES	Shares an existing statement in the dynamic statement cache
NO	Caches dynamic SQL statements as a separate unique statement entry in the dynamic statement cache

CONCURRENT_ACCESS_RESOLUTION

This option specifies the concurrent access resolution option to use for statements in a package.

CONCURRENT_ACCESS_RESOLUTION keyword values

Value	Description
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.
WAIT	Specifies the WAITFOROUTCOME option
USE	Specifies the USECURRENTLYCOMMITTED option

CURRENT_DATA

This option indicates whether to require data currency for cursors when the isolation level of cursor stability is in effect.

CURRENT_DATA keyword values

Value	Description
NO	Specifies to not require data currency
YES	Specifies to require data currency

DATE_FORMAT

This option specifies the format for the date for the procedure.

DATE_FORMAT keyword values

Value	Description
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.
EUR	Specifies the IBM European standard (dd.mm.yyyy)
ISO	Specifies the International Standards Organization format (yyyy-mm-dd)
JIS	Specifies the Japanese Industrial Standard Christian era format (yyyy-mm-dd)
LOCAL	Specifies the installation-defined format
USA	Specifies the IBM USA standard (mm/dd/yyyy)

DBINFO

This option indicates whether to pass the DBINFO structure to the stored procedure.

DBINFO keyword values

Value	Description
NO	Specifies not to pass the DBINFO structure to the stored procedure
YES	Specifies to pass the DBINFO structure to the stored procedure

DEBUG_MODE

This option specifies whether the procedure is allowed for debugging.

DEBUG_MODE keyword values

Value	Description
ALLOW	Specifies to allow debugging
DISALLOW	Specifies not to allow debugging
DISABLE	Specifies to disable debugging
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

DECIMAL

This option specifies the rules for a decimal operation (DEC31 or DEC15).

DECIMAL keyword values

Value	Description
(x, y)	Specifies the decimal value ( x) and the scale ( y) The decimal value is either 31 or 15. Values for scale are 1 through 9.
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

DEFER_PREPARE

This option indicates whether to immediately prepare dynamic SQL statements or to defer preparation. The following table lists valid values for the DEFER_PREPARE keyword.

DEFER_PREPARE keyword values

Value	Description
YES	Specifies to defer the preparation of dynamic SQL statements
NO	Specifies to immediately prepare dynamic SQL statements
<DEFLT>	Specifies a default value or to inherit the option from the plan <DEFLT> is a reserved keyword.

DEGREE

This option specifies whether to attempt to run a query using parallel processing.

DEGREE keyword values

Value	Description
1	Specifies to prohibit parallel processing
ANY	Specifies to allow parallel processing

DETERMINISTIC

This option indicates whether to use indeterminate or deterministic results for a procedure.

DETERMINISTIC keyword values

Value	Description
NO	Specifies to use indeterminate results
YES	Specifies to use deterministic results

DYNAMICRULES

This option specifies the values for the dynamic SQL attributes.

DYNAMICRULES keyword values

Value	Description
RUN	Specifies to process dynamic SQL statements using standard attribute values
BIND	Specifies to process dynamic SQL statements using bind behavior
DEFINEBIND	Specifies to process dynamic SQL statements using define behavior or bind behavior
DEFINERUN	Specifies to process dynamic SQL statements using define behavior or run behavior
INVOKEBIND	Specifies to process dynamic SQL statements using invoke behavior or bind behavior
INVOKERUN	Specifies to process dynamic SQL statements using invoke behavior or run behavior

EXPLAIN

This option indicates whether to add information about the package’s statements to the owner of the PLAN_TABLE table.

EXPLAIN keyword values

Value	Description
NO	Specifies not to add information
YES	Specifies to add information

EXTERNAL_NAME externalName JAVAExternalName

This option specifies the module that Db2 should load to execute the procedure. For parameters for a JAVAExternalName, see JAVAExternalName.

IMMEDIATE_WRITE

This option indicates whether updates that are made to dependent page sets for group buffer pools should be immediately written.

IMMEDIATE_WRITE keyword values

Value	Description
NO	Specifies normal write activity
YES	Specifies to immediately write updated pages

ISOLATION_LEVEL

This option specifies the isolation level.

ISOLATION_LEVEL keyword values

Value	Description
CS	Specifies cursor stability
RR	Specifies repeatable read
RS	Specifies read stability
UR	Specifies uncommitted read

KEEP_DYNAMIC

This option indicates whether dynamic SQL statements are purged at each commit point.

KEEP_DYNAMIC keyword values

Value	Description
NO	Specifies to purge dynamic SQL statements
YES	Specifies to keep dynamic SQL statements

LANGUAGE

This option specifies the implementation language of the procedure.

LANGUAGE keyword values

Value	Description
ASSEMBLE	Specifies Assembler
C	Specifies C
COBOL	Specifies COBOL
PLI	Specifies PL/1
REXX	Specifies REXX
SQL	Specifies SQL
JAVA	Specifies JAVA

MAXIMUM_FAILURES

This option specifies the number of allowable failures for the procedure.

MAXIMUM_FAILURES keyword values

Value	Description
-1	Specifies to use the Db2 installation parameter (STORMXAB)
0	Specifies to never stop the procedure
n	Specifies the number of failures Valid values are 1 through 32767.

OPTHINT

This option specifies the value of the query optimization hint bind option.

OPTHINT keyword values

Value	Description
'value'	Specifies a character string to be used by the optimizer when searching for input to Db2.
<NONE>	Specifies that Db2 should not use optimization hints for SQL statements.

OWNER authorizationID

This option specifies the owner of the procedure. The OWNER parameter is included in the CDL statement only if the OVERRIDE( SPOWNER ) keyword is in the ALUIN input stream.

PACKAGE_OWNER

This option specifies the owner of the package.

PACKAGE_OWNER keyword values

Value	Description
owner	Specifies the authorization ID of the owner of the package
<NONE>	Specifies no owner for the package

PACKAGE_PATH

This option specifies the package path to use when the procedure executes.

PACKAGE_PATH keyword values

Value	Description
<NONE>	Specifies the package path of the program that invoked the procedure
'value1, value2, value3'	Specifies the package path

PARAMETER_CCSID

This option specifies the encoding scheme for the parameters for the procedure.

PARAMETER_CCSID keyword values

Value	Description
ASCII	Specifies ASCII encoding
EBCDIC	Specifies EBCDIC encoding
UNICODE	Specifies Unicode encoding
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

PARAMETER_STYLE

This option specifies the convention used to pass parameters to and return values from the procedure.

PARAMETER_STYLE keyword values

Value	Description
SQL	Specifies to pass parameters according to the DB2SQL standard convention
GENERAL_NO_NULLS	Specifies to pass parameters according to the GENERAL standard convention
GENERAL_WITH_NULLS	Specifies to pass parameters according to the GENERAL WITH NULLS standard convention
JAVA	Specifies to pass parameters according to the JAVA and SQLJ standard conventions

PARAMETER_VARCHAR

This option specifies the representation of variable length string parameters.

PARAMETER_VARCHAR keyword values

Value	Description
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.
NULLTERM	Specifies a null-terminated string form
STRUCTURE	Specifies a VARCHAR structure form

PROGRAM_TYPE

This option indicates whether the procedure runs as a main routine or a subroutine.

PROGRAM_TYPE keyword values

Value	Description
SUB	Specifies to run the procedure as a subroutine
MAIN	Specifies to run the procedure as a main routine

QUALIFIER

This option specifies the implicit qualifier for the unqualified object names in the SQL statements.

QUALIFIER keyword values

Value	Description
qualifier	Specifies the qualifier for the unqualified table, view, index, and alias names
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

RELEASE_AT

This option specifies the release resources when the package was last bound or rebound.

RELEASE_AT keyword values

Value	Description
COMMIT	Specifies the COMMIT value
DEALLOCATE	Specifies the DEALLOCATE value

RESULT_SETS value

This option specifies the number of query result sets that the procedure can return. Valid values are 0 through 32767.

REOPT

This option specifies whether to have Db2 determine an access path at runtime.

REOPT keyword values

Value	Description
NONE	Specifies to determine an access path at runtime
ALWAYS	Specifies to determine the access path at runtime each time that the statement runs
ONCE	Specifies to determine the access path only once

ROUNDING

This option specifies the rounding mode when the package is bound. The following table lists valid values for the ROUNDING keyword.

ROUNDING keyword values

Value	Description
DEC_ROUND_CEILING	Specifies to round toward infinity
DEC_ROUND_DOWN	Specifies to round toward zero
DEC_ROUND_FLOOR	Specifies to round toward negative infinity
DEC_ROUND_HALF_DOWN	Specifies to round to the nearest digit If the digit is equidistant, round down.
DEC_ROUND_HALF_EVEN	Specifies to round to the nearest digit If the digit is equidistant, round to an even digit.
DEC_ROUND_HALF_UP	Specifies to round to the nearest digit If the digit is equidistant, round up.
DEC_ROUND_UP	Specifies to round away from zero
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

RUN_OPTIONS

This option specifies the language environment runtime options for the procedure.

RUN_OPTIONS keyword values

Value	Description
runtimeOptions	Specifies the runtime options
<NONE>	Specifies to use the default runtime options

SECURITY

If the procedure accesses resources that are protected by an external security product, this option specifies the authorization ID.

SECURITY keyword values

Value	Description
DB2	Specifies the authorization ID for the workload manager (WLM) procedure address space
USER	Specifies the authorization ID of the user who invoked the procedure
DEFINER	Specifies the authorization ID of the owner

SPECIAL_REGISTERS

This option specifies how special registers are set.

SPECIAL_REGISTERS keyword values

Value	Description
INHERIT	Specifies that the values of special registers are inherited
DEFAULT	Specifies that the values of special registers are initialized to the default values

SQL_DATA_ACCESS

This option specifies which SQL statements can be executed in the procedure.

SQL_DATA_ACCESS keyword values

Value	Description
MODIFIES	Specifies that the procedure can execute only supported SQL statements
READS	Specifies that the procedure can execute SQL statements with READS SQL DATA, CONTAINS SQL, or NO SQL
CONTAINS	Specifies that the procedure can execute SQL statements with CONTAINS SQL or NO SQL
NONE	Specifies that the procedure can execute SQL statements with NO SQL

SQL_PATH

This option specifies the SQL path that Db2 uses to resolve unqualified types and names in the procedure.

SQL_PATH keyword values

Value	Description
DEFAULT	Specifies 'SYSIBM', 'SYSFUN', 'SYSPROC', and the value of the QUALIFIER option
USER	Specifies the value of the SESSION_USER or USER special register
schemaName	Specifies the name of the schema
schemaNameList	Specifies a list of schema names, separated by commas
<NONE>	Specifies no SQL path

STAY_RESIDENT

This option indicates whether the procedure load module remains resident in memory when the procedure ends.

STAY_RESIDENT keyword values

Value	Description
NO	Specifies to delete the load module from memory
YES	Specifies to retain the load module in memory

SYSTEM_TIME SENSITIVE

This option indicates whether references to system-period temporal tables in both static and dynamic SQL statements are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.

SYSTEM_TIME SENSITIVE keyword values

Value	Description
NO	Specifies that references are not affected
YES	Specifies that references are affected

TIME_FORMAT

This option specifies the time format for values.

TIME_FORMAT keyword values

Value	Description
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.
EUR	Specifies the IBM European standard (hh.mm.ss)
ISO	Specifies the International Standards Organization format (hh.mm.ss)
JIS	Specifies the Japanese Industrial Standard Christian Era format (hh:mm:ss)
LOCAL	Specifies the installation-defined format
USA	Specifies the IBM USA standard (hh:mm AM or PM)

FOR_UPDATE_CLAUSE

This option specifies whether the FOR UPDATE clause is required or optional for a DECLARE CURSOR statement.

FOR_UPDATE_CLAUSE keyword values

Value	Description
REQUIRED	Specifies that the clause is required
OPTIONAL	Specifies that the clause is optional
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.

VALIDATE

This option specifies whether to recheck errors.

ALIDATE keyword values

Value	Description
RUN	Specifies to issue a warning when the CREATE statement succeeds
BIND	Specifies to issue an error when the CREATE statement fails

WLM_ENVIRONMENT

This option specifies the workload manager (WLM) environment in which the procedure runs. The following table lists valid values for the WLM_ENVIRONMENT keyword.

WLM_ENVIRONMENT keyword values

Value	Description
<DEFLT>	Specifies a default value <DEFLT> is a reserved keyword.
WLMName	Specifies the name of the WLM environment

WLM_ENV_FOR_NESTED

For nested procedures, this option indicates which address space Db2 uses.

WLM_ENV_FOR_NESTED keyword values

Value	Description
YES	Specifies to use the address space specified in the WLM environment
NO	Specifies to use an address space other than the one specified in the WLM environment

TEXT storedProcedureBody

This option specifies the statements in the body of the SQL procedure.

JAVAExternalName

When the procedure is a JAVA procedure, JAVAExternalName uses the following parameters for an external name.

JAR_SCHEMA schema	This option specifies the schema of the JAR file.
JAR_ID ID	This option specifies the name of the JAR file.
EXTERNAL_NAME name	This option specifies the package.class.method that Db2 should load to execute the JAVA procedure.
JAVASIGNATURE signature	This option specifies the signature of the JAR file.

CREATE PROCEDURE statement

JAVAExternalName

ALTER and BMC AMI Change Manager for Db2 13.1

On this page