APREUSE bind option

The APREUSE option specifies whether Db2 tries to reuse previous access paths for SQL statements in a package. Db2 uses information about the previous access paths from the directory to create a hint.

Command option	Option values	Used with
APREUSE	( NONE ) ( NO ) ( ERROR ) ( WARN )	BIND PACKAGE BIND PLAN BIND SERVICE REBIND PACKAGE ¹ REBIND PLAN REBIND TRIGGER PACKAGE

Note:

The APREUSE bind option is not valid for REBIND of packages for advanced triggers.

The access path hint is not guaranteed to succeed in all cases. For example, a hint for an access path that relies on objects (such as indexes) that no longer exist cannot be reused. Version incompatibilities might also prevent access paths from being reused. Some access paths cannot be reused because of ambiguity in underlying hint. For example, a hint to use a merge join indicates the type of join to use and the number of matching columns, but the names of the matching columns are not available.

When APREUSE(ERROR) is specified for a BIND PACKAGE command, Db2 tries to locate the access path information from a package that has a matching identity based on the following criteria:

Location
Collection ID
Name
Version

If no such a package exists, Db2 tries to locate the most recently created version of a package that otherwise matches. When a prior version of a matching package is reused, Db2 issues a DSNT294I message. Even if a prior version exists, the set of static SQL statements in a previous version might not be identical to the set of statements in the new package. In such a situation, the APREUSE option only applies to statements that are identical in both versions.

Db2 issues a DSNT292I warning message when it cannot locate any previous package, and ignores the APREUSE option for that package.

Db2 reports the number of statements that were processed, the number of statements that reused the previous access path and the number of statements that could not reuse the previous access path in a DSNT286I message.

Start of change If package copies exist for a package, you can also specify the APREUSESOURCE option to specify the package copy to use for access path reuse. End of change

Option descriptions for APREUSE

( NONE ): Db2 does not try to reuse previous access paths for statements in the package.
( NO ): An accepted synonym for NONE.
( ERROR ): Db2 tries to reuse the previous access paths for SQL statements in the package. If statements in the package cannot reuse the previous access path, the bind operation for the package that contains the statement ends, and processing continues for the next package. Db2 indicates the number of statements that cannot be reused in any package in a message. New and changed statements in a package never prevent the completion of the bind or rebind operation, even though no previous access path is available for reuse.
( WARN ): Db2 tries to reuse the previous access paths for SQL statements in the package. Db2 ignores the previous access path and generates a new access path for any statement in the package that cannot reuse the previous access. The bind operation for the package completes, regardless of whether some access paths cannot be reused. Db2 indicates the number of statements that cannot be reused in any package in a message.

The APREUSE option is ignored for package copies that were bound prior to DB2® 9. The RELBOUND column in the SYSIBM.SYSPACKAGE or SYSIBM.SYSPACKCOPY catalog tables indicates the Db2 release on which a package or package copy was last bound or rebound.

Statements that reference temporal tables or archive tables: Db2 uses statement text as the matching criteria when it searches for older access paths in the prior package to reuse. However, for static statements that reference temporal tables or archive tables, Db2 uses additional matching criteria. The reason is that statements that reference temporal tables or archive tables might be bound once or twice, depending on the value of the bind options SYSTIMESENSITIVE and ARCHIVESENSITIVE. In the following situations, the statement is bound twice:

SYSTIMESENSITIVE is set to YES, and the statement references a system-period temporal table or bitemporal table.
ARCHIVESENSITIVE is set to YES, and the statement references an archive table.

When the statement is bound twice, implicit predicates are added to the statement during the second bind. This process is called implicit query transformation. The type of implicit query transformation is identified in the EXPANSION_REASON column of PLAN_TABLE. Therefore, in addition to matching statement text, Db2 also checks that the access path in the prior package and in the current package have the same implicit predicate.

Default values for APREUSE

Process	Default value
BIND SERVICE	NO
BIND PLAN	N/A
BIND PACKAGE	NO
REBIND PLAN	N/A
REBIND PACKAGE	NO
REBIND TRIGGER PACKAGE	NO
Automatic rebind	WARN