EXPLAIN statement
The EXPLAIN statement captures information about the access plan chosen for the supplied explainable statement and places this information into the explain tables.
An explainable statement can either be a valid XQuery statement or one of the following SQL statements: CALL, Compound SQL (Dynamic), DELETE, INSERT, MERGE, REFRESH, SELECT, SELECT INTO, SET INTEGRITY, UPDATE, VALUES, or VALUES INTO.
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 ID of the statement must hold at least one of the following authorizations:- DATAACCESS authority which allows an INSERT, UPDATE, DELETE, or SELECT statement.
- INSERT privilege on the explain tables and at least one of the
following authorizations:
- All the privileges that are necessary to execute the explainable statement that is specified in the EXPLAIN statement (for example, if a DELETE statement is used as the explainable statement, the authorization rules for the DELETE statement are applied when the DELETE statement is explained)
- EXPLAIN authority
- SQLADM authority
- DBADM authority
Syntax
Description
- PLAN SELECTION
- Indicates that the information from the plan selection phase of query compilation is to be inserted into the explain tables.
- ALL
- Specifying ALL is equivalent to specifying PLAN SELECTION.
- PLAN
- The PLAN option provides syntax toleration for existing database applications from other systems. Specifying PLAN is equivalent to specifying PLAN SELECTION.
- FOR SNAPSHOT
- This clause indicates that only an explain snapshot is to be taken and placed into the SNAPSHOT column of the EXPLAIN_STATEMENT table. No other explain information is captured other than that present in the EXPLAIN_INSTANCE and EXPLAIN_STATEMENT tables.
- WITH SNAPSHOT
- This clause indicates that, in addition to the regular explain
information, an explain snapshot is to be taken.
The default behavior of the EXPLAIN statement is to only gather regular explain information and not the explain snapshot.
- default (neither FOR SNAPSHOT nor WITH SNAPSHOT specified)
- Puts explain information into the explain tables.
- WITH REOPT ONCE
- This clause indicates that the specified explainable statement is to be reoptimized using the values for host variables, parameter markers, special registers, or global variables that were previously used to reoptimize this statement with REOPT ONCE. The explain tables will be populated with the new access plan. If the user has DBADM authority, or the database registry variable DB2_VIEW_REOPT_VALUES is set to YES, the EXPLAIN_PREDICATE table will also be populated with the values if they are used to reoptimize the statement.
- SET QUERYNO = integer
- Associates integer, via the QUERYNO
column in the EXPLAIN_STATEMENT table, with the explainable
statement. The integer value supplied must be a positive
value.
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.
- SET QUERYTAG = string-constant
- Associates string-constant, via the
QUERYTAG column in the EXPLAIN_STATEMENT table, with the explainable
statement. string-constant can
be any character string up to 20 bytes in length. If the value supplied
is less than 20 bytes in length, the value is padded on the right
with blanks to the required length.
If this clause is not specified for an EXPLAIN statement, blanks are used as the default value.
- FOR explainable-sql-statement
- Specifies the SQL statement to be explained. This statement can
be any valid CALL, Compound SQL (Dynamic), DELETE, INSERT, MERGE,
REFRESH, SELECT, SELECT INTO, SET INTEGRITY, UPDATE, VALUES, or VALUES
INTO SQL statement. If the EXPLAIN statement is embedded in a program,
the explainable-sql-statement can contain
references to host variables (these variables must be defined in the
program). Similarly, if EXPLAIN is being dynamically prepared, the explainable-sql-statement can
contain parameter markers.
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.
- FOR XQUERY 'explainable-xquery-statement'
- Specifies the XQUERY statement to be explained. This statement can be any valid XQUERY
statement.
If the EXPLAIN statement is embedded in a program, the 'explainable-xquery-statement' can contain references to host variables, provided that the host variables are not used in the top level XQUERY statement, but are passed in through an XMLQUERY function, by an XMLEXISTS predicate, or by an XMLTABLE function. The host variables must be defined in the program.
Similarly, if EXPLAIN is being dynamically prepared, the 'explainable-xquery-statement' can contain parameter markers, provided that the same restrictions as for passing host variables are followed.
Alternatively, the XQUERY function db2-fn:sqlquery can be used to embed SQL statements with references to host variables and parameter markers.
The 'explainable-xquery-statement' must be a valid XQUERY statement that could be prepared and executed independently of the EXPLAIN statement. Query statements referring to cursors defined through CLP are not valid for use with this statement.
Notes
- The Explain facility uses the following IDs as the schema when
qualifying explain tables that it is populating:
- The session authorization ID for dynamic SQL
- The statement authorization ID for static SQL
- The following table shows the interaction of the snapshot keywords
and the explain information.
If neither the FOR SNAPSHOT nor the WITH SNAPSHOT clause is specified, an explain snapshot is not taken.
Keyword Specified Capture Explain Information? none Yes FOR SNAPSHOT No WITH SNAPSHOT Yes - The explain tables must be created by the user before invocation of the EXPLAIN statement. The information generated by this statement is stored in the explain tables, in the schema that is designated at the time the statement is compiled.
- If any errors occur during the compilation of the explainable statement supplied, then no information is stored in the explain tables.
- The access plan generated for the explainable statement is not saved and thus, cannot be invoked at a later time. The explain information for the explainable statement is inserted when the EXPLAIN statement itself is compiled.
- For a static EXPLAIN query statement, the information is inserted into the explain tables at bind time and during an explicit rebind. 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 or PREP options. Static EXPLAIN statements can be used to cause the explain tables to be populated for one specific static query 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 or 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.
- Static EXPLAIN statements in an SQL procedure are evaluated when the procedure is compiled.
- For an incremental bind EXPLAIN query 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 query 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.
- If the REOPT bind option is set to ONCE, and either the CURRENT EXPLAIN MODE or the CURRENT EXPLAIN SNAPSHOT special register is set to REOPT, the execution of static and dynamic query statements containing host variables, special registers, parameter markers, or global variables will cause explain information to be captured for the statement only when the statement is reoptimized. Alternatively, if the REOPT bind option is set to ALWAYS, explain information will be captured every time these statements are executed.
Examples
- Example 1: Explain a simple SELECT statement and tag with
QUERYNO = 13.
EXPLAIN PLAN SET QUERYNO = 13 FOR SELECT C1 FROM T1
- Example 2: Explain a simple SELECT statement and tag with
QUERYTAG = 'TEST13'.
EXPLAIN PLAN SELECTION SET QUERYTAG = 'TEST13' FOR SELECT C1 FROM T1
- 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
- Example 4: Attempt to get explain information when explain
tables do not exist.
This statement will fail because the explain tables have not been defined (SQLSTATE 42704).EXPLAIN ALL FOR SELECT C1 FROM T1
- Example 5: The following statement will succeed if it is
found in the package cache and has already been compiled using REOPT
ONCE.
EXPLAIN ALL WITH REOPT ONCE FOR SELECT C1 FROM T1 WHERE C1 = :<host variable>
- Example 6: The following example uses the db2-fn:xmlcolumn
function, which takes the case- sensitive name of an XML column as
an argument and returns an XML sequence that is the concatenation
of XML column values.Consider a table called BUSINESS.CUSTOMER with an XML column called INFO. A simple XQuery that returns all documents from the INFO column is :
EXPLAIN PLAN SELECTION FOR XQUERY 'db2-fn:xmlcolumn ("BUSINESS.CUSTOMER.INFO")'
If a column value is null, then the resulting return sequence for that row will be empty.