DB2 Version 9.7 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:

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

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). End of change

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.

The ALTER PACKAGE statement does not cause a rebind to take place, therefore query execution plans for static statements will not be affected until the next implicit or explicit rebind. However, once the ALTER PACKAGE statement commits, the value for optimization profile will be used as the default value for the CURRENT OPTIMIZATION PROFILE special register for compiling dynamic DML statements issued using the package. The ALTER PACKAGE statement does not process the optimization profile, but only validates that the name is syntactically valid. The optimization profile will be processed the next time a DML statement issued using the package is optimized.

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

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 will result in no optimization profile being associated with package TRUUVERT.EMPADMIN.

   ALTER PACKAGE TRUUVERT.EMPADMIN OPTIMIZATION PROFILE NONE