|
Problem IC71925
|
Status: Closed |
INCORRECT BEHAVIOUR WHEN UPDATING TABLE CARDINALITY STATISTICS TO 0 (ZERO). |
| product: |
DB2 FOR LUW / DB2FORLUW / 950 - DB2 |
| 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. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* All users of version 9.5 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 9.7 and higher versions. *
**************************************************************** |
| 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. |
| Solution |
|
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 14.10.2010
18.10.2010
18.10.2010 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
