Examining and optomizing a query

I have some simple quesitons about my DB2 query.  Like "is it using an index when it joins table a to table b?"  I've been looking all day for references that tell how to do this.  I've found didly.

Here are some of the google searches I've done:
db2 query optimization
db2 query plan
db2 explain
db2 explain query
db2 explain results

Nearly every hit is either a page trying to sell a training class or a third party software tool.  Neither are options I can use.

I did find references to the EXPLAIN statement and how to run it.  Seemed promising.  There was nothing about what to do once the statement is executed.  Lots of stuff gets put into the "explain tables" but I've found precious little information about how to interpret the results.

Please do not post examples of how to do what I need.  I'm looking for references.  Please post links to GOOD sites that explian SQL profiling/explaining/optimizing for DB2 databases.

Thank you.

Who is Participating?
Kent OlsenData Warehouse Architect / DBACommented:
Hi GnarOlak,

IBM continues to build Cadillacs that you work on with with wooden hammers from "My First Toolbox".  sigh.....

AQT (Advanced Query Tool) is a low-cost database client with a built in Explain Plan "handler".  It will give you a graphical representation of the plan that's easy to understand.  I don't know if AQT comes with a trial period.

This is a pretty good site for the basics:


Also, Aqua Data Studio has a pretty good (free) database client with a built-in Explain Plan package.  It will convert the explain plan to an easy-to-read diagram that is very clear about the steps and processes.  Here's a link to its description:


The download is at the same site.

Good Luck!
Try this in Google friend: DB2 join table
Try this one friend to save your time:
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

GnarOlakAuthor Commented:
I know how to write SQL queries.  I've been doing it for years.  What I need is a way to determine how db2, a database I have little experience with, will execute the query.

Oracle had an explain plan capability that listed, in order of execution, the tables being joined, wether or not an index was being used, what the index was, etc.  This is what I am looking for in DB2.
GnarOlakAuthor Commented:
I'm going to accept the last answer (not the one about moving the question!).  There was a reference in the How To list that refered me to the db2expln tool.  The reference I found only showed how to run the tool but nothing about interpreting the output.  As such, if I were to accept the answer now I'd grade it C.  If you, nguyenvinhtu, can provide a link or two about interpreting the output of the command I would be grateful and raise that grade to A.

GnarOlakAuthor Commented:

The answer given does provide the basis of the information I was looking for.  The link refered me to a tool that produces a report of how DB2 will execute a query.  As such I am willing to accept that as the answer to my question.

I feel that the answer is a bit weak because it only answers the specific "What tool do I use..."  but not the more general "and how do I use it?"  Perhaps I should have been more thourough when stating my question?

I need to know how DB2 is executing my queries so that I can improve them through restructuring the query, adding indexes, etc.  That being the case I really need to know not just what tool produces the query plan but also how to interpret it.

I was offering a better grade for better information.  It seemed reasonable to me.  If anyone else can provide that additonal information I will split the points.
As I said - give the experts a few days so they can continue helping :)
You just need to explain to them what you just explained to me :) ( they will read it)

This is how the site works - the experts work with you and with the rest of the experts :)
There are essentially thre ways to get explains:
1. db2expln which you have found.
  db2expln is best to "reverse engineer" plans form compiled packages such as procedures.
2. Visual explain
   This is part of the command center GUI.
   When you type a query there is an explain button on the top and it will pop up the explain graph
3. db2exfmt
   This is the preffered tool by DB2 Optimizer developers (when you open a PMR they love to see the result in this format.
   To get it first you need to ensure you have the supporting "explain tables" defiened:
    cd <...>/sqllib/misc
   db2 connect to <db>
   db2 -fvf EXPLAIN.DDL
   You do this once. Visual explain does it for you implicitly.
   db2 "explain plan for select * from sysibm.sysdummy1"
  Should return something like: "The query was not executed but only compiled...."  
   What has happened is that the optimizer has collected a lot of information and stored it in the tables defined
   Now it's time to fromat the output.
   Typically you just type:
   db2exfmt -d <db> -o <filename>.exfmt -1
   (The -1 (number one) says: Don't ask me any more questions, just pick up the last plan stored)
   You can now look at the plan in your favorite editor.
   It will show some basic information first (system config).
   Then it shows the SQL you typed and the SQL DB2 tuned your query into after rewriting it.
   The main attraction is the pretty-printed graph followed by details on teh so called LOLEPOP (low level plan operator).
Here is a link into the docs:

Ok, GnarOlak
You seems very interested in DB2,
This is a gift from me, an ebook of DB2, to GnarOlak and to any one who work with DB2 ^_^

I am not focus on DB2 but still spend free time to help you more GnarOlak.
This site is to help you understand more on DB2, DB2 administration
GnarOlakAuthor Commented:

I tried your suggestion but when I ran the db2exfmt command I got a fairly lengthy error message that included this:

Error Message =
SQL0035N  The file "db2exfmt.msg" cannot be opened.

I looked in the ...sqllib/misc directory and the file is not there.  I also searched the entire db2 installation and that file is not there either.  Is the installation incomplete?
Hmm, since db2exfmt is a tool perhaps you don't have the right client installed (?).
Can you try this from a shell running on the server?

GnarOlakAuthor Commented:

I did run that command on the server where db2 is running.
That is.. bizarre
GnarOlakAuthor Commented:
Thank you all very much.  I will continue to explore the information you have given me.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.