Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2012
    Posts
    82

    Unanswered: DB2 RUNSTATS Reorg not collecting Index ClusterRatio

    Hi,
    We are running DB2 9.7.3 on zLinux Server.

    The issue is I am doing runstats and reorgs weekly on the tables and indexes but when I go check the clusterratio in the catalog tables for indexes it shows -1. I have searched but can't find a solution

    Also this is my reorg runstats statergy. Please let me know if it is ideal or can be improved. THe Database is a warehousing with a BI reporting front end .


    runstats on table <table_name> with distribution on key columns and detailed indexes all ;

    reorg table <table_name>;

    reorg indexes all for table <table_name>;

    runstats on table <table_name> with distribution on key columns and detailed indexes all ;


    After all tables have been completed.

    FLUSH PACKAGE CACHE DYNAMIC

    db2rbind <dbname> all


    So any ideas for improving the stats gathering process ?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Why do you do RUNSTATS just before the REORG? Why do you think you need to do the REORG weekly?

    If you took time to read the manual, you'd learn that "[the CLUSTERRATIO value is] -1 if statistics are not collected or if detailed index statistics are collected (in which case, CLUSTERFACTOR will be used instead)."
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jan 2012
    Posts
    82
    Hi,
    I read somewhere that it is ideal to do runstats before reorging as well, that why it became part of the process.


    Also we run Reorg weekly because the warehousing does alot of delete and insertions. So that is why we do it weekly to avoid any issues.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by junaid377 View Post
    Hi,
    I read somewhere that it is ideal to do runstats before reorging as well, that why it became part of the process.
    I have no idea what the purpose of that is, unless you are saving the stats and will compare them with new ones.

    When you do a classic reorg (offline, not using INPLACE keyword) all the indexes are automatically reorged, so no reason to reorg them again.

    The db2rbind after runstats is a good idea, but in an OLTP environment you should bind each package individually (based on contents of syscat.packages or whatever) so you don't run into lock contention during the rebind. In that case, you can skip the packages with SYS* schema name.
    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
  •