DB2 Explain

Recently I got to work on yet another interesting thing I always wanted to try my hands on. DB2 performance tuning. Haven't really worked on ANY database performance related tools. Started off with Visual Explain which is available from 8.x. It basically uses a bunch of tables wherein it will dump the performance related data which can be viewed graphically or through command line. (I don't have any idea how command line tool works)

Looking at the graphical display, you will come to know how is your query doing, where is it making use of indexes or just doing blind table scan, where it is doing nested loop join/hybrid join, the no of CPU cycles, I/O utilization etc. Depending upon the plan, you can tweak your query to make maximum use of indexes, MQTs, or maybe create temporary tables to reduce the onus on joins.

I was using control center instead of DBArtisan as query plan was not enabled on that. And control center is painfully slow (I am not sure if it is always slow or it is "Morganised". May be designing UI in java was a really bad idea) . Plus, DB2 9.x is fussy about the machines too. You need a 64 bit linux box to run that shit. I am not saying DB2 is all crap. But I can't find a decent tool to access it man. DBArtisan is really great once the connection is made. But it can die almost instantly while opening/doing select * from the toolbar (Again not sure if Morganisation has made it that way)

Sometimes I just wonder, now that database is one universal thing which is integral part of any moderate size application, why can't there be a decent access tool available. I have heard microsoft SQL server is really good. But who uses it. And its microsoft, come on, how good it will be. There will of course shortcomings. The name says it all!!

Waise.. I managed to bring down the timerons of a killer query from 51000 to 70. How cool is that for performance tuning!