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.
| Command option | Option values | Used with |
|---|---|---|
| EXPLAIN |
The EXPLAIN(ONLY)option is not valid for REBIND PACKAGE for advanced triggers.
- The
EXPLAIN(ONLY)option is not valid for BIND SERVICE or REBIND PACKAGE for native REST services.
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 that are 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 )
- Inserts information in the tables that are populated by EXPLAIN. When EXPLAIN(ONLY) is specified, Db2 does not insert a new row or update a row in catalog table SYSIBM.SYSPACKAGE, or permanently insert or update a package in the directory. However, when EXPLAIN(ONLY) is specified, Db2 uses all the resources that it uses for a regular BIND or REBIND. For example, during EXPLAIN(ONLY) processing, Db2 acquires the same locks as it acquires during regular BIND or REBIND processing.
EXPLAIN(ONLY) 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. The access path described by the PLAN_TABLE data for a statement when access path reuse fails describes the new access path that Db2 selects when no reuse or APREUSE(WARN) is specified.
A 'Y' value in the BIND_EXPLAIN_ONLY column indicates PLAN_TABLE rows that are created under this option.
The EXPLAIN(ONLY)option is not valid for REBIND PACKAGE for advanced triggers.The EXPLAIN(ONLY)option is not valid for BIND SERVICE or REBIND PACKAGE for native REST services.
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.
Default values for EXPLAIN
| Process | Default value |
|---|---|
| BIND SERVICE | NO |
| BIND PLAN | NO |
| BIND PACKAGE | NO |
| REBIND PLAN | NO |
| REBIND PACKAGE | Existing value |
Catalog records for EXPLAIN
See the EXPLAIN column in SYSPACKAGE catalog table or SYSPLAN catalog table.
