Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399

    Unanswered: RUNSTATS on empty table

    does it do any damage to do RUNSTATS on empty tables? My boss says yes, but I could not find anything in the db2 documentation to confirm this.

    What if deleted many records from the tables - should not we do RUNSTATS to collect stats?

    It would not take a lot of time to run REORG and RUNSTATS on all tables and indexes in our db, so if I do it for all tables/indexes in certain db straightforward (without reorgchk) would it do any damage?

    Thanks
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by MarkhamDBA
    does it do any damage to do RUNSTATS on empty tables?
    Certainly not. On the contrary: if the table stays empty for the coming weeks, it's better that the optimizer knows about the fact.
    On the other hand, if the table will get repopulated soon, it's probably better to postpone the runstats until after that.

    REORG on an empty table will probably return a warning. (At least, it does on z/OS.) But again, no harm.
    --_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
    Dec 2008
    Location
    Toronto, Canada
    Posts
    399
    thanks, Peter
    DB2 9.5/9.7 on Unix/AIX 6.1/Linux

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you delete all the rows in a table and don't do a runstats, the worst that will happen is that DB2 will try to access the data via an index instead of a table scan. But since there won't be any index rows (since there are no data rows), that will not take any longer than a table scan.

    The worst than can happen if you do a runstats when he table is empty, and then you add a lot of rows before a new runstats can be executed, is that DB2 will do a table scan instead of index access (which could be very bad performance).

    So I would suggest that it “may” not a good idea to do runstats on an empty table (unless you are sure that runstats will be performed as soon as a lot of new rows will be added).

    If a table frequently oscillates between zero (or a few) rows and a lot of rows, then altering a table to “volatile” may be a good idea (which forces DB2 to use default statistics as if the stats were set -1).

    You will not find these conclusions in the documentation. You must understand how DB2 works and use your brains.
    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
  •