Using the Product in a DB2 Environment › Explain Function

Explain Function

As a complement to workload analysis, CA Mainframe Application Tuner provides an integrated DB2 Explain function.

CA Mainframe Application Tuner calls DB2 and issues the EXPLAIN command for both dynamic and static SQL.

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

Consequently, if objects such as tables are DROPped, ALTERed, or CREATEd while the job is being monitored, the DB2 EXPLAIN command might receive a return code of -204 (Undefined Name) or possibly, after an ALTER, 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:

• Statistical and textual information about the access path
• Suggestions about how to improve SQL statement performance

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 dependant on several TUNSSP00 settings. If the installation 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 TUNSSP00 settings that control whether or not the EXPLAIN command is issued are shown next.

DB2EXPL

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

The call for Explain data is made while the address space is being measured. Specifying DB2EXPL=YES indicates that Explain data will be collected for each dynamic SQL statement and all SQL statements in a DBRM or package. DB2EXPL=NO indicates no DB2 Explain data is to be gathered. BATCH indicates DB2 Explain data is collected only for batch jobs and not for online systems, such as CICS.

Explain will be performed for all static SQL found in the DBRM or package, as bound into the DB2 catalog. When DB2CTSQL=NO (do not access the DB2 catalog for SQL) is specified, the statement that is explained is derived from internal DB2 objects.

Dynamic SQL is always explained from SQL derived from internal DB2 objects.

The default is DB2EXPL=NO.

DB2HEXPL

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

DB2EXPL=YES must be specified in conjunction with this option.

Specifying DB2HEXPL=YES indicates that Explain data will be collected for each dynamic SQL statement and all SQL statements in a DBRM or package that are seen by the Synchronous Data Gatherer. DB2HEXPL=NO indicates that no DB2 Explain of the harvested SQL is performed.

This statement is only valid when DB2HVSQL=YES (harvest all SQL) or DB2HVSQL=NO and DB2HVDYN=YES (harvest only dynamic SQL) are specified.

The default is DB2HEXPL=NO.

CA Mainframe Application Tuner 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

• PLAN_TABLE
• DSN_STATEMNT_TABLE
• DSN_FUNCTION_TABLE

The qualifier, as required by CA Mainframe Application Tuner, 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.*)