DB2 Version 10.1 for Linux, UNIX, and Windows

Explain register values

The tables in this topic describe the interaction of the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values, both with each other and with the PREP and BIND commands.

With dynamic SQL, the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values interact as follows.

Table 1. Interaction of Explain Special Register Values (Dynamic SQL)
EXPLAIN SNAPSHOT values	EXPLAIN MODE values
EXPLAIN SNAPSHOT values	NO	YES	EXPLAIN	REOPT	RECOMMEND INDEXES	EVALUATE INDEXES
NO	Results of query returned.	Explain tables populated. Results of query returned.	Explain tables populated. Results of query not returned (dynamic statements not executed).	Explain tables populated when a statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated. Results of query not returned (dynamic statements not executed). Indexes recommended.	Explain tables populated. Results of query not returned (dynamic statements not executed). Indexes evaluated.
YES	Explain Snapshot taken. Results of query returned.	Explain tables populated. Explain Snapshot taken. Results of query returned.	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed).	Explain tables populated when a statement qualifies for reoptimization at execution time. Explain Snapshot taken. Results of query returned.	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed). Indexes recommended.	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed). Indexes evaluated.
EXPLAIN	Explain Snapshot taken. Results of query not returned (dynamic statements not executed).	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed).	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed).	Explain tables populated when a statement qualifies for reoptimization at execution time. Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query not returned (dynamic or incremental-bind statements not executed).	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed). Indexes recommended.	Explain tables populated. Explain Snapshot taken. Results of query not returned (dynamic statements not executed). Indexes evaluated.
REOPT	Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated. Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated. Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query not returned (dynamic or incremental-bind statements not executed).	Explain tables populated when a statement qualifies for reoptimization at execution time. Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated. Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query not returned (dynamic or incremental-bind statements not executed). Indexes recommended.	Explain tables populated. Explain Snapshot taken when a statement qualifies for reoptimization at execution time. Results of query not returned (dynamic or incremental-bind statements not executed). Indexes evaluated.

The CURRENT EXPLAIN MODE special register interacts with the EXPLAIN bind option in the following way for dynamic SQL.

Table 2. Interaction of EXPLAIN Bind Option and CURRENT EXPLAIN MODE
EXPLAIN MODE values	EXPLAIN Bind option values
EXPLAIN MODE values	NO	YES	REOPT	ALL
NO	Results of query returned.	Explain tables populated for static SQL. Results of query returned.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query returned.
YES	Explain tables populated for dynamic SQL. Results of query returned.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query returned.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query returned.
EXPLAIN	Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed).	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed).	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query not returned (dynamic statements not executed).	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed).
REOPT	Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.
RECOMMEND INDEXES	Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed). Recommend indexes.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed). Recommend indexes.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query not returned (dynamic statements not executed). Recommend indexes.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed). Recommend indexes.
EVALUATE INDEXES	Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed). Evaluate indexes.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed). Evaluate indexes.	Explain tables populated for static SQL when statement qualifies for reoptimization at execution time. Explain tables populated for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query not returned (dynamic statements not executed). Evaluate indexes.	Explain tables populated for static SQL. Explain tables populated for dynamic SQL. Results of query not returned (dynamic statements not executed). Evaluate indexes.

The CURRENT EXPLAIN SNAPSHOT special register interacts with the EXPLSNAP bind option in the following way for dynamic SQL.

Table 3. Interaction of EXPLSNAP bind Option and CURRENT EXPLAIN SNAPSHOT
EXPLAIN SNAPSHOT values	EXPLSNAP Bind option values
EXPLAIN SNAPSHOT values	NO	YES	REOPT	ALL
NO	Results of query returned.	Explain Snapshot taken for static SQL. Results of query returned.	Explain Snapshot taken for static SQL when statement qualifies for reoptimization at execution time. Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain Snapshot taken for static SQL. Explain Snapshot taken for dynamic SQL. Results of query returned.
YES	Explain Snapshot taken for dynamic SQL. Results of query returned.	Explain Snapshot taken for static SQL. Explain Snapshot taken for dynamic SQL. Results of query returned.	Explain Snapshot taken for static SQL when statement qualifies for reoptimization at execution time. Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain Snapshot taken for static SQL. Explain Snapshot taken for dynamic SQL. Results of query returned.
EXPLAIN	Explain Snapshot taken for dynamic SQL. Results of query not returned (dynamic statements not executed).	Explain Snapshot taken for static SQL. Explain Snapshot taken for dynamic SQL. Results of query not returned (dynamic statements not executed).	Explain Snapshot taken for static SQL when statement qualifies for reoptimization at execution time. Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query not returned (dynamic statements not executed).	Explain Snapshot taken for static SQL. Explain Snapshot taken for dynamic SQL. Results of query not returned (dynamic statements not executed).
REOPT	Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain Snapshot taken for static SQL when statement qualifies for reoptimization at execution time. Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain Snapshot taken for static SQL when statement qualifies for reoptimization at execution time. Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.	Explain Snapshot taken for static SQL when statement qualifies for reoptimization at execution time. Explain Snapshot taken for dynamic SQL when statement qualifies for reoptimization at execution time. Results of query returned.