Capturing access path information in EXPLAIN tables

You can populate EXPLAIN tables with information about the access paths that DB2® uses to process your SQL statements.

Before you begin

The following prerequisites must be met:

  • EXPLAIN tables, including PLAN_TABLE, exist under the appropriate schema.
  • You have the required authorities and privileges. For detailed information about the authorities and privileges, see EXPLAIN.

Procedure

To capture EXPLAIN information for SQL statements, use any of the following approaches:

  • Issue an EXPLAIN statement. If aliases are defined on explain tables that were created with a different authorization ID, and you have the appropriate SELECT and INSERT privileges, you can populate the EXPLAIN tables even if you do not own them.

    You can issue the EXPLAIN statement statically from an application program, or dynamically by using QMF™, SPUFI, or the command line processor.

    The process that DB2 uses to capture the access path information depends on options that you specify in the EXPLAIN statement. For example, if you specify the PLAN FOR explainable-statement option, DB2 uses the access path selection process to generate the access path information.

    However, if you specify the PACKAGE option, DB2 extracts information for the existing access paths that were selected for the statements when the package was bound. Similarly, if you specify the STMTCACHE option, DB2 extracts the information for the existing access paths that were selected when the statements were prepared and entered the dynamic statement cache.

  • Specify the EXPLAIN(YES) option when you bind the plan or package. With EXPLAIN(YES), only a small amount of additional processing is required to insert the results in a plan table. The same processing for access path selection is performed, regardless of whether you use EXPLAIN(YES) or EXPLAIN (NO).

    If a plan or package that was previously bound with EXPLAIN(YES) is automatically rebound, the value of the ABEXP subsystem parameter determines whether EXPLAIN information is gathered again during the automatic rebind. Again, inserting the results into a plan table requires only a small amount of overhead.

    When you specify the EXPLAIN(YES) bind option, the information appears in table package_owner.PLAN_TABLE or plan_owner.PLAN_TABLE. For dynamically prepared SQL, the qualifier of PLAN_TABLE is the current SQLID.

    If the plan owner or the package owner has an alias on a PLAN_TABLE that was created by another owner, other_owner.PLAN_TABLE is populated instead of package_owner.PLAN_TABLE or plan_owner.PLAN_TABLE.

  • Start of changeIssue a BIND or REBIND command and specify the EXPLAIN(ONLY) option. The EXPLAIN tables are populated as if EXPLAIN(YES) was specified. However, the bind or rebind operation does not complete for the package. If the specified package already exists it is not dropped or replaced.End of change
  • For remote binds, you can specify EXPLAIN(YES) when binding a package at the server. You can use one of the following approaches:
    • Specify EXPLAIN(YES) from the remote requester when binding a package at the DB2 server. The information appears in a plan table at the server, not at the requester.
    • If the requester does not support the propagation of the EXPLAIN(YES) option, rebind the package at the requester and specify EXPLAIN(YES).
  • Start of changeSpecify the CURRENT EXPLAIN MODE special register in the application. You can use this method to gather EXPLAIN information for dynamic statements, for a specific user and application, without any changes to the application logic.
    Option Description
    CURRENT EXPLAIN MODE = NO No EXPLAIN information is captured when explainable dynamic statements run. NO is the default value.
    CURRENT EXPLAIN MODE = YES Explainable dynamic SQL statements run normally, and information is captured to EXPLAIN tables after each statement is prepared and executed.
    CURRENT EXPLAIN MODE = EXPLAIN Explainable dynamic SQL statements do not execute, but information is captured to EXPLAIN tables after each statement is prepared in the application. Applications whose logic depends on the actual successful execution of statements fail if run with CURRENT EXPLAIN MODE = EXPLAIN. Only applications with simple application logic should use this option.
    When YES or EXPLAIN are specified for the CURRENT EXPLAIN MODE special register, EXPLAIN information is captured during the prepare phase for packages bound with the REOPT(NONE) option and when the statement is reoptimized at run time for packages that are bound with the REOPT(ONCE | ALWAYS | AUTO) bind options.

    For example, the following application program contains a dynamic statement, and EXPLAIN information is generated and inserted into the EXPLAIN tables after the statement is prepared and executed.

    EXEC SQL DECLARE C1 CURSOR FOR PREP_STMT;
SOURCE_STMT = 'SELECT X, Y, Z FROM SCHEMA.TABLE1 WHERE X < Y ';
EXEC SQL SET CURRENT EXPLAIN MODE = YES;
EXEC SQL PREPARE PREP_STMT FROM SOURCE_STMT;
EXEC SQL OPEN C1;
    End of change
  • Invoke the DSNAEXP stored procedure. The DSNAEXP stored procedure is deprecated. End program-specific programming interface information.