Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Aug 2003
    Posts
    106

    Unanswered: runstats without reorg

    I am using LUW 9.1 and have the following question.

    What are the disadvantages (if any) if one runs runstats without reorg after considerable inserts/updates/deletes?

    You are the creator of your own destiny!

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you don't have many delete/update operations that would cause fragmentation on page level, you may not need a REORG. Have a look at the REORGCHK utility to see which rules are applied there.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2003
    Posts
    106
    Quote Originally Posted by stolze
    If you don't have many delete/update operations that would cause fragmentation on page level, you may not need a REORG. Have a look at the REORGCHK utility to see which rules are applied there.
    OK.

    What if REORGCHK utility suggest that the table is to be REORGed but we go ahead and do RUNSTATS?

    What are the implications (doing constant RUNSTATS eventhough the REORGCHK suggests that the table has to be REORGed)?
    Last edited by db2guru1; 04-04-08 at 10:19.

    You are the creator of your own destiny!

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I really don't understand your question. RUNSTATS and REORG are two utilities for two completely different tasks. One does not and cannot replace the other. RUNSTATS collects statistics, the latter modifies the organization of the data on the data pages. Of course, the statistics depend on the current organization.

    Since REORGCHK also does a RUNSTATS under the covers, the net result of not doing a REORG but a RUNSTATS instead would be pretty much zero, zip, nada.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Aug 2003
    Posts
    106
    I did not articulate my question properly.

    I meant to say REORGCHK CURRENT STATISTICS (which does not do RUNSTATS under the cover)....

    Let me formulate my question in another way....

    "It is my understanding that if you do RUNSTATS on a table that is highly disorganized and fragmanted, db2 sometimes will not use the indexes"

    Is the above statement correct?

    You are the creator of your own destiny!

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you don't have current statistics, DB2 may not choose the optimal access plan. Whether index access is the best access or not is a different question to be decided on a query-by-query basis.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  7. #7
    Join Date
    Aug 2003
    Posts
    106
    That is EXACTLY what I want to hear.

    Thanks much for your reply.

    You are the creator of your own destiny!

  8. #8
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If you run reorgcheck using current stats, it might not produce corret results for the reorg. If you have done many updates your best bet is to run with UPDATE Statistics to make sure that reorgcheck utility has the right information to go on.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    If you run reorgcheck using current stats, it might not produce corret results for the reorg. If you have done many updates your best bet is to run with UPDATE Statistics to make sure that reorgcheck utility has the right information to go on.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need to

    1. REORG
    2. RUNSTATS
    3. BIND/REBIND
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Brett, what good is a reorg, wiht out current stats if you have been doing major data movement?

    Edit: do you mean an actual reorg or just a check?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by db2guru1
    What are the disadvantages (if any) if one runs runstats without reorg after considerable inserts/updates/deletes?
    Let's say that the *advantage* of doing runstats (without reorg) as compared with none of the two, that the subsequent access paths will be more adapted to the current (disorganized) state of the tablespace, i.e., more performant.
    --_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
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by Cougar8000
    Brett, what good is a reorg, wiht out current stats if you have been doing major data movement?

    Edit: do you mean an actual reorg or just a check?
    Huh?

    Don't you want the stats AFTER the Reorg?

    And then REBIND the sprocs so they can use the new stats?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    are you doing an actual reorg or reorgcheck? If you are doing an actual reorg then you are correct, you want to follow up with stats. However, if you are doing a reorgcheck, then stats have to be done first(i.e. part of the reorgcheck utility).
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  15. #15
    Join Date
    Aug 2003
    Posts
    106
    I am fully aware that you need to reorg, runstat, rebind etc....

    I think you are missing my point.

    My question is very simple....

    "What happens if I repeatedly do RUNSTATS (without first doing reorg) - assume that the table warrants reorg"

    You are the creator of your own destiny!

Posting Permissions

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