Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2006
    Posts
    8

    Question Unanswered: Performance problems after REORG

    The brief explanation of the problem is,

    After DB2 v7 maintenance window, performance has gone down, it takes some hours to retrieve the data.

    We have provided some table spaces to Reorg/Runstat during the maintenance window .

    After REORG some of the tablespaces, the performance is very slow .This is really strange because no code changes has been involved and also nothing was changed, it's just a Reorg of some tablespaces.

    I guess just a assumption, this problem might be with the Index space cluster ratio, because some of the tables cluster ratio is very low and also it is accessing the wrong cluster indexes.

    Normally the performance should improve after REORG.

    But Please let me know your valuable opinions in this regard.

    Regards,
    karthik

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Typically you will need to perform a RUNSTATS (followed by REBIND of your static SQL applications) otherwise the old statistics (especially cluster ratio values) are still being used for access path selections.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Jun 2006
    Posts
    8

    Thumbs up

    Thanks for your reply. Yes we have done the runstat also, but the same result.

    Our next assumption is, may be some dependencies of the packages that has to be rebind. is that correct?

    Because we have given some 10 tablespaces(i.e around 10 tables) for the REORG/Runstat, i guess wherever this tables are used in the application programs, that programs/packages needs to be rebind?

    Please let me know your valuable suggestions.

    Best Regards,
    karthik

  4. #4
    Join Date
    Jun 2006
    Posts
    3

    clustering chage?

    Do you have the stats from before the reorg? If so, check to see if the reorg was done with the wrong index specified as the clustering index. This can greatly affect performance.

  5. #5
    Join Date
    Jun 2006
    Posts
    8
    Thanks for your response. Runstats runs every week. After REORG, we have done the RUNSTAT/REBIND, but we doubt, is this problem with the rebind?

    We wanted to know all dependecies needs to be rebind.

    Best Regards,
    karthik

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Yes, indeed: all packages that use one of the tables being REORGed, need to be REBOUND after the RUNSTATS.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jun 2006
    Posts
    8
    Quote Originally Posted by Peter.Vanroose
    Yes, indeed: all packages that use one of the tables being REORGed, need to be REBOUND after the RUNSTATS.

    Hello Peter vanrose,

    Thanks a lot for your response. Yes you are correct, but our case, what ever the tables we have reorged has involved in more thatn 100 application programs. is that we need to rebind all of them and more over reorg took place 20 days back, can we rebind now?

    Regards,
    karthik

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    can we rebind now?
    Sure. Even with 100 apps, just execute REBIND on all plans and packages that use the tables involved.
    Consult the SYSPLANDEP and SYSPACKDEP tables to find out about those dependencies: e.g.:
    SELECT DISTINCT DNAME FROM SYSIBM.SYSPLANDEP WHERE BTYPE='T' AND (BCREATOR,BNAME) IN (your tables)
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  9. #9
    Join Date
    Jun 2006
    Posts
    8
    Quote Originally Posted by Peter.Vanroose
    Sure. Even with 100 apps, just execute REBIND on all plans and packages that use the tables involved.
    Consult the SYSPLANDEP and SYSPACKDEP tables to find out about those dependencies: e.g.:
    SELECT DISTINCT DNAME FROM SYSIBM.SYSPLANDEP WHERE BTYPE='T' AND (BCREATOR,BNAME) IN (your tables)

    Hello,

    Thanks a lot for your response. Like you said we are rebinding all the dependencies, but our case so many dependencies.

    I have one question. like you explained above, Can we rebind plan as well? Because why i am asking this question is our plan it was created on 1996 after that they didn't do rebind, that's why i am afraid to do rebind.

    Please confirm about the rebind plan.

    Regards,
    karthik

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    There is no difference (in terms of performance etc.) between rebinding plans or packages.
    In summary, you typically need to perform the "three R" on a regular basis:
    REORG, then RUNSTATS, then REBIND.
    This will (roughly) restore performance to the level of the previous R+R+R; inbetween performance is gradually decreasing (at least, for not-readonly data).
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  11. #11
    Join Date
    Oct 2004
    Location
    Norway
    Posts
    53
    Rebinding packages.

    Is this documented somewhere ?

    As far as I know, we've never rebound any package, unless we update something. That is, the programs from the DB2 Connect, all statements are dynamic in our environment, hence the unique statement itself is rebound everytime it's flushed from the system.

    We also have hundreds og small Cobol programs and procedures, however I'm not in charge of those, so I'm unsure whether they rebind those after a reorg or not.

    ---

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Filip Poverud
    As far as I know, we've never rebound any package, unless we update something.
    Actually, in that case you must BIND, not REBIND.
    A rebind can / should be done each time something changes at the DB2 side only, like new indexes or changed stats, so that the optimal access path may change. So essentially, REBIND will just re-run the optimizer and nothing else (i.e., it's a DB2-side-only process, the SQL is not inspected and should thus not be changed.)
    So a REBIND could be (and often is) done automatically, without problems (but with a performance impact which in rare cases could be negative...)
    Last edited by Peter.Vanroose; 08-03-06 at 10:10.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  13. #13
    Join Date
    Jan 2003
    Posts
    1,605
    Quote Originally Posted by Filip Poverud
    As far as I know, we've never rebound any package, unless we update something. That is, the programs from the DB2 Connect, all statements are dynamic in our environment, hence the unique statement itself is rebound everytime it's flushed from the system.
    ---
    Hi,
    don't mess static and dynamic statements. Static statements needs packages, dynamic doesn't. Dynamic statements creates "access path" (package) each time SQL statement is executed.
    Hope this helps,
    Grofaty

Posting Permissions

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