EXPLAIN bind option
The EXPLAIN option causes the bind process to obtain information about how SQL statements in the package or packages are to execute.
EXPLAIN | ( NO ) ( YES ) ( ONLY ) | On BIND and REBIND PLAN and PACKAGE, REBIND TRIGGER PACKAGE |
The bind process inserts that information into the table owner .PLAN_TABLE. Owner can be the authorization ID of the owner of the plan or package. Alternatively, the authorization ID of the owner of the plan or package can have an alias as owner .PLAN_TABLE that points to the base table, PLAN_TABLE. Owner must also have the appropriate SELECT and INSERT privileges on that table. This option does not obtain information for statements that access remote objects.
PLAN_TABLE must have a base table and can have multiple aliases with the same table name, PLAN_TABLE, but using different authorization IDs; it cannot be a view or a synonym. It should exist before the bind process begins.
The EXPLAIN option also populates all of the explain tables, except for the following:
- DSN_QUERY_TABLE
- DSN_STATEMENT_CACHE_TABLE
You can get EXPLAIN output for a statement that is embedded in a program that is bound with EXPLAIN(NO) by embedding the SQL statement EXPLAIN in the program. Otherwise, the value of the EXPLAIN option applies to all explainable SQL statements in the program, and to the fullselect portion of any DECLARE CURSOR statements.
In all inserts to owner .PLAN_TABLE, the value of QUERYNO is the statement number that the precompiler assigned and placed in the DBRM.
- ( NO )
- Provides no EXPLAIN information.
- ( YES )
- Inserts information in the tables populated by EXPLAIN.
No secondary authorization checks for the owner authorization ID are
performed during the bind operation. You must grant all privileges
and authorities directly to the owner authorization ID. If owner .PLAN_TABLE
does not exist at bind time, the value of the option VALIDATE determines
the success of the bind operation.
- If the value is BIND, the bind fails.
- If the value is RUN, DB2® checks to see if the table exists again at run time. If it still does not exist, the plan or package cannot run. If it does exist, DB2 inserts information in PLAN_TABLE before the plan or package runs.
If neither or both of the optional tables DSN_FUNCTION_TABLE or DSN_STATEMNT_TABLE exist, or if they are defined incorrectly, the bind does not fail.
- ( ONLY )
- Populates EXPLAIN tables but does not create or rebind a package.
This option allows EXPLAIN to be run when the authorization ID of
the bind or rebind process does not have the privilege to execute
statements in the package. If the command that specifies EXPLAIN(ONLY)
refers to a package and version that already exists, the existing
package is not dropped or replaced, even if ACTION(REPLACE) is specified.
If an error is encountered during population of the EXPLAIN tables,
information is not added to the EXPLAIN tables for the statement where
the error occurred, and for any subsequent statements.The EXPLAIN(ONLY) option is also useful for testing whether access paths can be successfully reused before you bind or rebind a package with the APREUSE(ERROR) or APCOMPARE(ERROR) bind options. When EXPLAIN(ONLY) is specified with those options, the PLAN_TABLE is populated with information about all statements in a package, even when reuse or comparison fails for the access path for one or more statements. You can examine the HINT_USED and REMARKS columns to discover which statements in the package that cannot be reused.Important: Do not rely upon the PLAN_TABLE data for a statement when access path reuse fails. The access path described by the data does not match the previous access path for the statement or the access path that DB2 selects when reuse is not specified. The data is useful only for identifying the statements that cannot reuse the previous access path.
A 'Y' value in the BIND_EXPLAIN_ONLY column indicates PLAN_TABLE rows that are created under this option.
Invalidation resulting from an unsuccessful rebind: An unsuccessful rebind generating a return code of greater than 4 invalidates the rebind object and rolls back all changes to the object, leaving it as it was before the rebind attempt. However, if the rebind fails because of either the REBIND option EXPLAIN or the SQL statement EXPLAIN (that is, the PLAN_TABLE does not exist or was created incorrectly), DB2 rolls back all changes to the object but does not invalidate the object.
Defaults:
Process | Default value |
---|---|
BIND PLAN | NO |
BIND PACKAGE | NO |
REBIND PLAN | NO |
REBIND PACKAGE | Existing value |
Catalog record: Column EXPLAIN of table SYSPACKAGE and column EXPLAIN of SYSPLAN.