DB2 10.5 for Linux, UNIX, and Windows

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

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

ALTERIN privilege on the schema
BIND privilege on the package
DBADM authority


>>-ALTER PACKAGE--package-name--+-------------------------+----->
                                | .-VERSION-.             |   
                                '-+---------+--version-id-'   

   .-------------------------------------------------------------.   
   V  (1)                                                        |   
>--------+-ACCESS PLAN REUSE--+-YES-+--------------------------+-+-><
         |                    '-NO--'                          |     
         +-OPTIMIZATION PROFILE--+-NONE----------------------+-+     
         |                       '-optimization-profile-name-' |     
         '-KEEP DYNAMIC--+-YES-+-------------------------------'     
                         '-NO -'

Notes:

The same clause must not be specified more than once.

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

If the statement is invoked from an operating system command prompt, precede each double quotation mark delimiter with a back slash character to ensure that the operating system does not strip the delimiters.

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 DB2® 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

Starting with DB2 for Linux, UNIX, and Windows Version 9.8 Fix Pack 2, 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 the WITH 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 of ACCESS PLAN REUSE.
- OPTPROFILE can be specified in place of OPTIMIZATION PROFILE.
- KEEPDYNAMIC can be specified in place of KEEP DYNAMIC.

Examples

Example 1: Enable access plan reuse for package TRUUVERT.EMPADMIN.

   ALTER PACKAGE TRUUVERT.EMPADMIN ACCESS PLAN REUSE YES

Example 2: Assume access plan reuse has been enabled for package TRUUVERT.EMPADMIN. Assume also that optimization profile AYYANG.INDEXHINTS contains a statement profile for a specific statement within the package. Associate the optimization profile with this package so that it will override the reuse of the access plan for the statement.

   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.

Example 3: The following statement will result in no optimization profile being associated with package TRUUVERT.EMPADMIN.

   ALTER PACKAGE TRUUVERT.EMPADMIN OPTIMIZATION PROFILE NONE