MVSFORUMS.com

[analyst]

" There is an existing SQL but of late suddenly, it is taking too much time,without changing any thing in that.What will be the causes and how you go about increasing the performance of that?"

I came across thru the above question in an interview,I replied him I will do RUNSTATS to get the latest statistics and check any index is dropped on the table etc..But he was not interested in those answers.

Could anybody please let me know,how you go about this when you face the above problem and suggest few things.Thanks for your replies in advance.

[dbzTHEdinosauer]

Start with an EXPLAIN. Especially before RUNSTATS. You need a benchmark.
_________________
Dick Brenholtz
American living in Varel, Germany

[jsharon1248]

That open ended question only leads to more questions as there is not enough information to make an informed judgement. How sudden was the change? How drastic was the change? How often does the SQL run? Is the problem SQL updating or only selecting? If the SQL is updating how often does it commit? Has the data volume changed? Have a lot of rows been deleted? How current are the stats? Are there any new triggers? When was the last REORG? Is the SQL consistently bad, or only for certain host variables/parameter markers? Is there a noticeable slow-down of other processes or is it isolated this one? Any recent DB config changes?

REORGs with RUNSTATS might resolve the issue, but the first step to resolve would probably be an EXPLAIN to determine the access path. Compare that to what you think it should be. Eliminate potential causes until you find the most likely and then make a recommendation.

[videlord]

1. check the current table status, e.g. REORG needed? RUNSTATS needed?
2. Monitor the SQL running, e.g. check DB2PE report, I/O problem? lock contention? system issue?

[analyst]

Thanks to you all for your valuable responses.I will start answering with EXPLAIN and proceed with REORG,RUNSTATS,monitoring etc..