Click on the action buttons in the action panel to get the query execution time for tables enabled
with DPF and generate a graph for the same
Operation
Get Query Execution Time
Query execution time for tables enabled with DPF is computed
Performance
- A graph showing the query execution time for DPF enabled tables is generated.
- If you hover your cursor over the bar in the graph you will see the exact execution time for each run.
Result
The query execution time for the tables enabled with DPF is noted
DPF helps achieve scaleability and manageability of data by splitting data logically across partitions
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\DatabasePartitioning
- In the file
query_DPF.db2
replace 'SCHEMA' with the Default Schema.
- Run db2 -tvf
query_DPF.db2
- Run db2 SET CURRENT EXPLAIN MODE=NO
- Run db2exfmt -d
designdb
-1 -o explain_DPF.out
. The Explain Plan can be viewed in the output file explain_DPF.out
.
Click here to view the sample output of the Explain Access Plan : Sample Explain Access Plan