SQL Reference

Appendix L. Explain Register Values

This appendix describes the interaction of the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values with each other and with the PREP and BIND commands.

The CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special register values interact in the following way for dynamic SQL.

Table 142. Interaction of Explain Special Register Values for Dynamic SQL

EXPLAIN SNAPSHOT values EXPLAIN MODE values

NO YES EXPLAIN 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.
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
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
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 SNAPSHOT values	EXPLAIN MODE values
NO	YES	EXPLAIN	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. 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 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 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.

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

Table 143. Interaction of EXPLAIN Bind Option and CURRENT EXPLAIN MODE

EXPLAIN MODE values EXPLAIN Bind option values
NO YES ALL
NO

Results of query returned.

Explain tables populated for static SQL
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
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
Explain tables populated for dynamic SQL
Results of query not returned (Dynamic statements not executed).

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
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
Explain tables populated for dynamic SQL
Results of query not returned (Dynamic statements not executed).
Evaluate indexes

EXPLAIN MODE values	EXPLAIN Bind option values
NO	YES	ALL
NO	Results of query returned.	Explain tables populated for static SQL 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 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 Explain tables populated for dynamic SQL Results of query not returned (Dynamic statements not executed).
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 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 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 144. Interaction of EXPLSNAP bind Option and CURRENT EXPLAIN SNAPSHOT

EXPLAIN SNAPSHOT values EXPLSNAP Bind option values
NO YES ALL
NO

Results of query returned.

Explain Snapshot taken for static SQL
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
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
Explain Snapshot taken for dynamic SQL
Results of query not returned (Dynamic statements not executed).

EXPLAIN SNAPSHOT values	EXPLSNAP Bind option values
NO	YES	ALL
NO	Results of query returned.	Explain Snapshot taken for static SQL 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 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 Explain Snapshot taken for dynamic SQL Results of query not returned (Dynamic statements not executed).

[ Top of Page | Previous Page | Next Page ]