Analyzing access path changes at bind or rebind

When you rebind packages, you can use access path comparison to learn which access paths have changed, and to verify whether access path reuse was successful.

Before you begin

Db2 ignores the APREUSE bind option for any package or package copy that was bound before DB2® 9. You must bind or rebind the package in Db2 12 and accept the access path changes, before you can begin reusing access paths. The RELBOUND column of the SYSIBM.SYSPACKAGE or SYSIBM.SYSPACKCOPY catalog tables indicates the Db2 release in which the package or package copy was last bound or rebound.

About this task

With access path comparison Db2 verifies that a new access path matches the previous access path. The comparison examines a new access path and compares it to the existing access path. When Db2 detects mismatches between new and previous access paths, it writes information about the mismatches to the PLAN_TABLE.REMARKS column.

When the comparison is complete, Db2 issues a DSNT285I message that indicates how many access paths are changed and unchanged after the rebind operation.

You can specify either error or warning rules for comparison. Under warning rules, the bind operations complete regardless of whether reuse or comparison fails for statements in a package. Under error rules, the bind operations ends for a package after all statements are processed if any reuse or comparison error is encountered.

Db2 also uses access path comparison to validate the resulting access paths when you specify access path reuse.

Procedure

To assess the result of access path changes after a bind or rebind:

Specify one of the following bind options to activate access path comparison:
- Specify APREUSE(WARN) to try to enforce the reuse of access paths, accept access path changes when reuse cannot be applied, and find out when changes occur. This option might result in unacceptable access path changes. Therefore, the recommendation is to also use a plan management policy that enables you to switch to previous access paths.
- APCOMPARE(WARN) to get new access paths and find out when changes occur. This option might result in unacceptable access path changes. Therefore, the recommendation is to also use a plan management policy that enables you to switch to previous access paths.
- APCOMPARE(ERROR) to accept no access path changes, without trying to reuse.
- APREUSE(ERROR) to try to enforce the reuse of access paths, accepting no reuse failures or access path changes
Specify the EXPLAIN(ONLY) or EXPLAIN(YES) bind options, so that Db2 creates EXPLAIN output for the bind or rebind operation.
- If you want to capture the EXPLAIN output without completing the bind operation, specify EXPLAIN(ONLY).This approach enables you to learn whether access path changes occur before you actually bind or rebind the packages. Db2 processes all statements in the packages, and generates the EXPLAIN output, but the bind or rebind operation is not completed, and new packages are not created. Because the bind or rebind operations do not complete, APCOMPARE(ERROR) and APCOMPARE(WARN) have the same meaning when EXPLAIN(ONLY) is specified.
- If you want to capture the EXPLAIN information and complete the bind or rebind operations, specify EXPLAIN(YES). Db2 processes the access paths and records the EXPLAIN information, the bind or rebind operations complete, and Db2 creates the new packages.
For either option, Db2 processes the access paths and inserts the access path comparison result information in the PLAN_TABLE.REMARKS column.
Issue the command to bind or rebind the packages.
The REBIND PACKAGE command is preferred when access path changes are accepted because it enables the use of plan management policies.
Examine the DSNT285I message to learn the numbers of access paths that are changed and unchanged by the bind or rebind operations.
```
DSNT285I  -csect-name REBIND FOR PACKAGE = package-name,
           USE OF APCOMPARE RESULTS IN:
           75 STATEMENTS WHERE COMPARISON IS SUCCESSFUL
           5 STATEMENTS WHERE COMPARISON IS NOT SUCCESSFUL
           2 STATEMENTS WHERE COMPARISON COULD NOT BE PERFORMED.
```
If you specified access path reuse, you can also examine the DSNT286I message which provides statistics about the success of reuse.
```
DSNT286I  -csect-name REBIND FOR PACKAGE = package-name,
           USE OF APREUSE RESULTS IN:
           73 STATEMENTS WHERE APREUSE IS SUCCESSFUL
           7 STATEMENTS WHERE APREUSE IS EITHER NOT SUCCESSFUL
             OR PARTIALLY SUCCESSFUL
           2 STATEMENTS WHERE APREUSE COULD NOT BE PERFORMED
           0 STATEMENTS WHERE APREUSE WAS SUPPRESSED BY OTHER HINTS.
```
Although Db2 also uses comparison when you specify access path reuse, the comparison and reuse are separate operations. Reuse fails when Db2 cannot apply a hint to enforce the previous access path. Comparison fails when the new access path does not match the previous access path. Notice, as shown in the preceding example, that even when reuse fails Db2 might sometimes select a new access path that matches the previous access path.
Query the PLAN_TABLE to find rows that Db2 populated as a result of the comparison or reuse.
When reuse succeeds, Db2 inserts the value 'APREUSE' into the PLAN_TABLE.HINT_USED column. When reuse or comparison fails, Db2 populates the PLAN_TABLE.REMARKS column with information about the failures.

