APREUSE bind option

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.

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

The APREUSE option is ignored for package copies that were bound prior to DB2 9. You can identify the version on which a package or package copy was bound by checking the RELBOUND column in the SYSIBM.SYSPACKAGE or SYSIBM.SYSPACKCOPY catalog table.