DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

**************************************************************** 
* 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.                       * 
****************************************************************

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.

DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
 

Problem was first fixed in Version 9.7 Fix Pack 4.

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.

forerunner  : APAR is sysrouted TO one or more of the following: IC71887 IC71925 IC71934 
follow-up : 

Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

13.10.2010
09.05.2011
09.05.2011

9.7.FP4