The EXPLAIN statement captures information about the access plan chosen for the supplied explainable statement and places this information into the Explain tables. (See Appendix K, Explain Tables and Definitions for information on the Explain tables and table definitions.)
An explainable statement is a DELETE, INSERT, SELECT, SELECT INTO, UPDATE, VALUES, or VALUES INTO SQL statement.
Invocation
This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.
The statement to be explained is not executed.
Authorization
The authorization rules are those defined for the SQL statement specified in the EXPLAIN statement. For example, if a DELETE statement was used as the explainable-sql-statement (see statement syntax that follows), then the authorization rules for a DELETE statement would be applied when the DELETE statement is explained.
The authorization rules for static EXPLAIN statements are those rules that apply for static versions of the statement passed as the explainable-sql-statement. Dynamically prepared EXPLAIN statements use the authorization rules for the dynamic preparation of the statement provided for the explainable-sql-statement parameter.
The current authorization ID must have insert privilege on the Explain tables.
Syntax
>>-EXPLAIN--+-PLAN SELECTION-+---+-----------------------+------>
+-ALL------------+ '--+-FOR--+---SNAPSHOT--'
| (1) | '-WITH-'
'-PLAN-----------'
>-----+-------------------------+------------------------------->
'-SET QUERYNO =--integer--'
>-----+----------------------------------+---------------------->
'-SET QUERYTAG =--string-constant--'
>----FOR--explainable-sql-statement----------------------------><
Notes:
Description
The Explain Snapshot information is intended for use with Visual Explain.
The default behavior of the EXPLAIN statement is to only gather regular Explain information and not the Explain Snapshot.
The Explain Snapshot information is intended for use with Visual Explain.
If this clause is not specified for a dynamic EXPLAIN statement, a default value of one (1) is assigned. For a static EXPLAIN statement, the default value assigned is the statement number assigned by the precompiler.
If this clause is not specified for an EXPLAIN statement, blanks are used as the default value.
The explainable-sql-statement must be a valid SQL statement that could be prepared and executed independently of the EXPLAIN statement. It cannot be a statement name or host variable. SQL statements referring to cursors defined through CLP are not valid for use with this statement.
To explain dynamic SQL within an application, the entire EXPLAIN statement must be dynamically prepared.
Notes
The following table shows the interaction of the snapshot keywords and the
Explain information.
| Keyword Specified | Capture Explain Information? | Take Snapshot for Visual Explain? |
|---|---|---|
| none | Yes | No |
| FOR SNAPSHOT | No | Yes |
| WITH SNAPSHOT | Yes | Yes |
If neither the FOR SNAPSHOT nor WITH SNAPSHOT clause is specified, then no Explain snapshot is taken.
The Explain tables must be created by the user prior to the invocation of EXPLAIN. (See Appendix K, Explain Tables and Definitions for information on the Explain tables and table definitions.) The information generated by this statement is stored in these explain tables in the schema designated at the time the statement is compiled.
If any errors occur during the compilation of the explainable-sql-statement supplied, then no information is stored in the Explain tables.
The access plan generated for the explainable-sql-statement is not saved and thus, cannot be invoked at a later time. The Explain information for the explainable-sql-statement is inserted when the EXPLAIN statement itself is compiled.
For a static EXPLAIN SQL statement, the information is inserted into the Explain tables at bind time and during an explicit rebind (see REBIND in the Command Reference). During precompilation, the static EXPLAIN statements are commented out in the modified application source file. At bind time, the EXPLAIN statements are stored in the SYSCAT.STATEMENTS catalog. When the package is run, the EXPLAIN statement is not executed. Note that the section numbers for all statements in the application will be sequential and will include the EXPLAIN statements. An alternative to using a static EXPLAIN statement is to use a combination of the EXPLAIN and EXPLSNAP BIND/PREP options. Static EXPLAIN statements can be used to cause the Explain tables to be populated for one specific static SQL statement out of many; simply prefix the target statement with the appropriate EXPLAIN statement syntax and bind the application without using either of the Explain BIND/PREP options. The EXPLAIN statement can also be used when it is advantageous to set the QUERYNO or QUERYTAG field at the time of the actual Explain invocation.
For an incremental bind EXPLAIN SQL statement, the Explain tables are populated when the EXPLAIN statement is submitted for compilation. When the package is run, the EXPLAIN statement performs no processing (though the statement will be successful). When populating the explain tables, the explain table qualifier and authorization ID used during population will be those of the package owner. The EXPLAIN statement can also be used when it is advantageous to set the QUERYNO or QUERYTAG field at the time of the actual Explain invocation.
For dynamic EXPLAIN statements, the Explain tables are populated at the time the EXPLAIN statement is submitted for compilation. An Explain statement can be prepared with the PREPARE statement but, if executed, will perform no processing (though the statement will be successful). An alternative to issuing dynamic EXPLAIN statements is to use a combination of the CURRENT EXPLAIN MODE and CURRENT EXPLAIN SNAPSHOT special registers to explain dynamic SQL statements. The EXPLAIN statement should be used when it is advantageous to set the QUERYNO or QUERYTAG field at the time of the actual Explain invocation.
Examples
Example 1: Explain a simple SELECT statement and tag with QUERYNO = 13.
EXPLAIN PLAN SET QUERYNO = 13 FOR SELECT C1 FROM T1;
This statement is successful.
Example 2:
Explain a simple SELECT statement and tag with QUERYTAG = 'TEST13'.
EXPLAIN PLAN SELECTION SET QUERYTAG = 'TEST13'
FOR SELECT C1 FROM T1;
This statement is successful.
Example 3: Explain a simple SELECT statement and tag with QUERYNO = 13 and QUERYTAG = 'TEST13'.
EXPLAIN PLAN SELECTION SET QUERYNO = 13 SET QUERYTAG = 'TEST13'
FOR SELECT C1 FROM T1;
This statement is successful.
Example 4: Attempt to get Explain information when Explain tables do not exist.
EXPLAIN ALL FOR SELECT C1 FROM T1;
This statement would fail as the Explain tables have not been defined (SQLSTATE 42704).