16.10. The Explain Tool and Explain TablesThe explain tool examines the access plan chosen by the DB2 optimizer for your SQL statements. Explain information must be captured before you can review it using one of DB2's explain tools. While the query is being compiled, the information can be captured into a file or special tables known as explain tables. DB2 uses explain tables to store access plan information so that users can see the decisions that the optimizer has made. These explain tables are listed in Table 16.6.
The explain tables have to be created before any explain information can be gathered. This is normally done automatically the first time you invoke Visual Explain from the Command Editor. If you need to create the tables manually, use the script file EXPLAIN.DDL located in the misc subdirectory of the SQLLIB directory. This file contains the definition of the explain tables. To create the explain tables, you can connect to the database and then run the following command: db2 tvf explain.ddl The explain tool can be invoked with the EXPLAIN statement, which has the following syntax: >>-EXPLAIN--+-PLAN SELECTION-+--+--------------------+----------> +-ALL------------+ '-+-FOR--+--SNAPSHOT-' '-PLAN-----------' '-WITH-' >--+-----------------+--+------------------------+--------------> '-WITH REOPT ONCE-' '-SET QUERYNO =--integer-' >--+---------------------------------+--------------------------> '-SET QUERYTAG =--string-constant-' >--FOR--explainable-sql-statement------------------------------>< Note that:
If you don't specify any of these options, which is the default, only EXPLAIN data is collected. This will provide EXPLAIN data, but not snapshot data required by the Visual Explain tool. For example, the following statement populates the EXPLAIN tables with EXPLAIN and snapshot data for the query select * from employee: EXPLAIN PLAN WITH SNAPSHOT FOR "select * from employee" |