Previous Topic: Display a DECLARE StatementNext Topic: View an Explained SQL Statement

Using the Product in a DB2 Environment › Explain Function

Explain Function

As a complement to workload analysis, CA MAT provides an integrated DB2 EXPLAIN function.

CA MAT calls DB2 and issues the EXPLAIN command for both dynamic and static SQL.

CA MAT issues an SQL EXPLAIN command for the statements found during the Monitor process. The command is executed on a thread created by CA MAT to the DB2 subsystem that the target address space is attached to. This thread is independent of the target job being monitored.

If objects such as tables are DROPped, ALTERed, or CREATEd while the job is being monitored, the DB2 EXPLAIN command could receive a return code of -204 (Undefined Name). The DB2 EXPLAIN could also, after an ALTER, return a -206 (name is not valid in the context where it is used).

You can display additional key information about the SQL statement by using the EXPLAIN line command (E) on either the DB2 Statements panel or the SQL Statements panel. You can see useful information in the following areas:

See Viewing an Explained SQL Statement to see an example of the output from the Explain line command.

The source of the SQL statements that are explained is dependent on a combination of global parameter values in the TUNSSP00 UTRPARM member, and options that can be specified at profile definition on the DB2 Monitor Criteria panel. If the recommendations were followed, static SQL is extracted from the DB2 catalog and explained. Dynamic SQL is extracted from internal DB2 objects and then passed to the EXPLAIN command.

The two monitor time options settings that control if the EXPLAIN command is issued are shown next. The options can be overridden on the "DB2 Monitor Criteria" panel when defining the monitor profile. See an example of this panel in the Monitor Criteria for DB2 chapter.

EXPLAIN SQL

Requests that information regarding DB2 access path selection be obtained from DB2 SQL statements by issuing the EXPLAIN command and externalizing the data.

YES

Specifies that DB2 EXPLAIN data is collected for all statements seen. The call for Explain data is made while the address space is being measured. If YES is specified then Use DB2 Catalog for EXPLAIN must also be YES.

NO

Specifies that no DB2 EXPLAIN data is gathered. This is the default.

EXPLAIN Harvested SQL

Requests that information regarding the DB2 access path selection is obtained from DB2 SQL statements that were extracted using the Synchronous Data Gatherer.

YES

Specifies that Explain data is collected for each dynamic SQL statement and all SQL statements in a DBRM or package that are seen by the Synchronous Data Gatherer.

Note: DB2EXPL=YES must be specified with this option.

NO

Specifies that no DB2 Explain of the harvested SQL is performed. This is the default.

CA MAT uses the DB2 EXPLAIN command to acquire access path data. The DB2 EXPLAIN command uses three tables to store this information. The table names used by the EXPLAIN command are

The qualifier, as required by CA MAT, is CAMAT.

CAMAT.PLAN_TABLE, CAMAT.DSN_STATEMNT_TABLE, and CAMAT.FUNCTION_TABLE are created in the installation process during Step 10.

The sample BIND statements following show the package name that is required to be bound in the DB2 plan. The BIND for the DB2 plan is distributed in CEESJCL member TNCEBND.

DSN SYSTEM(DIA3)
      BIND PACKAGE(MAT85) MEMBER(TNMESQLM) +
           QUALIFIER(MAT85DB2) +
           ACTION(REP) ISOLATION(CS) +
           RELEASE(COMMIT) +
           ENCODING(EBCDIC) +
           DEGREE(ANY) ENABLE(*)
*
************************************************
* BIND PERFORMANCE PRODUCTS MAIN PLAN               *
************************************************
*
      BIND PLAN(MAT85DB2) +
           OWNER(DB2USR)        +
           QUALIFIER(MAT85DB2) +
           RETAIN               +
           ISOLATION(CS)        +
           VALIDATE(BIND)       +
           ACTION(REPLACE)      +
           ENCODING(EBCDIC) +
           PKLIST(*.MAT85.*)