Access Path Analysis

The Access Path Analysis screen displays when you select option 1 from the Plan Table Analysis screen. It provides a detailed analysis of how the SQL statement is analyzed and executed by Db2. For example, the sequence and manner in which various tables are accessed, the locking method that is used, and a comprehensive description of both consequences and recommendations.

Access Path Analysis Screen

The report contains header information and a Conditions, Consequences, and Recommendations section for each table and/or index used by the SQL statement. The text in these sections can be modified to meet your site’s needs. See Modifying-Access-Path-Analysis-Text for information on modifying the text.

Header Information

The top of the report contains header information, including the Db2 Version and the Query Number. The Db2 Version is the Db2 release for the subsystem upon which the query is executed. The Query Number identifies the EXPLAINed statement. File-AID for Db2 assigns the program statement number as the query number unless the statement has a QUERYNO clause specified. The explain command may generate one or more rows in the PLAN_TABLE. If such is the case, all rows with the same query number refer to the same SQL statement. The PLAN_TABLE only contains the QUERYNO, not the statement number.

Conditions

The Conditions section of the report identifies the Query Block Number and Plan Number for the statement. The Query Block Number (QBlock Number) identifies the position of the query within the SQL statement. For example:

SELECT * FROM ABC WHERE COL1 IN
SELECT COL1 FROM DEF WHERE COL2 = 0

The outer SELECT will have a Query Block Number of 1 and the second SELECT a Query Block Number of 2.

The Plan Number identifies the sequence in which the steps within the Query Block Number specified are processed.

Tip

The following data can also display in the Conditions section, depending on the type of statement being explained: Query Block Type, Parent Query Block Type, Method of Join, and Join Type.

The Conditions section also contains information on any locking that may be performed as well as any index utilization.

Consequences

The Consequences section of the report outlines the performance ramifications of the SQL and the effects of object volume. The analysis is performed using actual catalog statistics in effect at the time the analysis operation was performed. To maximize the potential of the analysis operation, use objects with characteristics that closely resemble the objects that the SQL will ultimately be used against.

Recommendations

The Recommendations section of the report offers suggestions for improving the performance of the SQL statement. The recommendations are based on actual, not potential, object statistics. You may need to scroll with the DOWN primary command to view this section.

Executing the SQL Command

When you execute the SQL primary command, a temporary data set containing the SQL source is created. An ISPF/PDF edit session is initiated enabling you to edit the SQL. If you want to save the data set, you must use the ISPF/PDF CREATE or COPY command before exiting the window.

SQL Source — After Executing the SQL Command

When you press END to exit the window, the SQL is automatically re-explained and the SQL Analysis Summary screen displays (SQL Analysis Summary Screen — After a Re-explain).

The modified SQL is inserted directly after the original statement. The same DBRM/Package name and Statement number are used. The new Db2 cost value and access type are displayed. The new row is identified by an asterisk (*) in front of the DBRM/Package name, enabling you to compare the results of the changes immediately.

Important

In the modified SQL, the host variables are always substituted with parameter markers prior to the re-explain. This may affect the resulting Db2 cost.

SQL Analysis Summary Screen — After a Re-explain