< Day Day Up > |
How EXPLAIN WorksTo see how EXPLAIN works, refer to Figure 25.1. A single SQL statement, or a series of SQL statements in a package or plan, can be the subject of an EXPLAIN . When EXPLAIN is requested , the SQL statements are passed through the DB2 optimizer, and the following three activities are performed:
Figure 25.1. How EXPLAIN works.
The PLAN_TABLE , DSN_STATEMNT_TABLE , and DSN_FUNCTION_TABLE objects are nothing more than standard DB2 tables that must be defined with predetermined columns , data types, and lengths. To EXPLAIN a single SQL statement, precede the SQL statement with the EXPLAIN command as follows : EXPLAIN ALL SET QUERYNO = integer FOR SQL statement ; It can be executed in the same way as any other SQL statement. QUERYNO , which you can set to any integer, is used for identification in the PLAN_TABLE . For example, the following EXPLAIN statement populates the PLAN_TABLE with the access paths chosen for the indicated sample table query: EXPLAIN ALL SET QUERYNO = 1 FOR SELECT FIRSTNME, MIDINIT, LASTNAME FROM DSN8610.EMP WHERE EMPNO = '000240'; Another method of issuing an EXPLAIN is as a part of the BIND command. If you indicate EXPLAIN(YES) when binding a package or a plan, DB2 externalizes the access paths chosen for all SQL statements in that DBRM (or DBRMs) to the PLAN_TABLE . The final method of issuing EXPLAIN is to use the Visual Explain tool to invoke EXPLAIN for dynamic SQL statements. Visual Explain also provides an easy to use interface for displaying access paths graphically and suggesting alternate SQL formulations. |
< Day Day Up > |