DB2 - Problembeschreibung
Problem IC71934 | Status: Geschlossen |
INCORRECT BEHAVIOUR WHEN UPDATING TABLE CARDINALITY STATISTICS TO 0 (ZERO). | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 910 - DB2 | |
Problembeschreibung: | |
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-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * All users of version 9.1 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. | |
Lösung | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 15.10.2010 17.10.2010 17.10.2010 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7. | |
Problem behoben lt. FixList in der Version |