ALTER PACKAGE statement
The ALTER PACKAGE statement alters bind options for a package at the current server without having to bind or rebind the package.
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is in effect for the package (SQLSTATE 42509).
Authorization
- ALTERIN privilege on the schema
- BIND privilege on the package
- DBADM authority
Description
- package-name
- Identifies
the package that is to be altered. The package name must identify a package that exists at the
current server (SQLSTATE 42704).
- VERSION version-id
- Identifies which package version is to be altered. If a value is not specified, the version
defaults to the empty string. If multiple packages with the same package name but different versions
exist, only one package version can be altered in one invocation of the ALTER PACKAGE statement.
Delimit the version identifier with double quotation marks when it:
- Is generated by the VERSION(AUTO) precompiler option
- Begins with a digit
- Contains lowercase or mixed-case letters
- ACCESS PLAN REUSE
- Indicates whether the query compiler should attempt to reuse the access plans for static
statements in the package during future implicit and explicit rebinds.
- NO
- Specifies not to reuse access plans.
- YES
- Specifies to attempt to reuse access plans.
- OPTIMIZATION PROFILE
- Indicates what, if any, optimization profile to associate with the package.
- NONE
- Associates no optimization profile with the package. If an optimization profile is already associated with the package, the association is removed.
- optimization-profile-name
- Associates the optimization profile optimization-profile-name with the package. The optimization profile is a two-part name. If the specified optimization-profile-name is unqualified, the value of the CURRENT DEFAULT SCHEMA special register is used as the implicit qualifier. If an optimization profile is already associated with the package, the association is replaced with optimization-profile-name.
While the ALTER PACKAGE statement removes the current copy of the package from the database package cache, it does not invalidate the package and does not cause an implicit rebind to take place. This means that although dynamic SQL is affected by the changes made by the statement, query execution plans for static statements are not be affected until the next implicit or explicit rebind.
- KEEP DYNAMIC
-
You can modify the value of the
KEEPDYNAMIC
bind option for a package without requiring a fresh bind operation, thereby avoiding unnecessary recompilation until the next bind operation occurs. This option controls how long the statement text and section associated with a prepared statement are kept in the SQL context. It takes effect after all applications that are using the package have completed the transactions that were running when the ALTER PACKAGE statement was executed.- YES
-
Instructs the SQL context to keep the statement text and section associated with prepared statements indefinitely. Dynamic SQL statements are kept across transactions. All packages bound with
KEEPDYNAMIC YES
are by default compatible with the existing package cache behavior. - NO
-
Instructs the SQL context to remove the statement text and section associated with prepared statements at the end of each unit of work. The executable versions of prepared statements and the statement text in packages bound with the
KEEP DYNAMIC NO
option are removed from the SQL context at transaction boundaries. The client, driver, or application needs to prepare any dynamic SQL statement it wishes to reuse in a new unit of work again.For remote applications that use an IBM® non-embedded API, once you have ensured that statements will be prepared in new transactions, you can use this option so that WLB will not be disallowed solely based on the
KEEP DYNAMIC
behavior. However even with this option, WLB may be disallowed for other reasons.SELECT
statements issued by cursors with theWITH HOLD
option are disassociated from the SQL context at the next transaction boundary where the cursor is closed. As a result, workload balancing is allowed as long as there are no executable versions of prepared statements associated with the application in the SQL context.
Note: Workload balancing is not restricted for dynamic SQL applications that use IBM non-embedded APIs, such as JDBC, .NET, or CLI/ODBC, to run SQL within the common client packages. These interfaces implicitly re-prepare SQL statements before executing them in transactions where their connection might have been moved to a new executable version of prepared statements.
Notes
- Catalog view values may not reflect the settings that were in effect for the package: Because this statement does not trigger a rebind of the package, the settings for a package as shown in the SYSCAT.PACKAGES catalog view might not reflect what was actually in effect during the last BIND or REBIND. If the ALTER_TIME is greater than the LAST_BIND_TIME, then this might be the case.
- Syntax alternatives: The following syntax alternatives are supported for
compatibility with the BIND and REBIND commands. These alternatives are non-standard and should not
be used.
APREUSE
can be specified in place ofACCESS PLAN REUSE
.OPTPROFILE
can be specified in place ofOPTIMIZATION PROFILE
.KEEPDYNAMIC
can be specified in place ofKEEP DYNAMIC
.
Examples
TRUUVERT.EMPADMIN
. ALTER PACKAGE TRUUVERT.EMPADMIN ACCESS PLAN REUSE YES
ALTER PACKAGE TRUUVERT.EMPADMIN OPTIMIZATION PROFILE AYYANG.INDEXHINTS
Dynamic
statements will be affected after the statement commits; static statements will be affected at the
next rebind. When the package is rebound, the query compiler will attempt to reuse the access plans
for all static statements in the package, with the exception of the statement identified by the
optimization profile. When recompiling this statement, the query compiler will instead attempt to
apply the statement profile. ALTER PACKAGE TRUUVERT.EMPADMIN OPTIMIZATION PROFILE NONE