Systems Engineering and RDBMS

db2exfmt – Command to format the explain plan information in DB2 LUW

Posted by decipherinfosys on August 16, 2007

DB2 LUW has functionality similar to Oracle and SQL Server through which one can see the explain plan information (the path optimizer took for the query along with other information) without actually executing the query. Just like tkprof utility in Oracle which formats the trace file output, db2exfmt is the utility to format the content of the explain tables in DB2.

To use this utility, you need read access to a set of tables (called EXPLAIN tables) in which DB2 stores all the information related to query execution like query statements, Access plan, cost associated with query, objects used in access plan etc. db2exfmt extracts information from all these tables and represents it in a readable format. Not all of these tables are created automatically. One has to run EXPLAIN.DDL script to create all the tables within the database. Script resides in misc subdirectory of sqllib subdirectory on the database server. This set of tables can be common among different users. For more details on these tables, please refer to the IBM documentation here.

Let us see how we can generate explain plan information. We are using db2 command window for our test.

D:\Program Files\IBM\SQLLIB\BIN>db2 set current explain mode explain

The statement above switches the explain mode to be on. After execution of this statement, explain plan information will be collected for any static or dynamic SQL statement without it being executed. In fact DB2 will display warning message indicating statement has not been executed. This will go on until we turned off the explain mode. Let us issue a query now. Please change the query for your test scenario.

D:\Program Files\IBM\SQLLIB\BIN>db2 select * from TEST where TEST_ID = 21
SQL0217W The statement was not executed as only Explain information requests
are being processed. SQLSTATE=01604

It is evident from above statement that DB2 only generated explain information without executing the query. In fact it does not display back the results. Now we will switch off the explain plan mode.

D:\Program Files\IBM\SQLLIB\BIN>db2 set current explain mode no

Now let us generate the text file using db2exfmt command and see the information. db2exfmt.exe resides in bin subdirectory under sqllib subdirectory on database server.

D:\Program Files\IBM\SQLLIB\BIN>db2exfmt -d decipher -# 0 -w -1 -g TIC -n % -s % -o test_expl.txt

For explanation of each of the flag value, please refer to IBM document here.

We cannot put entire text file generated by command. You can examine the text file generated and look for obvious points which can be improved for a given query but here is the edited output.

DB2 Universal Database Version 9.1, 5622-044 (c) Copyright IBM Corp. 1991, 2006
Licensed Material – Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

******************** EXPLAIN INSTANCE ********************

Original Statement:
select *
from TEST
where TEST_Name = ‘DB2’

Optimized Statement:

Access Plan:


Total Cost: 15.5586

Query Degree: 1



( 1)






( 2)




1 125


( 3) TEST







This is very useful when we are working on large volume databases to make sure that query takes optimal path without waiting on the execution of the query.

Sorry, the comment form is closed at this time.

%d bloggers like this: