DB2: Here is All About Explain Plan

DB2 EXPLAIN

The primary function of the explain is to provide access path information for select statements. Information regarding delete, update, and insert statements are somewhat limited in information.

For SQL statements referencing a single table, the explain can describe whether table or index access will be used to retrieve the information and what I/O methods will be used.

The explain will also describe join methods and the order in which tables will be referenced for SQL statements referencing multiple tables table.

Before an explain can be executed, the DB2 plan tables must be created to hold the results of the explain.

Issuing an explicit explain on a single SQL statement or using the explain parameter on a bind package or plan will populate the plan table with explain information.

Below is an example of the information found in the plan table when running an explain on the following SQL statement:

SELECT
A.EMPNO, A.FIRSTNME, A.LASTNAME,
A.WORKDEPT, B.DEPTNAME
FROM
DSN8710.EMP A,
DSN8710.DEPT B
WHERE
B.DEPTNO = A.WORKDEPT
AND
A.EMPNO IN
(SELECT C.EMPNO FROM DSN8710.EMPPROJACT C
WHERE C.PROJNO = 'MA2112');

Interpreting PLAN_TABLE:

Interpreting the plan table is difficult at best. Without the table and index definitions, it would be impossible to analyze this access path. The results of the explain can be translated into the following:

1. EMPPROJACT table is the first table accessed using an index only scan on XEMPPROJACT1. Using SPUFI, it can be determined that XEMPPROJACT1 is a unique index based on multiple columns. PROJNO is the first column of the index and that is how the data is being accessed.

2. The results of retrieving the EMPPROJACT table are sorted to remove duplicates and to satisfy the IN predicate, or in this case the results are sorted by PROJNO. 12

3. EMP is the outer table of a nested loop join with the DEPT table. This table is being accessed using a matching index scan because of the IN predicate. The XEMP1 index is retrieving data in the order of DEPT.

4. DEPT is the inner table of a nested loop join with the EMP table. Because this is the inner table it will be scanned each time a new outer row (EMP) is retrieved. This table is being accessed using a matching index scan on XDEPT1. XDEPT1 is indexed on WORKDEPT, and the data is being retrieved by that column.
5. Results are presented back to the user. The results of this explain leave a lot of unanswered questions.

Summary

Simple questions such as ‘how many rows are in these tables?’, or ‘what columns exist for an index?’ are left unanswered by the explain. Again the reason is the explain was not designed as a SQL tuning tool, it was designed simply to present the access path the DB2 optimizer chose for this SQL statement.

References

Reference Here