Capturing reoptimized access paths

Special measures are required for capturing EXPLAIN information that accurately reflects the reoptimized access path for a SQL statement when certain REOPT options are specified.

About this task

EXPLAIN information for SQL statements that are reoptimized at run time do not always represent the access path that DB2 uses at run time. For example, in each of the following situations, the captured EXPLAIN information reflects the access path that DB2 chooses when literal values are unknown:

For static SQL statements that use the REOPT(ALWAYS) option when EXPLAIN(YES) is specified at bind time
For dynamic SQL statements that contain parameter markers and special registers and use the REOPT(ONCE) or REOPT(AUTO) options, when an EXPLAIN PLAN statement is issued.

Therefore, DB2 might choose a different access path at run time when the literal values are known.

Procedure

To capture EXPLAIN information that accurately reflects the access path for statements that are reoptimized at run time, use the following approaches:

Activate performance trace class 30 (IFCID 0022 and IFCID 0063), and set a scope to limit the trace to a specific AUTHID and PLANNAME. The scope makes the collected trace records are specific to the plan and user of interest. The performance trace collects the SQL statement text and bind records that are generated each time that DB2 prepares a statement. The result shows the actual access path that DB2 generates each time that the statement is issued. You can also activate IFCID 0247 to capture the literal values that are used each time that the statements are prepared.
For statements that are processed under the REOPT(ONCE) option, capture the EXPLAIN records from the statement in the dynamic statement cache:
1. Locate the statement ID and token. You might issue the following statement to find these values:
```
EXPLAIN STMTCACHE ALL;
```
  When you specify STMTCACHE ALL, DB2 only populates data the DSN_STATEMENT_CACHE_TABLE. No records are captured in other EXPLAIN tables.
2. Extract the EXPLAIN records from the statement cache and write them to the EXPLAIN tables. For example, you might issue one of the following statements:
```
EXPLAIN STMTCACHE STMTID statement-ID
```
```
EXPLAIN STMTCACHE STMTTOKEN statement-token
```
  When you issue these statements, information is captured in the EXPLAIN tables for the specified statement.
You can also use the method for statements that use the REOPT(AUTO) option. However, the EXPLAIN records that are captured in this manner apply only to the last access path that was used. You might not be able to determine whether the retrieved access path matches the access path that was used when a problem occurred.