Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2005
    Posts
    103

    Unanswered: performance issue

    AIX 5.3
    DB2 9.5 2a

    Recently installed 9.5 2a and migrated instance and database from 8.2 FP15.
    We have just discovered that a program known to complete within 15 minutes now takes just over an hour.

    I am looking for brainstorm-suggestions to follow, even if they would match what has already been attempted to some degree.

    Thanks in advance.

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Did you try updating statistics on tables/indexes?

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    And then rebinding everything?

    Andy

  4. #4
    Join Date
    Jul 2005
    Posts
    103
    I have reorg'ed tables, rebuilt indexes, and ran statistics.

  5. #5
    Join Date
    Jul 2005
    Posts
    103
    In addition to this, I noticed in syscat.tables that the INVALIDATE_TIME field has a value near the time of the migration for most, if not all of the tables in the database.

    Should I be concerned about this?

    As far as I know, the databse is behaving normally, except for the now slower program execution.

  6. #6
    Join Date
    Jul 2005
    Posts
    103
    Oh, I did rebind all as well.

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    What does your program do? You need to find out which statement(s) is taking longer, maybe the access plan has changed after migration.

  8. #8
    Join Date
    Jul 2005
    Posts
    103
    The program has been re-compiled and all packages have been rebound.

    The program is a cobol program that does some thinking, grabs a record set, does more thinking, grabs another record set, and so on it goes. Interlaced in the thinking it may make updates based on its thinking.

    I have tried to capture SQL statements with occasional snapshots, but I guess I am not taking the picture when it grabs the goods. I believe the program builds SQL statements as needed as determined in its thinking.

    I have thought about trying to do a trace on the user connection, but my understanding is that may be difficult to actually use that.

    Thanks for the ideas thus far.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    Try capturing them using statement event monitor. If you know the appl/auth id or appl name, you can specify it when creating the monitor.

    Do you see any errors/warnings in the db2diag.log (ie. memory/locking/etc...) while the program is executing?

  10. #10
    Join Date
    Nov 2009
    Posts
    5
    Try re-booting the server. But do not do immediate restart.

    Instead ...

    1) shutdown
    2) disconnect power
    3) disconnect UPS
    4) make sure all lights are out on the front and back of the server
    5) make sure none of the hard disks are still spinning (listen intently)
    6) make sure none of the blower fans are running (wet your finger and hold next to the fan)
    7) re-connect power
    8) IPL the server

    the procedure above will be just as helpful as reorg, runstats, rebind ....

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,369
    I now know what to suggest if you ever report a problem...

  12. #12
    Join Date
    Nov 2009
    Posts
    5
    it seems you got your chance .... i will go and reboot now ...

  13. #13
    Join Date
    Jul 2005
    Posts
    103
    It would seem I have wet my finger suffiently well. The jolt has sparked an idea that seems to have resolved our problem. (It was an index issue.)

    Thanks to all for your time.

Posting Permissions

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