How to get an access plan in DB2 using db2exfmt
The most common way used to generate and retrieve access plans in DB2 is by using DB2 Control Center. CC provides a graphical representation of a query’s access plan, and it also includes an Index Advisor, that you can use when you are not sure about which indexes to create and to use.
However, not always you have access to a graphical environment (needed to run DB2 Control Center). For the command line, there are two utilities that you can use. DB2 EXPLAIN command and the db2exfmt utility. Although the first one is more complete, I find the second one to be easier to use. So, in order to get an access plan for your query using db2exfmt, you just need to do:
- db2 -tvf ~/sqllib/misc/EXPLAIN.DDL (create the explain tables where all the explain data will be stored)
- db2 set current explain mode explain (this will put DB2 in explain mode
and all the subsequent queries won’t be run, but explain data will be
gathered) - run your workload (e.g., db2 -tvf query.sql)
- db2exfmt -d dbname -1 -o output.txt (formats the information on the explain tables)
Detailed information about the access plan for your query will be in the file output.txt. By using this information, you can see which indexes are being used or not as also other performance considerations about your query.
PS: don’t forget to run "db2 set current explain mode no" when you are done with your access plans, so the queries will be executed.
Popularity: 10% [?]
Related Entries:
