DB2 LUW Explain table format (db2exfmt) - Why and How to generate Access plan?

In this post, we will be looking at why/when and how to generate an access plans in DB2 LUW?  

Before delving into this topic, let's have a look at what is an Access plan? Access plan in DB2 LUW will show the details like Original and Optimized query, Statistics and Operators used, Graph, Total cost in timerons, Objects used etc used by query to retrieve the data from tables.

Why/When to generate Access plan?

Whenever we encounter an Performance issue with a query/we want to see how the DB2 Optimizer is pulling data as per User requests.

How to generate Access plan? 

We follow below steps to generate Access plan. Of course a connection to the database must be established as Instance owner.

Step 1: Check if EXPLAIN (or) ADVISE tables exists in the database.

$ db2 "list tables for all" | grep -Ei "EXPLAIN|ADVISE"



Step 2: At times we may have to drop the existing EXPLAIN (or) ADVISE tables. Generate the drop statement using below command. This step is optional

$ db2 "list tables for all" | grep -Ei "EXPLAIN|ADVISE" | awk '{print "drop table "$2"."$1";"}'




Step 3: Ensure that Explain tables are created.

$ db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN', 'C', CAST (NULL AS VARCHAR(128)), CAST (NULL AS VARCHAR(128)))"

(or)

$ db2 -tf ~/sqllib/misc/EXPLAIN.DDL








Step 4: Enable the Explain facility to capture Explain information. When Explain facility is enabled, queries will not execute and will return a warning as shown in Step 3.
 $ db2 "set current explain mode  EXPLAIN"

Step 5: Execute the problem query.
I  would suggest to keep the query in a file say "query.sql" and ensure that table names are fully qualified (schemaname.tablename format). Please note that this query does not get executed and instead it prints a warning message like below
$ db2 -tvf query.sql

SQL0217W  The statement was not executed as only Explain information requests
are being processed.  SQLSTATE=01604

Step 6: Disable the Explain facility.
$ db2 "set current explain mode NO"

Step 7: Now execute db2exfmt command exactly as shown below and it will generate the Access plan in the file "db2exfmt.query.sql.out"
$ db2exfmt -d <dbname> -n % -s % -g TIC -w -1 -# 0 -o db2exfmt.query.sql.out

dbname --> would be the name of database which is having Performance issues


Comments, you are welcome :-)

P.S. We can alternatively use db2expln and db2top as well to generate access plan. 
However, I would recommend to use db2exfmt as it gives a better output compared to db2expln