ALTER PROCEDURE statement

The ALTER PROCEDURE statement defines changes to a stored procedure.

ALTER PROCEDURE procedureOwner.procedureSchema.procedureName

This option specifies the fully qualified name of the procedure.

VERSION versionNumber

This option specifies the version of the procedure.

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

This option specifies the parameters for a procedure.

Procedure parameters 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.

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.
<BLANK>	Indicates the old value of the keyword when objects have been catmainted from an earlier version of Db2.

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. The following table lists valid values for the ARCHIVE SENSITIVE keyword.

ARCHIVE SENSITIVE keyword values

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

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.

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.

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, 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.

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.

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. 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.

When the procedure is a JAVA procedure, JAVAExternalName uses the parameters shown in JAVAExternalName.

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.

JAR_SCHEMA schema	This option specifies the schema of the JAR file.
JAR_ID name	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.

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. Values are 0 through 32767.

REOPT

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

REOPT keyword values

Value	Description
NONE	Specifies to determine an access path at run time.
ALWAYS	Specifies to determine the access path at run time 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.

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 run-time options for the procedure.

RUN_OPTIONS keyword values

Value	Description
runtimeOptions	Specifies the run-time options.
<NONE>	Specifies to use the default run-time options.

SECURITY

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

SECURITY keyword values

Value	Description
DB2	Specifies the authorization ID for the 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.

VALIDATE 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.

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.

ALTER PROCEDURE statement

ALTER and BMC AMI Change Manager for Db2 12.1

On this page