Results 1 to 13 of 13
  1. #1
    Join Date
    Jun 2008
    Posts
    16

    Unanswered: Runstats eating up money

    Hello,

    In my shop, it has been identified that every time we run runstats, it costs us a fortune and found out a workaround for this.

    Run runstats so that it does not update all the catalog tables, but updates only those that are needed for running runstats.

    My question is, is it okay to go ahead with this idea? If so, when are we going to update the tables that are not needed by runstats.

    I mean, I just want a few ideas/points to negate this concept in my shop.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Could you be more specific? What do you mean by "updates only those that are needed for running runstats."

    Once you run runstats, and the data does not significantly change, it is not really necessary to keep re-running it unless there is a schema change (including index change) on a particular table, and then it only needs to be rerun on that table.

    Runstats are typically either not run oftern enough, or run too often. It is almost always better to run it too often than not enough, so that it is why it recommended to run it often.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2008
    Posts
    16
    I meant, 'updates only those catalog tables that are needed for runstats'. My boss, says/feels by running Runstats which updates all the catalog tables (assuming that there are enough changes) it is too dear. Workaround - run Runstats to update only those catalog tables that are needed for Runstats. Hope it gives you better understanding of my problem. Should there be more confusion, please let me know - I shall respond with the precise command for running Runstats.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    No, that is not percise enough. I don't know what you are talking about.

    The following is what I would recommend on DB2 for LUW runstats in 99% of the cases:

    runstats on table <table-name> with distribution on key columns and indexes all;

    Like I said, the other option is to not run so often. But since you are concerned about "cost" I suspect that you may be on the mainframe?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    I think he is reffering to updating syscat tables/views with the new updated statistics. If that is it then you have no choice in the matter.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by Cougar8000
    I think he is reffering to updating syscat tables/views with the new updated statistics. If that is it then you have no choice in the matter.
    That is what he said, but that makes no sense since the update part is trivial. The hard part is gathering the stats by reading all the data and all the indexes, and calculating the stats (like distribution statistics).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    True, but that is what he is asking unless he means something else.

    I am not a fan of running stats just because. we have developed a quick and dirty script that read npage and fpages and if there is a more then 10% diff in there then we do stats. Other wise leave it alone.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by deadmanthedba
    Run runstats so that it does not update all the catalog tables, but updates only those that are needed for running runstats.
    Do you mean "Run runstats so that it does not update all the catalog tables, but updates only those that are needed for the optimizer"?
    That's possible by adding the "UPDATE ACCESSPATH" option to the RUNSTATS statement. In that case, space-related statistics are not written to the catalog.

    Probably this won't help much; no idea. Could save you 10% or so?

    Anyhow, as Marcus_A suggests, you could save more money by executing RUNSTATS only on those tablespaces that have considerably changed since the last RUNSTATS run. How to know this? Compare runstats info with last time's ;-)
    Well, that is: if you kept RUNSTATS history over (say) the last year, analyse it to get a good idea of which tables change and how often. Extrapolate this to the coming year and you've got a good RUNSTATS periodicity plan.
    You could also use real time statistics to get an idea of which tables change, and how often.
    --_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
    Jan 2008
    Posts
    96
    Hi Peter,

    You could also use real time statistics to get an idea of which tables change, and how often
    Just curious to find out about Real Time Statistics. Is that Mainframe thing? Is Real Time Statistics similar to Auotmatic Statistics Collection in DB2 for LUW?

    Thanks

    Ran

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by rangupt
    Is that Mainframe thing?
    Yes. I've still to start experimenting with it, so I don't know any details.
    The basic idea is that, after having setup some catalog tables, DB2 starts collecting per-tablespace and/or per-table insert/update/delete events, which can then be collected in those tables. This collecting "change statistics" comes with almost no overhead, since DB2 has to write log entries anyhow for any insert/update/delete.

    (Others may correct or comment on this...)
    --_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
    Jun 2008
    Posts
    16
    Quote Originally Posted by Marcus_A
    Like I said, the other option is to not run so often. But since you are concerned about "cost" I suspect that you may be on the mainframe?
    My aplogies! I was away for a couple of days. Getting back to the topic... it just slipped my mind to mention env details.

    DB2 V8 z/OS

    To be more precise... the command I used all my life as a DBA to run runstats is UPDATE ALL. However, like I said my boss suggested to use UPDATE ACCESSPATH. The latter, updates only the required catalog tables that are needed for accesspath by optimizer. Per him, this costs less. He could be true. My initial question is, should I continue to used UPDATE ACCESSPATH instead of UPDATE ALL, when will the remaining tables be updated (tables/cols that are not needed by optimizer for accesspath). To me, answer is NEVER. Then, why do we have those in the first place... Is there any other utility that needs the remaining columns for whatsoever reason?

    If I do not make sense, I authorize you to hit me with a wet noodle.

  12. #12
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by deadmanthedba
    Should I continue to used UPDATE ACCESSPATH instead of UPDATE ALL
    Yes.
    Quote Originally Posted by deadmanthedba
    When will the remaining tables be updated (tables/cols that are not needed by optimizer for accesspath). To me, answer is NEVER.
    Indeed.
    Quote Originally Posted by deadmanthedba
    Then, why do we have those in the first place... Is there any other utility that needs the remaining columns for whatsoever reason?
    They are collecting disk usage. Useful e.g. to know when disks are almost full, when extents have almost been used up, etc. With DB2 v8, and if using default PRIQTY & SECQTY, the latter is no longer a problem, and the "disk full" problems can be monitored outside DB2.
    --_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 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by Marcus_A
    The following is what I would recommend on DB2 for LUW runstats in 99% of the cases:

    runstats on table <table-name> with distribution on key columns and indexes all;
    Marcus,

    This is the REORG statement we use:
    runstats on table <table-name> with distribution and detailed indexes all allow write access;
    How does this compare to your REORG statement?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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