Good afternoon,

We are in the process of migrating from DB2 version 7 to version 8 (mainframe). Unfortunately, some of our applications (which uses dynamic SQL) is getting a different (and apparently slow) access path on version 8. Some that were running in about 1 minutes are now running well over 10, and some as long as 30. Since this is dynamic SQL, we cannot avoid this by simply NOT rebinding after migration. We have tried the Statitistics Analyzer, and while that has improved response times for one or two queries, it has apparently hurt others.

(this is a TERRIBLE question but) has anyone come across a similar situation? If so, what did you do to resolve it?

DB2/zos v8 has quite a big change in optimization area. Usually, the feedback is positive. :-)

anyway, the first thing you can try is to make sure your statistics are up to date. If still can't resolve the issue, you should open PMR to IBM

thanks, we have done both. We have run statistics, opened a PMR, run the statistics specified by IBM technicians, and run the statistics run by the Visual Explain tool. They have been fruitless to date. The IBM technician is still working on it. She has been helpful but we have not yielded the positive results we need.

You can raise the severity of the PMR depending on how urgent this issue is for you and how much it impacts your business. With a higher severity, you will get more attention, of course.

I hesitate to throw stones without seeing the details (not provided), but the DB2 optimizer is most likely to be fooled when your tables are over-indexed and/or your queries are horribly written. But there are exceptions.

the reports are from a 3rd party vendor (PeopleSoft), and function just fine under version 7. They may be poorly written, but there is nothing we can do about that (at least not in the short term). As for raising the priority on the PMR, it has been a high priority PMR all along, and IBM has been responsive, but they have not yet been able to find a way around this.

I was hoping someone else would have experienced the same problem and could offer some hints on how to resolve this.

Hmmm. PeopleSoft is owned by Oracle now.