Hi All,
I have a list of SAP sql’s that I need to tune and when I run the db2advis nothings works on those sql’s as they are GUI based. I have run the db2exfmt for all these sql’s. I have the output also but I don’t know how to take things from there. Please if anyone can help me in this it will be a great help. I need to tune all these sql’s ASAP. Please help me.

DB2 Version - v9.1.0.5 fixpack 5

AIX - 5.3

Thanks

db2PRO! follow dummy's advice - DO NOT USE DB2ADVIS! EVER!
Start by reading up on DB2 access path selection. And, if that is your starting point, this is not going to happen "ASAP" - guaranteed!

Thanks dummy1...So, could you please suggest any good material for DB2 access path selection ..so that I can start reading it ..

ftp://ftp.software.ibm.com/ps/produc.../db2d3e951.pdf

Thanks a lot db2dummy1...very useful material

YW ... we aim to please!

Care to explain?

But of course!

db2advis will recommend indices to optimize those SQL statements presented to it as input. If you submit one SQL statement it will optimize indices for that one SQL - chances are you will get index only access everywhere for that one query. These indices may be absolutely useless for the rest of the workload. In order to have db2advis do proper job (and the jury is still out on how good a job it does), you have to submit the most representative samples of your SQL in one execution - many SQL statements. You have to know what they are or capture them as they are being run. I am yet to find someone who would do a good job at this. Most of the time people send one SQL statement to db2advis, which of course is a waste of time, as explained above, except for that one SQL. My recommendation is to do what db2advis attempts to do, but do it yourself. While you may not have all the SQL that will ever be run on your database, you understand the overall query and update patterns, and your experience and intuition will tell you what indices to define. Unfortunately "experience and intuition" are the two things that db2advis will never have. So, my vote is for basic human intelligence!

BTW, happy birthday to you, Dr!

Thank you, I'm enyoing my last working day as a 49-er.

I agree, but when only 1 query is choking you database, the 1st thing I do is to feed that query to db2advis. The output (what's in a name) is an advice. Sometimes I follow it up and sometimes not.
Another thing to unleash db2advis after a system-test and let it swallow the complete package cache! That gives nice indication.

So your advice NEVER to use db2advis... I say: never on your production machine (I saw it bringing down an instance once) but on your test-machine: yeah, why not?