ALTER PROCEDURE statement (SQL - native procedure)
The ALTER PROCEDURE statement changes the definition of an SQL procedure at the current server. The procedure options, parameter names, and routine body can be changed and additional versions of the procedure can be defined and maintained using the ALTER PROCEDURE statement.
For information about the SQL control statements that are supported in native SQL procedures, refer to SQL procedural language (SQL PL).
Invocation for ALTER PROCEDURE (SQL - native)
This can be dynamically prepared only if dynamic rules run behavior is in effect. For more information, see Authorization IDs and dynamic SQL.
Authorization for ALTER PROCEDURE (SQL - native)
The privilege set that is defined below must include at least one of the following:
- Ownership of the procedure
- The ALTERIN privilege on the schema
- System DBADM
- SYSCTRL authority
- SYSADM
- Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)
The authorization ID that matches the schema name implicitly has the ALTERIN privilege on the schema.
If the authorization ID that is used to alter the procedure has the installation SYSADM authority or the installation SYSOPR authority and if the current SQLID is set to SYSINSTL, the procedure is identified as system-defined procedure when the procedure definition is reevaluated.
Additional privileges might be required in the following situations:
- If SQL-routine-body is specified, the privilege set must include the privileges that are required to execute the statements in SQL-routine-body.
- If a user-defined type is referenced (as the data type of a parameter or SQL variable), the privilege set must also include at least one of the following privileges or authorities:
- Ownership of the user-defined type
- The USAGE privilege on the user-defined type
- System DBADM authority
- DATAACCESS authority
- SYSADM authority
- If the procedure uses a table as a parameter, the privilege set must also include at least one of the following privileges or authorities:
- Ownership of the table
- The SELECT privilege on the table
- DATAACCESS authority
- SYSADM authority
- If you specify the WLM ENVIRONMENT FOR DEBUG MODE clause, RACF® or an external security product is invoked to check the required authority for defining programs in the WLM environment. If the WLM environment access is protected in RACF, the privilege set must include the required authority. For more information, see Managing authorizations for creation of stored procedures in WLM environments.
- When defining a new version of a procedure (using the ADD VERSION clause) or when replacing an existing version (using the REPLACE VERSION clause), the privilege set must include the required authorization to add a new package or a new version of an existing package depending on the value of the BIND NEW PACKAGE field on installation panel DSNTIPP, or the privilege set must include SYSADM or SYSCTRL authority.
Additional authorization may be required on the SYSDUMMYx tables depending on the content of the procedure definition. See SYSDUMMYx tables.
Privilege set: If the statement is embedded in an application program, the privilege set is the privileges that are held by the owner of the package.
- The privilege set includes SYSADM authority
- The privilege set includes SYSCTRL authority
- The SQL authorization ID of the process has the ALTERIN privilege on the schema
- If the PACKAGE OWNER option is not specified, the role associated with the binder becomes the package owner.
- If the PACKAGE OWNER option is specified, the role specified in the PACKAGE OWNER option becomes the package owner. In a trusted context, the PACKAGE OWNER specified must be a role.
Syntax for ALTER PROCEDURE (SQL - native)
routine-specification:
parameter-declaration:
data-type:
built-in-type:
option-list: (The options can be specified in any order, but each option can be specified only one time.)
SQL-routine-body:
- 1 An ALTER FUNCTION (SQL scalar) statement or an ALTER PROCEDURE (SQL native) statement with an ADD VERSION or REPLACE clause is not allowed in an SQL-routine-body.
- 2 The COMMIT statement and the ROLLBACK statement (without the TO SAVEPOINT clause) must not be issued in a routine body if the routine is in the calling chain of an SQL routine, an external routine, or a trigger.
Description for ALTER PROCEDURE (SQL - native)
- procedure-name
- Identifies the procedure to alter. The procedure that is identified in
procedure-name must exist at the current server.
The procedure must not be obfuscated.
- ACTIVE VERSION or ALL VERSIONS or VERSION routine-version-id
- Identifies the version of the procedure that is to be changed, replaced, or regenerated depending on whether the ALTER, REPLACE, or REGENERATE keyword is specified. Important: Do not create additional versions of procedures that are supplied with Db2 by specifying the VERSION keyword. Only versions that are supplied with Db2 are supported. Additional versions of such routines cause the installation and configuration of the supplied routines to fail.
- ACTIVE VERSION
- Specifies that the currently active version of the procedure is to be changed, replaced, or regenerated.
ACTIVE VERSION is the default value.
- ALL VERSIONS
- Specifies that all of the versions of the procedure are to be changed. Only the following options can be changed when this option is specified:
- AUTONOMOUS or COMMIT ON RETURN
- VERSION routine-version-id
- Identifies the version of the procedure that is to be changed, replaced, or regenerated. routine-version-id is the version identifier that is assigned when the version is defined. routine-version-id must identify a version of the specified procedure that exists at the current server.
- ALTER
- Specifies that a version
of the procedure is to be changed.
When you change a procedure to add or replace a version of the procedure, any option that is not explicitly specified will use the existing value from the version of the procedure that is being changed.
- REPLACE
- Specifies that a version of the procedure is to be replaced.
Binding the replaced version of the procedure might result in a new access path even if the routine body is not changed.
When you replace a procedure, the data types, CCSID specifications, and character data attributes (FOR BIT/SBCS/MIXED DATA) of the parameters must be the same as the attributes of the corresponding parameters for the currently active version of the procedure. For options that are not explicitly specified, the system default values for those options are used, even if those options were explicitly specified for the version of the procedure that is being replaced. This is not the case for versions of the procedure that specified DISABLE DEBUG MODE. If DISABLE DEBUG MODE is specified for a version of a procedure, it cannot be changed by the REPLACE clause. When a procedure definition is replaced, any existing comments in the catalog for that definition of the procedure are removed.
- ADD VERSION routine-version-id
- Specifies that a new version of the procedure is to be created. routine-version-id is the version identifier for the new version of the procedure. routine-version-id must not identify a version of the specified procedure that already exists at the current server.
When you add a new version of a procedure the data types, CCSID specifications, and character data attributes (FOR BIT/SBCS/MIXED DATA) of the parameters must be the same as the attributes of the corresponding parameters for the currently active version of the procedure. The parameter names can differ from the other versions of the procedure. For options that are not explicitly specified, the system default values will be used.
- ACTIVATE VERSION routine-version-id
- Specifies the version of the procedure that is to be the currently active version of the procedure. routine-version-id is the version identifier that is assigned when the version of the procedure is defined. The version that is specified with routine-version-id is the version that will be invoked by the CALL statement, unless the value of the CURRENT ROUTINE VERSION special register overrides the currently active version of the procedure when the procedure is invoked. routine-version-id must identify a version of the procedure that already exists at the current server.
- REGENERATE
- Regenerates a version of the procedure. When Db2 maintenance is applied that changes how an SQL procedure is generated, the procedure might need to be regenerated to process the maintenance changes.
REGENERATE automatically rebinds, at the local server, the package for the SQL control statements for the procedure and rebinds the package for the SQL statements that are included in the procedure body. If a remote bind is also needed, the BIND PACKAGE COPY command must be explicitly done for all of the remote servers.
REGENERATE is different from a REBIND PACKAGE command where the SQL statements are rebound (i.e. to generate better access paths for those statements), but the SQL control statements in the procedure definition remain the same.
- USING APPLICATION COMPATIBILITY applcompat-level
- Specifies the application compatibility level used to regenerate the version of the procedure. The ALTER statement returns an error if the existing definition of the version includes syntax, semantics, or options that require a higher application compatibility level. This situation can occur when the version was most recently defined or regenerated while running at a higher application compatibility level than applcompat-level.
The following values can be specified:
VvvRrMmmm
-
Compatibility with the behavior of the identified Db2 function level. For example,
V12R1M510
specifies compatibility with the highest available Db2 12 function level. The equivalent function level or higher must be activated.For the new capabilities that become available in each application compatibility level, see:Tip: Extra program preparation steps might be required to increase the application compatibility level for applications that use data server clients or drivers to access Db2 for z/OS®. For more information, see Setting application compatibility levels for data server clients and drivers. - V12R1
- Compatibility with the behavior of Db2 12 function level 500. This value has the same result as specifying
V12R1M500
. - V11R1
- Compatibility with the behavior of Db2 11 new-function mode. After migration to Db2 12, this value has the same result as specifying
V12R1M100
. For more information, see V11R1 application compatibility level - V10R1
- Compatibility with the behavior of DB2® 10 new-function mode. For more information, see V10R1 application compatibility level.
If USING APPLICATION COMPATIBILITY is omitted, the regeneration uses the APPLCOMPAT value of the applicable SYSIBM.SYSPACKAGE catalog table row.
- DROP VERSION routine-version-id
- Drops the version of the
procedure that is identified with routine-version-id. routine-version-id is
the version identifier that is assigned when the version is defined. routine-version-id must
identify a version of the procedure that already exists at the current
server and must not identify the currently active version of the procedure.
Only the identified version of the procedure is dropped.
When only a single version of the procedure exists at the current server, use the DROP PROCEDURE statement to drop the procedure. A version of the procedure for which the version identifier is the same as the contents of the CURRENT ROUTINE VERSION special register can be dropped if that version is not the currently active version of the procedure.
- (parameter-declaration,…)
- Specifies the number of parameters of the procedure, the data
type and usage of each parameter, and the name of each parameter for
the version of the procedure that is being defined or changed. The
number of parameters and the specified data type and usage of each
parameter must match the data types in the corresponding position
of the parameter for all other versions of this procedure. Synonyms
for data types are considered to be a match.
IN, OUT, and INOUT specify the usage of the parameter. The usage of the parameters must match the implicit or explicit usage of the parameters of other versions of the same procedure.
- IN
- Identifies the parameter as an input
parameter to the procedure. The value of the parameter on entry to
the procedure is the value that is returned to the calling SQL application,
even if changes are made to the parameter within the procedure.
IN is the default.
- OUT
- Identifies the parameter as an output parameter that is returned by the procedure. If the parameter is not set within the procedure, the null value is returned.
- INOUT
- Identifies the parameter as both an input and output parameter for the procedure. If the parameter is not set within the procedure, its input value is returned.
- parameter-name
- Names the parameter for use as an SQL variable. The name cannot be the same as the name of any other parameter-name for this version of the procedure. The name of the parameter in this version of the procedure can be different than the name of the corresponding parameter for other versions of this procedure.
- built-in-type
- Specifies the data type of the parameter. See CREATE PROCEDURE statement (SQL - native procedure) for more information on data type specifications.
- distinct-type-name
- The data type of the input parameter is a distinct type. Any length, precision, scale, subtype, or encoding scheme attributes for the parameter are those of the source type of the distinct type. The distinct type must not be based on a LOB data type.
- array-type-name
- The data type of the input parameter is a
user-defined array type.
If you specify array-type-name without a schema name, Db2 resolves the array type by searching the schemas in the SQL path.
- NOT DETERMINISTIC or DETERMINISTIC
- Specifies whether the procedure returns the same results
each time it is called with the same IN and INOUT arguments.
- NOT DETERMINISTIC
- The procedure might not return the same result each time it is called with the same IN and INOUT
arguments, even when the data that is referenced in the database has not changed.
NOT DETERMINISTIC is the default.
- DETERMINISTIC
- The procedure always returns the same results each time it is called with the same IN and INOUT arguments if the data that is referenced in the database has not changed.
Db2 does not verify that the procedure code is consistent with the specification of DETERMINISTIC or NOT DETERMINISTIC.
- MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL
- Specifies the classification of SQL
statements and nested routines that this routine can execute or invoke. The database manager
verifies that the SQL statements issued by the procedure, and all routines locally invoked by the
routine, are consistent with this specification; the verification is not performed when nested
remote routines are invoked. For the classification of each statement, see SQL statement data access classification for routines.
- MODIFIES SQL DATA
- Specifies that the procedure can execute any SQL statement except statements that are not
supported in procedures.
MODIFIES SQL DATA is the default.
- READS SQL DATA
- Specifies that procedure can execute statements with a data access indication of READS SQL DATA or CONTAINS SQL. The procedure cannot execute SQL statements that modify data.
- CONTAINS SQL
- Specifies that the procedure can execute only SQL statements with an access indication of CONTAINS SQL. The procedure cannot execute statements that read or modify data.
- CALLED ON NULL INPUT
- Specifies that the procedure will be called if any, or even if all parameter values are null.
- DYNAMIC RESULT SETS integer
- Specifies the maximum number of query result sets that the procedure can return. The default is DYNAMIC RESULT SETS 0, which indicates that there are no result sets. The value must be in the range 0–32767.
- ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE
- Specifies
whether the version of the procedure can be run in debugging mode.
The default for a new version of a procedure is determined using the
value of the CURRENT DEBUG MODE special register.
- ALLOW DEBUG MODE
- Specifies that this version of the procedure can be run in debugging mode. When this version of the procedure is invoked and debugging is attempted, a WLM environment must be available.
- DISALLOW DEBUG MODE
- Specifies that the version of the procedure cannot be run in debugging
mode.
You can use a subsequent ALTER PROCEDURE statement to change this option to ALLOW DEBUG MODE.
- DISABLE DEBUG MODE
- Specifies that the version of the procedure can never be run in
debugging mode.
The version of the procedure cannot be changed to specify ALLOW DEBUG MODE or DISALLOW DEBUG MODE after the version of the procedure has been created, replaced, or altered to use DISABLE DEBUG MODE. To change DEBUG MODE for a version of a procedure that specifies DISABLE DEBUG MODE, you must drop and re-create the version of the procedure using the option that you want.
When DISABLE DEBUG MODE is in effect, the WLM ENVIRONMENT FOR DEBUG MODE option is ignored.
- PARAMETER CCSID
- Indicates
whether the encoding scheme for character or graphic string parameters
is ASCII, EBCDIC, or UNICODE. The default encoding scheme is the value
that is specified in the CCSID clauses of the parameter list or in
the field DEF ENCODING SCHEME on installation panel DSNTIPF.
This clause provides a convenient way to specify the encoding scheme for character or graphic string parameters. If individual CCSID clauses are specified for individual parameters in addition to this PARAMETER CCSID clause, the value that is specified in all of the CCSID clauses must be the same value that is specified in this clause.
If the data type for a parameter is a user-defined distinct type that is defined as a character or graphic type string, the CCSID of the distinct type must be the same as the value that is specified in this clause.
If the data type for a parameter is a user-defined array type that is defined with character or graphic string array elements, or a character string array index, the CCSID of these array attributes must be the same as the value that is specified in this clause.
This clause also specifies the encoding scheme that will be used for system-generated parameters of the routine.
- QUALIFIER schema-name
- Specifies the implicit qualifier that is used for unqualified object names that are referenced in the procedure body. For information about how the default for this option is determined, see Unqualified alias, index, JAR file, mask, permission, sequence, table, trigger, and view names.
- PACKAGE OWNER authorization-name
-
Specifies the owner of the package that is associated with the version of the procedure. The SQL authorization ID of the process is the default value.
This authorization ID must have the privileges required to execute the SQL statements that are contained in the body of the routine and must contain the necessary bind privileges. The value of the PACKAGE OWNER option is subject to translation when sent to a remote system.
Refer to the Authorization section of the BIND PACKAGE subcommand (DSN) command for authorization requirements.
- ASUTIME
- Specifies the total amount of processor time, in CPU service units,
that a single invocation of a procedure can run. The value is unrelated to the ASUTIME column of the
resource limit specification table.
When you are debugging a procedure, setting a limit can be helpful in case the procedure gets caught in a loop. For information on service units, see z/OS MVS Initialization and Tuning Guide.
- NO LIMIT
- Specifies that there is no limit on the number of CPU service units that the procedure can run.
NO LIMIT is the default.
- LIMIT integer
- The limit on the number of CPU service units is a positive integer in the range 1 - 2147483647. If the procedure uses more service units than the specified value, Db2 cancels the procedure. The CPU cycles that are consumed by parallel tasks in a procedure do not contribute towards the specified ASUTIME LIMIT.
- COMMIT ON RETURN NO, COMMIT ON RETURN YES, or AUTONOMOUS
- Indicates whether Db2 commits the transaction immediately on return
from the procedure.
- COMMIT ON RETURN NO
- Db2 does not issue a commit when the procedure returns. NO is the default.
- COMMIT ON RETURN YES,
- Db2 issues a commit when the procedure
returns if the following statements are true:
- The SQLCODE that is returned by the CALL statement is not negative.
- The procedure is not in a must-abort state.
The commit operation includes the work that is performed by the calling application process and by 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.
- AUTONOMOUS
- Db2 executes the SQL procedure in a
unit of work that is independent from the calling application.
When this option is
specified the procedure follows the rules of the COMMIT ON RETURN YES option before returning to the
calling application. However, it does not commit changes in the calling application. When autonomous
is specified:
- DYNAMIC RESULT SETS 0 must be in effect.
- Stored
procedure parameters must not be defined as:
- A LOB type
- The XML data type
- A distinct data type that is based on a LOB or XML value
- An array type that is defined with array elements that are a LOB type
A value must not be assigned to a global variable when an autonomous procedure is executing.
- INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS
- Specifies how special registers are set on entry to the routine.
- INHERIT SPECIAL REGISTERS
- Specifies that the
values of special registers are inherited, according to the rules
that are listed in the table for characteristics of special registers
in a procedure in Special registers in a user-defined function or a stored procedure.
INHERIT SPECIAL REGISTERS is the default.
- DEFAULT SPECIAL REGISTERS
- Specifies that special registers are initialized to the default values, as indicated by the rules in the table for characteristics of special registers in a procedure in Special registers in a user-defined function or a stored procedure.
- WLM ENVIRONMENT FOR DEBUG MODE name
- Specifies the WLM (workload manager) application environment used
by Db2 when debugging the procedure. The
name of the WLM environment is an SQL identifier.
If you do not specify WLM ENVIRONMENT FOR DEBUG MODE, Db2 uses the default WLM-established stored procedure address space that is specified at installation time.
The WLM ENVIRONMENT FOR DEBUG MODE value is ignored when DISABLE DEBUG MODE is in effect.
You must have the appropriate authority for the WLM application environment.
- DEFER PREPARE or NODEFER PREPARE
- Specifies
whether to defer preparation of dynamic SQL statements that refer
to remote objects, or to prepare them immediately.
The default depends on the value that is specified for the REOPT option. If REOPT NONE is specified, the default is NODEFER PREPARE. Otherwise, the default is DEFER PREPARE.
- DEFER PREPARE
- Specifies that the preparation of dynamic SQL statements that refer to remote objects will be
deferred.
For considerations with distributed processing, see DEFER and NODEFER bind options.
- NODEFER PREPARE
- Specifies that the preparation of dynamic SQL statements that refer to remote objects will not be deferred.
- CURRENT DATA
- Specifies whether to require data currency for read-only and ambiguous cursors when the isolation level of cursor stability is in effect. CURRENT DATA also determines whether block fetch can be used for distributed, ambiguous cursors. For more information about updating the current row of a cursor, block fetch, and data currency, see Choosing a CURRENTDATA option.
- YES
- Specifies that data currency is required for read-only and ambiguous cursors. Db2 acquired page or row locks to ensure data currency. Block fetch is not allowed for distributed, ambiguous cursors.
- NO
- Specifies that data currency is not required for read-only and ambiguous cursors. Block fetch is allowed for distributed, ambiguous cursors. Use of CURRENT DATA(NO) is not recommended if the procedure attempts to dynamically prepare and execute a DELETE WHERE CURRENT OF statement against an ambiguous cursor after that cursor is opened. You receive a negative SQLCODE if your procedure attempts to use a DELETE WHERE CURRENT OF statement for any of the following cursors:
- A cursor that is using block fetch
- A cursor that is using query parallelism
- A cursor that is positioned on a row that is modified by this or another application process
No is the default.
- DEGREE
- Specifies whether to attempt to run
a query using parallel processing to maximize performance.
- 1
- Specifies that parallel processing should not be used.
1 is the default.
- ANY
- Specifies that parallel processing can be used.
- CONCURRENT ACCESS RESOLUTION
- Specifies
the whether processing uses only committed data or whether it will
wait for commit or rollback of data that is in the process of being
updated.
- WAIT FOR OUTCOME
- Specifies that processing will wait for the commit or rollback of data that is in the process of being updated.
- USE CURRENTLY COMMITTED
- Specifies that processing use the currently committed version
of the data when data that is in the process of being updated is encountered. USE
CURRENTLY COMMITTED is applicable on scans that access
tables that are defined in universal table spaces with row or page
level lock size.
When there is lock contention between a read transaction and an insert transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS or RS. Applicable scans include intent read scans for read-only and ambiguous queries and for updatable cursors. USE CURRENTLY COMMITTED is also applicable to scans initiated from WHERE predicates of UPDATE or DELETE statements and the subselect of INSERT statements.
When there is lock contention is between a read transaction and a delete transaction, USE CURRENTLY COMMITTED is applicable to scans with isolation level CS and when CURRENT DATA NO is specified.
- DYNAMICRULES
- Specifies the values that
apply, at run time, for the following dynamic SQL attributes:
- The authorization ID that is used to check authorization
- The qualifier that is used for unqualified objects
- The source for application programming options that Db2 uses to parse and semantically verify dynamic SQL statements
DYNAMICRULES also specifies whether dynamic SQL statements can include GRANT, REVOKE, ALTER, CREATE, DROP, and RENAME statements.
In addition to the value of the DYNAMICRULES clause, the run time environment of a native SQL procedure controls how dynamic SQL statements behave at run time. The combination of the DYNAMICRULES value and the run time environment determines the value for the dynamic SQL attributes. That set of attribute values is called the dynamic SQL statement behavior. The following values can be specified:- RUN
- Specifies that dynamic SQL statements are to be processed using
run behavior.
RUN is the default.
- BIND
- Specifies that dynamic SQL statements are to be processed using bind behavior.
- DEFINEBIND
- Specifies that dynamic SQL statements are to be processed using either define behavior or bind behavior.
- DEFINERUN
- Specifies that dynamic SQL statements are to be processed using either define behavior or run behavior.
- INVOKEBIND
- Specifies that dynamic SQL statements are to be processed using either invoke behavior or bind behavior.
- INVOKERUN
- Specifies that dynamic SQL statements are to be processed using either invoke behavior or run behavior.
- APPLICATION ENCODING SCHEME
- Specifies the default encoding scheme for SQL variables in static
SQL statements in the procedure body. The value is used for defining an SQL variable in a compound
statement if the CCSID clause is not specified as part of the data type, and the PARAMETER CCSID
routine option is not specified.
- ASCII
- Specifies that the data is encoded using the ASCII CCSIDs of the server.
- EBCDIC
- Specifies that the data is encoded using the EBCDIC CCSIDs of the server.
- UNICODE
- Specifies that the data is encoded using the Unicode CCSIDs of the server.
For information about how the default for this option is determined, see ENCODING bind option.
- WITH EXPLAIN or WITHOUT EXPLAIN
- Specifies
whether information will be provided about how SQL statements in the
procedure will execute.
- WITHOUT EXPLAIN
- Specifies that information will not be provided about how SQL
statements in the procedure will execute.
You can get EXPLAIN output for a statement that is embedded in a native SQL procedure that is specified using WITHOUT EXPLAIN by embedding the SQL statement EXPLAIN in the procedure body. Otherwise, the value of the EXPLAIN option applies to all explainable SQL statements in the procedure body, and to the fullselect portion of any DECLARE CURSOR statements.
WITHOUT EXPLAIN is the default.
- WITH EXPLAIN
- Specifies that information will be provided about how SQL statements in the procedure will
execute. Information is inserted into the table owner.PLAN_TABLE.
owner is the authorization ID of the owner of the procedure package.
Alternatively, the authorization ID of the owner of the procedure can have an alias as
owner.PLAN_TABLE that points to the base table, PLAN_TABLE.
owner must also have the appropriate SELECT and INSERT privileges on that
table. WITH EXPLAIN does not obtain information for statements that access remote objects.
PLAN_TABLE must have a base table and can have multiple aliases with the same table name,
PLAN_TABLE, but have different schema qualifiers; it cannot be a view or a synonym. It should exist
before the version is added or replaced. In all inserts to
owner.PLAN_TABLE, the value of QUERYNO is the statement number that is
assigned by Db2.
The WITH EXPLAIN option also populates two optional tables, if they exist: DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE. DSN_STATEMNT_TABLE contains an estimate of the processing cost for an SQL statement.
For more information about the EXPLAIN statement, including a description of the tables that are populated by the WITH EXPLAIN option, see EXPLAIN statement.
- WITH IMMEDIATE WRITE or WITHOUT IMMEDIATE WRITE
- Specifies whether immediate writes are to be done
for updates that are made to group buffer pool dependent page sets or partitions. This option is
only applicable for data sharing environments. The IMMEDWRITE subsystem parameter has no affect of
this option. IMMEDWRITE bind option shows the implied hierarchy of the IMMEDWRITE
bind option (which is similar to this procedure option) as it affects run time.
- WITHOUT IMMEDIATE WRITE
- Specifies that normal write activity is performed. Updated pages that are group buffer pool
dependent are written at or before phase one of commit or at the end of abort for transactions that
have been rolled back.
WITHOUT IMMEDIATE WRITE is the default.
- WITH IMMEDIATE WRITE
- Specifies that updated pages that are group buffer pool dependent are immediately written as soon as the buffer update completes. Updated pages are written immediately even if the buffer is updated during forward progress or during the rollback of a transaction. WITH IMMEDIATE WRITE might impact performance.
- ISOLATION LEVEL RR, RS, CS, or UR
- Specifies how far to isolate the procedure from the effects of
other running applications. For information about isolation levels, see Choosing a RELEASE option.
- RR
- Specifies repeatable read.
- RS
- Specifies read stability.
- CS
- Specifies cursor stability. CS is the default.
- UR
- Specifies uncommitted read.
- WITH KEEP DYNAMIC or WITHOUT KEEP DYNAMIC
- Specifies whether Db2 keeps dynamic SQL statements after commit
points.
- WITHOUT KEEP DYNAMIC
- Specifies that Db2 does not keep
dynamic SQL statements after commit points.
WITHOUT KEEP DYNAMIC is the default.
- WITH KEEP DYNAMIC
- Specifies that Db2 keeps dynamic SQL statements after commit points. If you specify WITH KEEP DYNAMIC, the application does not need to prepare an SQL statement after every commit point. Db2 keeps the dynamic SQL statement until one of the following occurs:
- The application process ends
- A rollback operations occurs
- The application executes an explicit PREPARE statement with the same statement identifier as the dynamic SQL statement
If you specify WITH KEEP DYNAMIC, and the dynamic statement cache is active, the Db2 subsystem keeps a copy of the prepared statement in the cache. If the dynamic statement cache is not active, the subsystem keeps only the SQL statement string past a commit point. If the application executes an OPEN, EXECUTE, or DESCRIBE operation for that statement, the statement is implicitly prepared.
If you specify WITH KEEP DYNAMIC, DDF server threads that are used to execute procedures or packages that have this option in effect will remain active. Active DDF server threads are subject to idle thread time outs. For more information, see IDLE THREAD TIMEOUT field (IDTHTOIN subsystem parameter).
If you specify WITH KEEP DYNAMIC, you must not specify REOPT ALWAYS. WITH KEEP DYNAMIC and REOPT ALWAYS are mutually exclusive. However, you can specify WITH KEEP DYNAMIC and REOPT ONCE.
Use WITH KEEP DYNAMIC to improve performance if your DRDA client application uses a cursor that is defined as WITH HOLD. The Db2 subsystem automatically closes a held cursor when there are no more rows to retrieve, which eliminates an extra network message.
- OPTHINT string-constant
- Specifies whether query optimization hints are used for static SQL statements that are contained within the body of the procedure.
string-constantis a character string of up to 128 bytes in length, which is used by the Db2 subsystem when searching the PLAN_TABLE for rows to use as input. The default value is an empty string, which indicates that the Db2 subsystem does not use optimization hints for static SQL statements.
Optimization hints are only used if optimization hints are enabled for you system. For more information, see OPTIMIZATION HINTS field (OPTHINTS subsystem parameter)
- SQL PATH
- Specifies the SQL path that the Db2 subsystem uses to resolve unqualified
user-defined types, functions, and procedure names (in CALL statements) in the body of the
procedure. The default value is "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM",
and the value of the QUALIFIER option.
The maximum length of the SQL path is 2048 bytes. Db2 calculates the length by taking each schema-name specified and removing any trailing blanks from it, adding two delimiters around it, and adding one comma after each schema name except for the last one. The length of the resulting string cannot exceed 2048 bytes.
- schema-name
- Specifies a schema. Db2 does not
validate that the specified schema actually exists when the ALTER statement is processed.
SYSPUBLIC must not be specified for the SQL path.
- schema-name-list
- Specifies a comma separated list of schema names. The same schema name should not appear more
than one time in the list of schema names. The number of schema names that you can specify is
limited by the maximum length of the resulting SQL path.
SYSPUBLIC must not be specified for the SQL path.
- SYSTEM PATH
- Specifies the schema names "SYSIBM", "SYSFUN", "SYSPROC", "SYSIBMADM".
- SESSION_USER or USER
- Specifies the value of the SESSION_USER (or USER) special register. At the time the ALTER statement is processed, the actual length is included in the total length of the list of schema names that is specified for the PATH option. If you specify SESSION_USER (or USER) in a list of schema names, do not use delimiters around the SESSION_USER (or USER) keyword.
- RELEASE AT
- Specifies when to release
resources that the procedure uses: either at each commit point or
when the procedure terminates.
- COMMIT
- Specifies that resources will be released at each commit point.
COMMIT is the default.
- DEALLOCATE
- Specifies that resources will be released only when the procedure terminates. DEALLOCATE has no
effect on packages that run on a Db2 server
through a DRDA connection with a client system. DEALLOCATE
also has no effect on dynamic SQL statements, which always use RELEASE AT COMMIT, with this exception: When you use the RELEASE AT DEALLOCATE clause and the WITH KEEP DYNAMIC
clause, and the subsystem is installed with a value of YES for the field CACHE DYNAMIC SQL on
installation panel DSNTIP8, the RELEASE AT
DEALLOCATE option is honored for dynamic SELECT and SQL data change statements.
Locks that are acquired for dynamic statements are held unit one of the following events occurs:
- The application process ends.
- The application process issues a PREPARE statement with the same statement identifier. (Locks are released at the next commit point).
- The statement is removed from the dynamic statement cache because the statement has not been used. (Locks are released at the next commit point).
- An object that the statement is dependent on is dropped or altered, or a privilege that the statement needs is revoked. (Locks are released at the next commit point).
RELEASE AT DEALLOCATE can increase the package size because additional items become resident in the package.
For more information about how the RELEASE clause affects locking and concurrency, see Choosing a RELEASE option.
- QUERY ACCELERATION
- Specifies whether a static SQL query is bound for acceleration,
and if so, with what behavior.
- NONE
- Specifies that no static SQL query in the application is bound for acceleration or will be accelerated when the application is run.
- ENABLE
- Specifies that a static SQL query is bound for acceleration if
it satisfies the acceleration criteria, including the cost and heuristics
criteria. The query is routed to an accelerator when the application
runs. Otherwise, if the static query does not satisfy the acceleration
criteria, the query is bound for execution in Db2.
If an error condition, such as one of the following examples, occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application:
- A failure occurs while running the static query on the accelerator.
- The accelerator returns an error for the query.
- The accelerator is not started and Db2 cannot route the static query to the accelerator for execution.
- ENABLE WITH FAILBACK
- Results in the same behavior as ENABLE, except if one of the error conditions occurs on the first OPEN of the accelerated static query when the application is run. In this case, instead of failing the static query and returning a negative SQL code to the application, Db2 performs a temporary statement-level incremental bind of the query and runs the query in Db2. The application does not see the acceleration failure. Failback to Db2 is not possible after the application does a successful OPEN for the query on the accelerator.
- ELIGIBLE
- Specifies that a static SQL query is bound for acceleration if
the query meets the basic acceleration criteria, regardless of the
cost or heuristics criteria. The query is routed to the accelerator
when the application runs.
Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application.
- ALL
- Specifies that all of the static SQL queries in the application
are to be bound for acceleration and routed to the accelerator when
the application runs. If Db2 determines
that a static query cannot be bound to run on the accelerator and
the query references a user base table or view, the BIND or REBIND
PACKAGE operation fails with an error message for that query. (A failure
exception is made for declared global temporary tables (DGTTs) and
created global temporary tables and (CGTTs) because these tables cannot
be accelerated.)
Like the behavior for ENABLE, if an error condition occurs while executing the accelerated static query when the application is run, Db2 fails the static query and returns a negative SQL code to the application.
This bind option does not apply to a fullselect or WITH common-table-expression that is specified in a RETURN statement for the routine, or in a SET host-variable-assignment that is used in the routine. The queries that are specified in these cases cannot be accelerated.
- GET_ACCEL_ARCHIVE
- Specifies whether a static SQL query that is bound for acceleration
retrieves archived data on the accelerator, instead of active data.
- NO
- Specifies that no static SQL query is bound to retrieve archived
data from the accelerator. If the static query also is not bound for
acceleration, the query is bound to run in Db2.
If the static query is bound for acceleration because the QUERYACCELERATION bind option was specified, the query is routed to the accelerator when the application runs; however, the query does not retrieve any archived data.
- YES
- Specifies that if all of the following criteria are met, the query
is bound for acceleration and retrieves the archived data on the accelerator
when the application runs:
- The QUERYACCELERATION bind option is also specified.
- The static SQL query references an accelerated table that has partitioned data archived on an accelerator.
- The static query satisfies the acceleration criteria that is specified by the QUERYACCELERATION bind option.
If the static query does not satisfy the acceleration criteria that is specified by the QUERYACCELERATION bind option, the BIND or REBIND PACKAGE operation fails with an error message for that query.
This bind option does not apply to a fullselect or WITH common-table-expression that is specified in a RETURN statement for the routine, or in a SET host-variable-assignment that is used in the routine. The queries that are specified in these cases cannot be accelerated.
- ACCELERATION WAITFORDATA
- Specifies the maximum amount of time, if any, that an accelerator will delay a query while the
accelerator waits for the replication of committed Db2 data changes that occurred prior to Db2 running the query.
For static accelerated queries, you must also set the QUERYACCELERATION bind option for this function or procedure to a valid value other than NONE to request that static queries be accelerated. If the QUERYACCELERATION bind option value is set to NONE, the ACCELERATIONWAITFORDATA bind option is accepted and the package is bound with the option value; however, the option will not apply to static SQL queries because no static queries will be accelerated.
For dynamic accelerated queries, specifying the ACCELERATION WAITFORDATA bind option also initializes the CURRENT QUERY ACCELERATION WAITFORDATA special register, which is used for the dynamic queries in the Db2 function or procedure if the function or procedure option DEFAULT SPECIAL REGISTERS is also used. Initializing CURRENT QUERY ACCELERATION WAITFORDATA to a value greater than 0 specifies that Db2 and the accelerator will apply WAITFORDATA delay behavior and restrictions to all dynamic SQL queries to be accelerated from the Db2 function or procedure. The CURRENT QUERY ACCELERATION special register must also have a valid value other than NONE to request that dynamic queries be accelerated.
- nnnn.m
- Specifies a DECIMAL(5,1) numeric-constant value that specifies the maximum number of seconds
that the accelerator will delay a query while the accelerator waits for the replication of committed
Db2 data changes that occurred prior to
Db2 running the query.
You can specify a value in the range of 0.0–3600.0 seconds. For example, a value of 20.0 represents 20.0 seconds (or 20000 milliseconds), and a value of 30.5 represents 30.5 seconds (or 30500 milliseconds). The maximum value of 3600.0 means they the query is delayed for 3600 seconds.
You can also specify the value as an INTEGER numeric-constant value ranging 0–3600 seconds, which Db2 will convert to a DECIMAL(5,1) value.
Important: When a non-zero value is specified for the ACCELERATIONWAITFORDATA bind option, Db2 and the accelerator will apply other WAITFORDATA delay behaviors, restrictions, and requirements to all queries that will be accelerated from the application package. These behaviors, restrictions, and requirements can cause queries that were formerly accelerated successfully to no longer be accelerated or to fail. See SET CURRENT QUERY ACCELERATION WAITFORDATA statement for more information about WAITFORDATA behaviors, restrictions, and requirements. - ACCELERATOR
- Specifies an accelerator server that, if enabled and available, Db2 will consider as the preferred accelerator for eligible SQL queries before sending the queries to other accelerator servers. If the specified accelerator server is not enabled or available, Db2 will send the queries to other available accelerator servers.
- REOPT
- Specifies if Db2 will determine the access path at run time by
using the values of SQL variables or SQL parameters, parameter markers, and special registers.
- NONE
- Specifies that Db2 does not determine
the access path at run time by using the values of SQL variables or SQL parameters, parameter
markers, and special registers.
NONE is the default.
- ALWAYS
- Specifies that Db2 always determine the access path at run time each time an SQL statement is run.
- ONCE
- Specifies that Db2 determine the access path for any dynamic SQL statements only one time, at the first time the statement is opened. This access path is used until the prepared statement is invalidated or removed from the dynamic statement cache and needs to be prepared again.
- VALIDATE RUN or VALIDATE BIND
- Specifies whether to recheck, at
run time, errors of the type "OBJECT not FOUND" and NOT AUTHORIZED"
that are found during bind or rebind. The option has no effect if
all objects and needed privileges exist.
- VALIDATE RUN
- Specifies that if needed objects or privileges do not exist when the ALTER PROCEDURE statement
is processed, warning messages are returned, but the ALTER PROCEDURE statement succeeds. The
Db2 subsystem rechecks for the objects and
privileges at run time for those SQL statements that failed the checks during processing of the
ALTER PROCEDURE statement. The authorization checks the use of the authorization ID of the owner of
the procedure package.
VALIDATE RUN is the default.
- VALIDATE BIND
- Specifies that if needed objects or privileges do not exist at the time the ALTER PROCEDURE statement is processed, an error is issued and the ALTER PROCEDURE statement fails.
- ROUNDING
- Specifies the rounding mode for manipulation
of DECFLOAT data.
- DEC_ROUND_CEILING
- Specifies numbers are rounded towards positive infinity.
- DEC_ROUND_DOWN
- Specifies numbers are rounded towards 0 (truncation).
- DEC_ROUND_FLOOR
- Specifies numbers are rounded towards negative infinity.
- DEC_ROUND_HALF_DOWN
- Specifies numbers are rounded to nearest; if equidistant, round down.
- DEC_ROUND_HALF_EVEN
- Specifies numbers are rounded to nearest; if equidistant, round so that the final digit is even.
- DEC_ROUND_HALF_UP
- Specifies numbers are rounded to nearest; if equidistant, round up.
- DEC_ROUND_UP
- Specifies numbers are rounded away from 0.
- DATE FORMAT ISO, EUR, USA, JIS, or LOCAL
- Specifies the date format
for result values that are string representations of date or time
values. See String representations of datetime values for
more information.
The default format is specified in the DATE FORMAT field of installation panel DSNTIP4 of the system where the procedure is defined. You cannot use the LOCAL option unless you have a date exit routine.
- DECIMAL(15), DECIMAL(31), DECIMAL(15,s), or DECIMAL(31,s)
- Specifies the maximum precision that is to be used for decimal arithmetic operations. For more information, see Arithmetic with two decimal operands. The default format is specified in the DECIMAL ARITHMETIC field of installation panel DSNTIPF of the system where the procedure is defined. If the form pp.s is specified, s must be a number in the range 1–9. s represents the minimum scale that is to be used for division.
- FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED
- Specifies
whether the FOR UPDATE clause is required for a DECLARE CURSOR statement
if the cursor is to be used to perform positioned updates.
- FOR UPDATE CLAUSE REQUIRED
- Specifies that a FOR UPDATE clause must be specified as part of
the cursor definition if the cursor will be used to make positioned
updates.
FOR UPDATE CLAUSE REQUIRED is the default.
- FOR UPDATE CLAUSE OPTIONAL
- Specifies that the FOR UPDATE clause does not need to be specified
in order for a cursor to be used for positioned updates. The procedure
body can include positioned UPDATE statements that update columns
that the user is authorized to update.
If the resulting DBRM for the procedure is very large, you might need extra storage when you specify FOR UPDATE CLAUSE OPTIONAL.
- TIME FORMAT ISO, EUR, USA, JIS, or LOCAL
- Specifies the time format
for result values that are string representations of date or time
values. See String representations of datetime values for
more information.
The default format is specified in the TIME FORMAT field of installation panel DSNTIP4 of the system where the procedure is defined. You cannot use the LOCAL option unless you have a date exit routine.
- BUSINESS_TIME SENSITIVE
- Determines 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.
- YES
- References to application-period temporal tables are affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register. YES is the default value.
- NO
- References to application-period temporal tables are not affected by the value of the CURRENT TEMPORAL BUSINESS_TIME special register.
For more information, see CURRENT TEMPORAL BUSINESS_TIME special register.
- SYSTEM_TIME SENSITIVE
- Determines 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.
- YES
- References to system-period temporal tables are affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register. YES is the default value.
- NO
- References to system-period temporal tables are not affected by the value of the CURRENT TEMPORAL SYSTEM_TIME special register.
For more information, see CURRENT TEMPORAL SYSTEM_TIME special register.
- ARCHIVE SENSITIVE
- Determines whether references to archive-enabled tables in SQL statements are affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable.
- YES
- References to archive-enabled tables are affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable. YES is the default value.
- NO
- References to archive-enabled tables are not affected by the value of the SYSIBMADM.GET_ARCHIVE built-in global variable.
For more information, see GET_ARCHIVE.
- APPLCOMPAT applcompat-level
- Specifies the application compatibility behavior for SQL statements in the procedure body. If this option is not specified, the behavior is determined by the applcompat-level of the previous bind of the package if one exists, or otherwise by the APPLCOMPAT subsystem parameter.The following applcompat-level values can be specified:
VvvRrMmmm
-
Compatibility with the behavior of the identified Db2 function level. For example,
V12R1M510
specifies compatibility with the highest available Db2 12 function level. The equivalent function level or higher must be activated.For the new capabilities that become available in each application compatibility level, see:Tip: Extra program preparation steps might be required to increase the application compatibility level for applications that use data server clients or drivers to access Db2 for z/OS. For more information, see Setting application compatibility levels for data server clients and drivers. - V12R1
- Compatibility with the behavior of Db2 12 function level 500. This value has the same result as specifying
V12R1M500
. - V11R1
- Compatibility with the behavior of Db2 11 new-function mode. After migration to Db2 12, this value has the same result as specifying
V12R1M100
. For more information, see V11R1 application compatibility level - V10R1
- Compatibility with the behavior of DB2 10 new-function mode. For more information, see V10R1 application compatibility level.
- CONCENTRATE STATEMENTS OFF or CONCENTRATE STATEMENTS WITH LITERALS
- Specifies whether each dynamic SQL statement in the routine that specifies literal constants
will be cached as a separate unique statement entry in the dynamic statement cache, instead of
sharing an existing statement in the cache. Dynamic SQL statements are eligible to share an existing
statement in the cache if the new statement meets all of the conditions for sharing a cached version
of the same dynamic statement, except that the new statement specifies one or more literal constants
that are different than the cached statement.
- CONCENTRATE STATEMENTS OFF
-
Specifies that each dynamic SQL statement that specifies literal constants will be cached as a unique statement entry if it specifies one or more constants that are different than the cached version of the same dynamic statement. CONCENTRATE STATEMENTS OFF is the default dynamic statement caching behavior.
- CONCENTRATE STATEMENTS WITH LITERALS
-
Specifies that each dynamic SQL statement that specifies literal constants will share a cached version of the same dynamic statement that is also prepared using the CONCENTRATE STATEMENTS WITH LITERALS option, if the new dynamic statement meets all of the conditions for sharing the cached statement, and the constants that are specified can be reused in place of the constants in the cached statement.
- SQL-routine-body
- Specifies the statements that define the body of the SQL procedure. For information on the SQL control statements that are supported in native SQL procedures, see SQL procedural language (SQL PL). If an SQL-procedure-statement is the only statement in the procedure body, the statement must not end with a semicolon.
Notes for ALTER PROCEDURE (SQL - native)
- Considerations for altering a version of a procedure
- To alter a version of a procedure, the environment settings that are in effect when the ALTER PROCEDURE statement is issued must be the same as the environment settings that are in effect when the version of the procedure is first created using the CREATE PROCEDURE or ALTER PROCEDURE statements if one of the following options is specified:
- QUALIFIER
- PACKAGE OWNER
- OPTHINT
- SQL PATH
- DECIMAL (if the value includes a comma
For the WLM FOR DEBUG MODE clause, the name specified must be representable in the CCSID of the original statement that defined the object or version.
- Changing to a native SQL procedure
- You cannot change an external SQL procedure to a native SQL procedure. You can drop the external SQL procedure that you want to change by using the DROP statement and create a native SQL procedure with a similar definition using the CREATE PROCEDURE statement. Alternatively, you can create a native SQL procedure using a different schema.
- Identifier resolution
- See SQL procedural language (SQL PL) for information on how names
are resolved to columns, SQL variables, or SQL routines for native SQL procedures. Name resolution
is unchanged for external SQL procedures.
If duplicate names are used for columns and SQL variables and parameters, qualify the duplicate names by using the table designator for columns, the procedure name for parameters, and the label name for SQL variables.
- Characteristics of the package that is generated for a version of a procedure
- The package that is associated with a version of a procedure is named as follows:
- location is set to the value of the CURRENT SERVER special register
- collection-id (schema) for the package is the same as the schema qualifier of the procedure
- package-id is the same as the specific name of the procedure
- version-id is the same as the version identifier for the initial version of the procedure
The package is generated using the bind options that correspond to the implicitly or explicitly specified procedure options. In addition to the corresponding bind options, the package is generated using the following bind options:
- DBPROTOCAL(DRDA)
- FLAG(1)
- SQLERROR(NOPACKAGE)
- ENABLE(*)
- Application compatibility level considerations for procedure objects
- The application compatibility level controls the adoption and use of new capabilities and enhancements. When an object is created or altered, two separate application compatibility levels are used: one to process the definition of the object, and the other for processing the SQL statements in the object body:
Object definition The CURRENT APPLICATION COMPATIBILITY special register value is used to process the object definition, except for statements in the object body
This application compatibility level is stored in the SYSENVIRONMENT.APPLCOMPAT column. You can use the environment ID value in the catalog definition of the object to locate the SYSENVIRONMENT row with the matching ENVID value.
This application compatibility level can be changed when the object is regenerated.
Statements in the object body The application compatibility level that is implicitly or explicitly specified with the APPLCOMPAT option of the CREATE or ALTER statement is used to process statements in the object body.
This application compatibility level is stored in the SYSPACKAGE.APPLCOMPAT column for the package associated with the object definition.
- Altering a procedure definition using a lower application compatibility level than the current definition
- If the CURRENT APPLICATION COMPATIBILITY special register value is lower than the application compatibility level of the existing object definition, altering an object might result in an error even if the content of the ALTER statement is valid at the current level. The errors can occur when the existing object definition contains some functionality that requires a higher level than the CURRENT APPLICATION COMPATIBILITY special register setting.
- Application compatibility levels for regenerating procedure objects
-
For ALTER statements that regenerate objects, the object definition is reprocessed using the application compatibility setting that is specified implicitly or explicitly by the USING APPLICATION COMPATIBILITY clause that follows the REGENERATE keyword. This application compatibility value replaces the existing value in the SYSENVIRONMENT.APPLCOMPAT column for the environment settings associated with the object definition.
If the USING APPLICATION COMPATIBILITY clause is not specified, the existing application compatibility value in the SYSENVIRONMENT.APPLCOMPAT column for the object definition is used to reprocess the text associated with the object definition.
The behavior of the statements in the body remains controlled by the value the existing APPLCOMPAT option of the object.
- Considerations for a procedure that is defined using a TABLE LIKE name AS LOCATOR clause
- If a procedure is defined with a table parameter (the TABLE LIKE name AS LOCATOR clause was specified in the CREATE PROCEDURE statement to indicate that one of the input parameters is a transition table), the procedure cannot be changed with an ALTER PROCERDURE statement if the change requires that the parameter list be specified. For example, to add or replace a version of a native SQL procedure, the procedure must be dropped and re-created.
- Considerations for SQL processor programs
- SQL processor programs, such as SPUFI, the command line processor, and DSNTEP2, might not correctly parse SQL statements in the routine body that end with semicolons. These processor programs accept multiple SQL statements as input, with each statement separated with a terminator character. Processor programs that use a semicolon as the SQL statement terminator can truncate a CREATE FUNCTION statement with embedded semicolons and pass only a portion of it to Db2. Therefore, you might need to change the SQL terminator character for these processor programs. For information on changing the terminator character for SPUFI and DSNTEP2, see Setting the SQL terminator character in a SPUFI input data set and DSNTEP2 and DSNTEP4 sample programs.
- Correspondence of procedure options to BIND options
The following table lists the corresponding bind command options for specific CREATE PROCEDURE and ALTER PROCEDURE options. For more information about the bind options, see BIND and REBIND options for packages, plans, and services .
Correspondence of procedure options to bind optionsCREATE PROCEDURE or ALTER PROCEDURE option Bind commands option ACCELERATION WAITFORDATA nnnn.m ACCELERATIONWAITFORDATA(nnnn.m) ACCELERATOR accelerator-name ACCELERATOR(accelerator-name) APPLICATION ENCODING SCHEME ASCII ENCODING(ASCII) APPLICATION ENCODING SCHEME EBCDIC ENCODING(EBCDIC) APPLICATION ENCODING SCHEME UNICODE ENCODING(UNICODE) ARCHIVE SENSITIVE NO ARCHIVESENSITIVE(NO) ARCHIVE SENSITIVE YES ARCHIVESENSITIVE(YES) BUSINESS_TIME SENSITIVE NO BUSTIMESENSITIVE(NO) BUSINESS_TIME SENSITIVE YES BUSTIMESENSITIVE(YES) CURRENT DATA NO CURRENTDATA(NO) CURRENT DATA YES CURRENTDATA(YES) DEFER PREPARE DEFER(PREPARE) NODEFER PREPARE NODEFER(PREPARE) DEGREE 1 DEGREE(1) DEGREE ANY DEGREE(ANY) DYNAMICRULES RUN DYNAMICRULES(RUN) DYNAMICRULES BIND DYNAMICRULES(BIND) DYNAMICRULES DEFINEBIND DYNAMICRULES(DEFINEBIND) DYNAMICRULES DEFINERUN DYNAMICRULES(DEFINERUN) DYNAMICRULES INVOKEBIND DYNAMICRULES(INVOKEBIND) DYNAMICRULES INVOKERUN DYNAMICRULES(INVOKERUN) GET_ACCEL_ARCHIVE NO GETACCELARCHIVE(NO) GET_ACCEL_ARCHIVE YES GETACCELARCHIVE(YES) ISOLATION LEVEL CS ISOLATION(CS) ISOLATION LEVEL RS ISOLATION(RS) ISOLATION LEVEL RR ISOLATION(RR) ISOLATION LEVEL UR ISOLATION(UR) OPTHINT string-constant OPTHINT(hint-id) PACKAGE OWNER authorization-name OWNER(authorization-id) QUALIFIER schema-name QUALIFIER(qualifier-name) QUERY ACCELERATION NONE QUERYACCELERATION(NONE) QUERY ACCELERATION ENABLE QUERYACCELERATION(ENABLE) QUERY ACCELERATION ENABLE WITH FAILBACK QUERYACCELERATION(ENABLE WITH FAILBACK) QUERY ACCELERATION ELIGIBLE QUERYACCELERATION(ELIGIBLE) QUERY ACCELERATION ALL QUERYACCELERATION(ALL) RELEASE AT COMMIT RELEASE(COMMIT) RELEASE AT DEALLOCATE RELEASE(DEALLOCATE) REOPT ALWAYS REOPT(ALWAYS) REOPT NONE REOPT(NONE) REOPT ONCE REOPT(ONCE) ROUNDING DEC_ROUND_CEILING ROUNDING(CEILING) ROUNDING DEC_ROUND_DOWN ROUNDING(DOWN) ROUNDING DEC_ROUNDING_FLOOR ROUNDING(FLOOR) ROUNDING DEC_ROUNDING_HALF_DOWN ROUNDING(HALFDOWN) ROUNDING DEC_ROUNDING_HALF_EVEN ROUNDING(HALFEVEN) ROUNDING DEC_ROUNDING_HALF_UP ROUNDING(HALFUP) ROUNDING DEC_ROUNDING_UP ROUNDING(UP) SQL PATH path-specification PATH(path-specification) SYSTEM_TIME SENSITIVE NO SYSTIMESENSITIVE(NO) SYSTEM_TIME SENSITIVE YES SYSTIMESENSITIVE(YES) VALIDATE BIND VALIDATE(BIND) VALIDATE RUN VALIDATE(RUN) WITH EXPLAIN EXPLAIN(YES) WITHOUT EXPLAIN EXPLAIN(NO) WITH IMMEDIATE WRITE IMMEDWRITE(YES) WITHOUT IMMEDIATE WRITE IMMEDWRITE(NO) WITH KEEPDYNAMIC KEEPDYNAMIC(YES) WITHOUT KEEPDYNAMIC KEEPDYNAMIC(NO) - Invalidation of packages
- This statement might invalidate all packages that depend on target objects, and sometimes other related objects through cascading effects, depending on the clauses and keywords specified and other factors.
ALTER PROCEDURE with the ACTIVATE VERSION option invalidates all packages that are dependent on a previous version of the procedure. (The ACTIVATE VERSION does not invalidate packages if the value of routine-version-id is the same as the current active version of the procedure.)
When a version of an SQL procedure is altered to change certain options that are specified for the active version, all application packages that refer to that procedure are marked invalid, and those invoking packages require a rebind. This includes changes to certain bind options and routine options that result in the implicit regeneration of the procedure. These options are marked accordingly in the following table in columns "Change requires rebind of invoking applications" and "Change results in implicit regeneration of the entire body of the procedure." For more information, see Changes that invalidate packages.
- Implicit rebind and regeneration
- When certain attributes of a native SQL procedure are changed, the body of the procedure might be rebound or regenerated. The columns "Change results in implicit regeneration of the entire body of the procedure" and "Change results in implicit rebind of non-control statements in the body of the procedure" in the following table summarize when implicit rebind and regeneration occurs when specific options are changed.
Table 1. CREATE PROCEDURE and ALTER PROCEDURE options that result in rebind or regeneration when changed. CREATE PROCEDURE or ALTER PROCEDURE option Change requires rebind of invoking applications? Change results in implicit rebind of the non-control statements of the body of the procedure? Change results in implicit regeneration of the entire body of the procedure? ACCELERATION WAITFORDATA No Yes No ACCELERATOR No Yes No ALLOW DEBUG MODE, DISALLOW DEBUG MODE, or DISABLE DEBUG MODE Yes1, 2 Yes1 Yes APPLICATION ENCODING SCHEME Yes Yes Yes ARCHIVE SENSITIVE Yes Yes Yes ASUTIME Yes No No BUSINESS_TIME SENSITIVE Yes Yes Yes COMMIT ON RETURN Yes No No CURRENT DATA No Yes No DATE FORMAT Yes Yes Yes DECIMAL Yes Yes Yes DEFER PREPARE or NODEFER PREPARE No Yes No DEGREE No Yes No DYNAMIC RESULT SETS Yes No No DYNAMICRULES No Yes No FOR UPDATE CLAUSE OPTIONAL or FOR UPDATE CLAUSE REQUIRED Yes Yes Yes GET_ACCEL_ARCHIVE Yes Yes Yes INHERIT SPECIAL REGISTERS or DEFAULT SPECIAL REGISTERS Yes No No ISOLATION LEVEL No Yes No MODIFIES SQL DATA, READS SQL DATA, or CONTAINS SQL Yes Yes Yes NOT DETERMINISTIC or DETERMINISTIC No No No OPTHINT No Yes No PACKAGE OWNER No Yes No QUALIFIER No Yes No QUERYACCELERATION Yes Yes Yes RELEASE AT COMMIT or RELEASE AT DEALLOCATE No Yes No REOPT No Yes No SQL PATH No Yes No STOP AFTER SYSTEM DEFAULT FAILURES, STOP AFTER nn FAILURES, or CONTINUE AFTER FAILURES Yes No No SYSTEM_TIME SENSITIVE Yes Yes Yes TIME FORMAT Yes Yes Yes VALIDATE RUN or VALIDATE BIND No Yes No WITH EXPLAIN or WITHOUT EXPLAIN No Yes No WITH IMMEDIATE WRITE or WITHOUT IMMEDIATE WRITE No Yes No WITH KEEP DYNAMIC or WITHOUT KEEP DYNAMIC No Yes No WLM ENVIRONMENT FOR DEBUG MODE Yes No No Note:- The procedure package is rebound or regenerated if a value of ALLOW DEBUG MODE is changed to DISALLOW DEBUG MODE.
- Invoking applications are invalidated if a value of DISALLOW DEBUG MODE is changed to DISABLE DEBUG MODE.
- Considerations for SYSENVIRONMENTS catalog table
- An ALTER statement that specifies a new environment settings will result in a new row being added to the SYSENVIRONMENTS catalog table. The new row will be added even if an error is subsequently encountered during processing of the ALTER statement. Thus, a new SYSENVIRONMENTS row might be added even for an ALTER statement that fails.
- Compatibilities:
- For compatibility with previous versions of Db2, when any of the following options are
implicitly or explicitly specified as part of option-list, a warning is issued,
but the statement is executed. However, if REPLACE or ADD VERSION is specified with one of the
following options, an error is issued, and the statement does not execute. For example, if ADD
VERSION and STAY RESIDENT are specified, an error is issued.
- STAY RESIDENT
- PROGRAM TYPE
- RUN OPTIONS
- NO DBINFO
- COLLID or NOCOLLID
- SECURITY
- PARAMETER STYLE GENERAL WITH NULLS
- STOP AFTER SYSTEM DEFAULT FAILURES
- STOP AFTER nn FAILURES
- CONTINUE AFTER FAILURES
If WLM ENVIRONMENT is specified for a native SQL procedure, WLM ENVIRONMENT FOR DEBUG MODE must be specified.
For compatibility with the CREATE PROCEDURE statement, the following clause can be specified, but will be ignored:
- LANGUAGE SQL
- Altering obfuscated procedures
- Obfuscated procedures cannot be altered in any way.
- Alternative syntax and synonyms:
- To provide compatibility with previous releases of Db2 or other products in the Db2 family, Db2 supports the following keywords:
- RESULT SET, RESULT SETS, and DYNAMIC RESULT SET as synonyms for DYNAMIC RESULT SETS.
- VARIANT as a synonym for NOT DETERMINISTIC
- NOT VARIANT as a synonym for DETERMINISTIC
- Considerations for catalog comments for a routine definition
- When a function definition is replaced any existing comment in the catalog for the definition is removed, and when a function definition is regenerated any existing comment in the catalog for the definition is retained.
Example for ALTER PROCEDURE (SQL - native)
- Example 1
- The following statement changes the existing procedure options for the active version of the
UPDATE_SALARY_1 native SQL procedure. If you need to change a different version of the procedure,
you would specify
VERSION routine-version-id
in place of ACTIVE VERSION. Note that the ALTER clause that precedes the version specification can be omitted.ALTER PROCEDURE UPDATE_SALARY_1 ALTER ACTIVE VERSION NOT DETERMINISTIC CALLED ON NULL INPUT ALLOW DEBUG MODE ASUTIME LIMIT 10
- Example 2
- To change the procedure body of any existing version of a procedure, you need to use the REPLACE
clause. The following statement changes both the procedure body and the existing SQL data access
option for version V2 of the UPDATE_SALARY_1 SQL procedure. Note that the list of parameters is
specified even though no changes are made to the list. To replace an existing version of a
procedure, you must specify the list of parameters, any options that are to have non-default values
(even if those options are specified in the version of the procedure that you are replacing), and
the body of the procedure.
ALTER PROCEDURE UPDATE_SALARY_1 REPLACE VERSION V2 (P1 INTEGER, P2 CHAR(5)) MODIFIES SQL DATA UPDATE EMP SET SALARY = SALARY * RATE WHERE EMPNO = EMPLOYEE_NUMBER;
- Example 3
- To add a new version of an existing procedure, use the ADD VERSION clause. The following
statement adds a new version of the UPDATE_SALARY_1 procedure to apply a larger salary increase.
Note that the list of parameters is specified even though the new version of the procedure uses the
same parameters as the existing version of the procedure. To add a new version of a procedure, you
must specify the list of parameters, any options that will have non-default values, and the body of
the procedure.
ALTER PROCEDURE UPDATE_SALARY_1 ADD VERSION V3 (P1 INTEGER, P2 CHAR(5)) UPDATE EMP SET SALARY = SALARY * (RATE*10) WHERE EMPNO = EMPLOYEE_NUMBER;
- Example 4
- When the new version of the procedure has been defined, as in Example 3, you must use the ALTER
PROCEDURE statement with the ACTIVATE VERSION clause if the new version of the procedure is to be
the currently active version, as in the following example.
ALTER PROCEDURE UPDATE_SALARY_1 ACTIVATE VERSION V3;
- Example 5
- To regenerate the currently active version of a procedure, use the following statement.
ALTER PROCEDURE UPDATE_SALARY_1 REGENERATE ACTIVE VERSION;