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
>>-EXPLAIN--+-PLAN SELECTION-+--+--------------------+---------->
+-ALL------------+ '-+-FOR--+--SNAPSHOT-'
| (1) | '-WITH-'
'-PLAN-----------'
>--+-----------------+--+------------------------+-------------->
'-WITH REOPT ONCE-' '-SET QUERYNO = -integer-'
>--+---------------------------------+-------------------------->
'-SET QUERYTAG = -string-constant-'
>--FOR--+-explainable-sql-statement--------------+-------------><
'-XQUERY--'explainable-xquery-statement'-'
Notes:
- The PLAN option is
supported only for syntax toleration of existing DB2® for z/OS® EXPLAIN
statements. There is no PLAN table. Specifying PLAN is equivalent to specifying PLAN
SELECTION.
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
DB2 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 schema can be associated with a set of explain tables, or
aliases that point to a set of explain tables under a different schema.
If no explain tables are found under the schema, the Explain facility
checks for explain tables under the SYSTOOLS schema and attempts to
use those tables.
- The following table shows the interaction of the snapshot keywords
and the explain information.
Keyword Specified |
Capture Explain Information? |
none |
Yes |
FOR SNAPSHOT |
No |
WITH SNAPSHOT |
Yes |
If neither the FOR SNAPSHOT nor the WITH SNAPSHOT clause is
specified, an explain snapshot is not taken.
- 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.
EXPLAIN ALL FOR SELECT C1
FROM T1
This statement will fail because the
explain tables have not been defined (SQLSTATE 42704).
- 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.