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.

Begin program-specific programming interface information.

EXPLAIN
  • ( NO )
  • ( YES )
  • ( ONLY )(Not valid for REBIND of a native REST service)
On: Start of changeBIND SERVICE (YES | NO), End of changeBIND 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.

For automatic rebind: EXPLAIN(YES) is in effect if you bind the plan or package with EXPLAIN(YES) and if the value of field EXPLAIN PROCESSING on installation panel DSNTIPO is YES. If EXPLAIN(YES) and VALIDATE(BIND) are in effect and PLAN_TABLE is not correct, the automatic rebind fails.
( 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 )
Start of changeInserts 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.End of change

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.

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 SERVICE NO
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.

End program-specific programming interface information.