Access Plan
An alternate way to analyze the query slowdown problem, is by going through the explain snapshot.
An explain snapshot is compressed information that is collected when an SQL statement is explained. It contains
- The internal representation of the access plan (graphical as well), including its operators and the tables and indexes accessed.
- The decision criteria used by the optimizer, including statistics for database objects and the cumulative cost for each operation.
Run the following steps on the DB2 CLP window to view the explain snapshot :
- Change to directory DB2_install_directory\sqllib\misc.
- Connect to the database as db2 connect to designdb.
- Run db2 -tvf EXPLAIN.DDL. This creates the explain tables.
- Run db2 SET CURRENT EXPLAIN MODE=YES.
- Go the Ad Hoc SQL tab, Select Options. Note the Default Schema used for this workshop.
- Change to directory TE_install_directory\tutorials\Workshops\DesignforQueryPerformance\Indexing.
- In the tutorial, under the directory SQL, look for the file query_index.db2 replace 'SCHEMA' with the Default Schema.
- Run db2 -tvf query_index.db2.
- Run db2 SET CURRENT EXPLAIN MODE=NO.
- Run db2exfmt -d sample -1 -o explain_index.out. The Explain Plan can be viewed in the output file explain_index.out.