IBM Support

IC71860: INCORRECT BEHAVIOUR WHEN UPDATING TABLE CARDINALITY STATISTICS TO 0 (ZERO).

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • When updating table cardinality statistics to 0 (zero), the
    update is not applied correctly to the table's metadata.  For
    example:
    
    UPDATE SYSSTAT.TABLES SET CARD=0, NPAGES=1, FPAGES=1,
    OVERFLOW=0, ACTIVE_BLOCKS=0 WHERE TABNAME = 'T1 AND TABSCHEMA =
    'S1'
    
    DB2 returns successful completion for the update and querying
    CARD for S1.T1 from catalog table SYSCAT.TABLES will show 0.
    However, the table cardinality in the metadata for table S1.T1
    is not correctly updated to 0.  The incorrect cardinality can
    be observed in db2cat output and/or in explain output.
    
    The incorrect update can also be observed when applying db2look
    update statistics statements generated with the -m and -r
    options.  For source tables which are statistically empty and
    which have at least one index defined, the db2look mimic output
    will include an update index statistics statement setting
    INDCARD=0 following the update table statistics statement
    setting CARD=0.  When the statements are run, the update table
    statistics statement will return success, but the table's
    metadata is not updated.  If the exisiting table's metadata does
    not already have CARD=0, the subsequent update index statistics
    statement will fail with SQL1227N reason code 8 and indication
    that setting INDCARD is 0 is invalid or inconsistent with
    another statistics.
    
    After applying the APAR fix, updating table CARD to 0 will
    function correctly for tables with consistent metadata.  For
    tables with metadata that was made inconsistent due to
    experiencing this problem, you can fix the inconsistency by
    first updating the table CARD to a non-zero value and then
    re-updating the table CARD to zero.
    

Local fix

  • If the table has zero rows, runstats on the table will set the
    table CARD to zero correctly in the metadata.  In the db2look
    scenario, using -m without -r will cause db2look to generate a
    runstats statement prior to the update statistics statements.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of version 9.7 on Linux, Unix and Windows          *
    * platforms.                                                   *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * When updating table cardinality statistics to 0 (zero), the  *
    * update is not applied correctly to the table's metadata.     *
    * For                                                          *
    * example:                                                     *
    *                                                              *
    * UPDATE SYSSTAT.TABLES SET CARD=0, NPAGES=1,                  *
    * FPAGES=1,OVERFLOW=0, ACTIVE_BLOCKS=0 WHERE TABNAME = 'T1 AND *
    * TABSCHEMA = 'S1'                                             *
    *                                                              *
    * DB2 returns successful completion for the update and         *
    * querying CARD for S1.T1 from catalog table SYSCAT.TABLES     *
    * will show 0. However, the table cardinality in the metadata  *
    * for table S1.T1                                              *
    * is not correctly updated to 0.  The incorrect cardinality    *
    * can be observed in db2cat output and/or in explain output.   *
    *                                                              *
    * The incorrect update can also be observed when applying      *
    * db2look update statistics statements generated with the -m   *
    * and -r options.  For source tables which are statistically   *
    * empty and                                                    *
    * which have at least one index defined, the db2look mimic     *
    * output will include an update index statistics statement     *
    * setting INDCARD=0 following the update table statistics      *
    * statement setting CARD=0.  When the statements are run, the  *
    * update table statistics statement will return success, but   *
    * the table's metadata is not updated.  If the exisiting       *
    * table's metadata does not already have CARD=0, the           *
    * subsequent update index statistics statement will fail with  *
    * SQL1227N reason code 8 and indication that setting INDCARD   *
    * is 0 is invalid or inconsistent with                         *
    * another statistics.                                          *
    *                                                              *
    * After applying the APAR fix, updating table CARD to 0 will   *
    * function correctly for tables with consistent metadata.  For *
    * tables with metadata that was made inconsistent due to       *
    * experiencing this problem, you can fix the inconsistency by  *
    * first updating the table CARD to a non-zero value and then   *
    * re-updating the table CARD to zero.                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to DB2 Version 9.7 Fix Pack 4.                       *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 Fix Pack 4.
    

Temporary fix

  • If the table has zero rows, runstats on the table will set the
    
    
    table CARD to zero correctly in the metadata.  In the db2look
    scenario, using -m without -r will cause db2look to generate a
    
    
    runstats statement prior to the update statistics statements.
    

Comments

APAR Information

  • APAR number

    IC71860

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-10-13

  • Closed date

    2011-05-09

  • Last modified date

    2011-05-09

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC71887 IC71925 IC71934

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC71860

Modified date: 09 May 2011