About Me

My photo
Singapore, Singapore, Singapore
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Connect to me

See my profile on LinkedIn Visit my Ashnik Website


So in last episode (#45) of db2 night show, people presented db2look, db2move, db2this, db2that (sorry Scott to have stolen your words)... So i continue my thread of my pseudo-presentation... But again i emphasize that do view the actual vedio... now final no 3 vdo has come... It has some really kwel tips... tomorrow is final no 4 (i know I am lagging behind :))... watch it live or catch up with the replay latter... But do watch n vote for whom u find the best...

Well getting back to my "would have been" presentation...


Well lot many times a query will be slapped back on your face telling you that it is non-performant... its taking too much of CPU or may be I/O or may be sorting (see previous post to know how u can list such queries)...
Well in such cases you always wonder what is it that is causing the problem... Which part of the query is causing the issue!!! :-o
More than that generally what is reported is an part of application or a module which has more response time...

So how do we proceed...
Here is a db2something to rescue you... db2exfmt

This tool will help you format the content of db2 EXPLAIN tables. AIX users can locate this in DB2_HOME/sqllib/misc

Let's see how you can use this tool, we will discuss two scenarios, one where we know the bad performing query and other where we know a part of application which has perormance issues:

-- You need to create EXPLAIN tables using EXPLAIN.DDL in the above mentioned directory

For first scenario:
-- Use the EXPLAIN statement to generate explain information for a query

Following are the valid db2 queries/commands which can be used in explain statement:
* CALL, Compound SQL (Dynamic), DELETE, INSERT


In explain statement use WITH SNAPSHOT to ensure that snapshot is also recorded with the explain info...

This step will capture the explain information in the explain tables.

-- In case you of scenario 2, that is you want to see performance hotspots in your application... Use SET EXPLAIN SNAPSHOT and EXPLAIN MODE to set /EXPLAINregistry/ /EXPLAIN SNAPSHOT This will enable capturing snapshot/explain info for all the queries which will be fired by application in that session...

-- Once you have the EXPLAIN info captured use db2exfmt to format the explain data

db2exfmt can be either invoked with arguments or can be invoked in interactive mode.

Now lets explore what the various info you can get from the explain tables using exfmt:

>> You can use -g option to generate graph. There are vaiour sub-options to include more detailed info in the graph:
-gT will include cost for each operation in graph
-gI will include I/O for every operation in graph
-gF will include first tuple cost
-gC will include the expected cardinality
One can combine any of the above options e.g. -gCI shall give cardinality and I/O cost for every operation in graph

You can either choose to format the latest explain info or can -w timestamp for explaining a specific timestmp's explain info...

Or you can specify SOURCE_SCHEMA/SOURCE NAME to narrow down you search for hotspots... using -s or -n

-o / -t will let you re-direct the output to an output file or to your terminal.

Well now it is worth mentioning about other tools in db2 available for similar usage

>> with db2expln you can get the explain plan in form of a graph with less hassles, but you need to use it for individual queries. It is much better when you have few queries and when you exactly know the query, else
it can not be used to capture the explain info of all queries being fired on db from application

>> with db2caem (db2 create activity event monitor) tool you can do all these stuff with much more ease... But you need to be on db2 9.7 fix pack 3... Trust me this tool is the best you would have ever used for getting explain plan... An do watch the replay of episode #45, where some one has explain this tool quite well...

Well next time I will be back with a success story... Probably I will share how we monitored and got rid of high I/O contention and memory usage issues...


  1. sorry guys... forgot to mention that, the images were not actually taken by me, i just googled an example and pasted...

  2. any good article on how to decipher the explain plan step by step?

  3. db2exfmt generates a graph more or less similar to one generted by db2expln... You can look for link posted in this blog for "final no 3 vdo" of db2night show's db2 has talent... There was a ppt which explained it quite well... Just have a look... Anyways you have given a nice topic for another blog...

    1. Hi sameer !!
      Thanks for the superb post,, please do put up more n more !!

      i'm waiting for
      how to decipher the explain plan step by step?
      shall wait for the blog...

      Thanks and regards

    2. Hi Nagesh,

      Thanks for your comment. I have now started blogging on PostgreSQL database. Do visit my blogs on http://pgpen.blogspot.com