db2exfmt

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...

db2exfmt

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:

Prerequisite
-- 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
* MERGE
* REFRESH
* SELECT
* SELECT INTO
* SET INTEGRITY
* UPDATE
* VALUES
* VALUES INTO

Example:
>>> EXPLAIN ALL FOR SELECT Col1 FROM Test_Tab

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...