How EXPLAIN Works

 <  Day Day Up  >  

How EXPLAIN Works

To 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:

  • The access paths that DB2 chooses are externalized, in coded format, into a PLAN_TABLE .

  • Cost estimates for the SQL statements are formulated and inserted into a DSN_STATEMNT_TABLE .

  • The user -defined functions that will be used are placed into a DSN_FUNCTION_TABLE .

Figure 25.1. How EXPLAIN works.

graphics/25fig01.gif


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  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net