Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2010
    Posts
    123

    Unanswered: db2 statistics problem

    db2 "runstats on table db2inst1.books"
    SQL2314W Some statistics are in an inconsistent state. The newly collected
    "TABLE" statistics are inconsistent with the existing "INDEX" statistics.
    SQLSTATE=01650



    can anybody explain the procedure, by which i can get my table into consistent state.

  2. #2
    Join Date
    Oct 2007
    Posts
    246
    its a warning message
    SQL2314W Some statistics are in an inconsistent state. The newly
    collected "<object1>" statistics are inconsistent with the
    existing "<object2>" statistics.

    Explanation:

    Possible causes of this warning include the following.

    1. Issuing RUNSTATS on the table only may result in a situation where
    the table level statistics are inconsistent with the already existing
    index level statistics. Likewise, issuing RUNSTATS for indexes only
    or during index creation may leave table level statistics in an
    inconsistent state. For example, if index level statistics are
    collected on a particular table and later a significant number of
    rows is deleted from this table, issuing RUNSTATS on the table only
    may end up with the table cardinality less than FIRSTKEYCARD which is
    an inconsistent state.
    2. If a RUNSTATS is issued with the ALLOW WRITE ACCESS option when many
    inserts, updates, or deletes are occurring concurrently, then
    inconsistencies may occur because of table changes between the time
    that table and index statistics are collected.
    3. If a RUNSTATS is issued with the TABLESAMPLE option, then an
    excessively low sample size may cause statistics to be inaccurately
    extrapolated, resulting in inconsistencies. The chance of inaccurate
    extrapolation is higher for SYSTEM sampling than for BERNOULLI
    sampling.

    User response:

    1. Issue a RUNSTATS to collect both table level and index level
    statistics.
    2. Collect statistics when inserts, updates, and deletes are minimal or
    not occurring at all. Or, if concurrent inserts, updates, and deletes
    are not essential, issue a RUNSTATS with the ALLOW READ ACCESS
    option.
    3. Increase the sample size, or, if RUNSTATS was issued with TABLESAMPLE
    SYSTEM, use the TABLESAMPLE BERNOULLI option instead.

    sqlcode: 2314

    sqlstate: 01650

    regds
    Paul

  3. #3
    Join Date
    Jan 2010
    Posts
    335
    For those who can't use the search-function:
    http://www.dbforums.com/db2/1660651-...s-warning.html

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I would suggest these options for any runstats:

    db2 "runstats on table db2inst1.books with distribution on key columns and detailed indexes all"

    The error above is a warning that you can ignore.
    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
    Dec 2010
    Posts
    123
    thanks so much marcus

  6. #6
    Join Date
    Dec 2010
    Posts
    123
    thanks so much mathew

Posting Permissions

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