When access path comparison fails, Db2 inserts information about mismatched PLAN_TABLE rows. When corresponding rows for the new and previous access path do not match, Db2 identifies a column that contains the values that do not match:
- For rows that exist in both the old and new access paths, the REMARKS column in rows for the new access path identifies the mismatched columns.
- For rows that exist only in the new access path, the REMARKS column in the new non-matching rows identifies the mismatched columns.
- For rows that exist only for the old access path, the REMARKS column of a different row for the new access path identifies the mismatch.
When access path reuse fails, Db2 inserts one of the following reason codes, which are also used by SQLCODE +395:

2

The value of the TABNO column is not valid. Negative values and values greater than the total number of table references are not valid.
The value of the TABNO column is validated only when the combination of the following columns does not identify a particular table reference within a query block: CREATOR, TNAME, CORRELATION_NAME, and QBLOCKNO.

3

A value is not specified for one of the following columns: CREATOR or TNAME

4

The value of the TABNO column was not specified, and the combination of the following columns does not uniquely identify a table reference: CREATOR, TNAME, CORRELATION_NAME, and QBLOCKNO.

5

The value specified for the TABNO column contains a value in the valid range. However, the value does not correctly correspond to the values of the following columns: CREATOR, TNAME, and CORRELATION_NAME.

6

The table identified by the values specified by the following columns cannot be found within the query block that is identified by the QBLOCKNO column: CREATOR, TNAME, CORRELATION_NAME.

7

The PAGE_RANGE column contains an invalid value.

8

The PREFETCH column contains an invalid value.

9

The METHOD column contains an invalid value.

10

The SORTN_JOIN column contains an invalid value.

11

The SORTC_JOIN column contains an invalid value.

12

The ACCESSTYPE column contains an invalid value.

13

One of the following columns contains an invalid value: ACCESSCREATOR or ACCESSNAME.

15

The hint specifies an index that cannot be used.

16

The hint specified multi-index access that cannot be used.

17

The hint specifies an invalid combination of ACCESSTYPE values.

18

The join method for the inner table of a join was not specified in the METHOD column.

19

The nested-loop join that is specified by the value of the METHOD column cannot be used.

20

The merge join that is specified by the value of the METHOD column cannot be used.

21

The hybrid join that is specified by the value of the METHOD column cannot be used.

22

The mode of parallelism that is specified by the value of the PARALLELISM_MODE column requested cannot be used.

23

The PARALLELISM_MODE column contains an invalid value.

24

One of the following columns contains an invalid value: ACCESS_DEGREE or ACCESS_PGROUP_ID

25

One of the following columns contains an invalid value: JOIN_DEGREE or JOIN_PGROUP_ID

26

A row for a table reference that is required for the hint is missing. For query blocks that reference multiple tables, the hint must contain a row for each table. Required tables might not be mentioned in the statement text because Db2 uses materialization or transformations in the access path.

27

The hint contains a redundant table reference because a table that is identified by a combination of the following columns appears in more than one row for the same query block: CREATOR, TNAME, CORRELATION_NAME, TABNO (when required).

28

The PRIMARY_ACCESSTYPE column contains an invalid value.

29

The SORTN_GROUP_ID column contains an invalid value.

30

The SORTN_GROUP_ID column contains an invalid value.

31

No value is specified in the PARALLELISM_MODE column.

32

One of the following columns contains an invalid value: CREATOR or TNAME

33

The join sequence is incorrect.

34

The column WHEN_OPTIMIZE contains an invalid or inconsistent value.

35

The value of the ACCESSTYPE column cannot contain 'V' for the specified table.

36

The number of dimension tables that are joined before the star join fact table exceeds the maximum number of dimensions that can be joined by a fact table index.

37

When the value of the JOIN_TYPE column for the fact table is 'SJ', a nested-loop join must be used.

38

The join sequence for dimension tables in a star join is incorrect because it does not match the sequence that is specified by the fact table index.

39

The value of ACCESSTYPE column is 'O' but the specified access path cannot be used.

40

Multiple index access cannot be used with the specified indexes .

41

Duplicate hint input records found for the same table.

42

Predicate validation failed for a selectivity override, but no matching predicate was found in the statement. You might be able to fix the problem by recapturing EXPLAIN information and reissuing the BIND QUERY command.

43

A selectivity override with multiple instances cannot be applied for the specified SQL statement. You might need to create a selectivity override that uses only a single instance.

48

Access path information for a correlated subquery is missing

50

The APREUSE option was specified during a bind or rebind operation and the hint was accepted. However, during access path comparison, Db2 detected that the resulting access path does not match the original access path.

99

An unexpected error occurred.

What to do next

You might take any of the following actions based on the results of the comparison:

When reuse fails, rebind packages that can tolerate changes with the APREUSE(WARN) bind option. You can use the message and PLAN_TABLE output to create a list of packages and statements that had access path changes. The list might be helpful for isolating problems when performance regressions occur.
When reuse fails for packages that cannot tolerate access path changes, do not rebind the packages. This option is the most cautious approach, but it might prevent your applications from taking advantage of performance improvements.