Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2006
    Posts
    14

    Unanswered: Access Path changes for dynamic SQL after Migration to version 8 on mainframe

    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?

  2. #2
    Join Date
    May 2003
    Posts
    113
    Quote Originally Posted by bigal21
    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

  3. #3
    Join Date
    Mar 2006
    Posts
    14

    thanks for the quick response

    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.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Mar 2006
    Posts
    14
    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.

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Hmmm. PeopleSoft is owned by Oracle now.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •