DB2 - Problembeschreibung
Problem IC99408 | Status: Geschlossen |
THE SQL0437W MIGHT BE INDUCED BY RUNSTATS RESULTING IN INCORRECT COLCARD VALUE | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 | |
Problembeschreibung: | |
The problem occurs once you execute RUNSTATS with column details like below: RUNSTATS ON TABLE DB2INST1.TESTTABLE ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND DETAILED INDEXES ALL Afterwards the select statements might return warning: SQL0437W Performance of this complex query might be sub-optimal. Reason code: "6". SQLSTATE=01602 The warning is caused by inconsistency between values in SYSSTAT.COLUMNS and SYSSTAT.COLDIST tables, where the COLCARD value of SYSSTAT.COLUMNS is the incorrect one. You can check the conditions by obtaining db2look with mimic option for the database, and comparing UPDATE statements for SYSSTAT.COLUMNS and SYSSTAT.COLDIST tables: 1. The UPDATE for SYSSTAT.COLUMNS table sets incorrect COLCARD value 3: UPDATE SYSSTAT.COLUMNS SET COLCARD=3, NUMNULLS=0, SUB_COUNT=-1, SUB_DELIM_LENGTH=-1, AVGCOLLENCHAR=-1, AVGCOLLEN=16 WHERE COLNAME = 'C1' AND TABNAME = 'TESTTABLE' AND TABSCHEMA = 'DB2INST1'; 2. The correct cardinality is stored in SYSSTAT.COLDIST table (note 4 different COLVALUE contents): UPDATE SYSSTAT.COLDIST SET COLVALUE='VAL1 ', VALCOUNT=92041 WHERE COLNAME = 'C1' AND TABNAME = 'TESTTABLE' AND TABSCHEMA = 'DB2INST1' AND TYPE = 'F' AND SEQNO = 1; UPDATE SYSSTAT.COLDIST SET COLVALUE='VAL2 ', VALCOUNT=1602 WHERE COLNAME = 'C1' AND TABNAME = 'TESTTABLE' AND TABSCHEMA = 'DB2INST1' AND TYPE = 'F' AND SEQNO = 2; UPDATE SYSSTAT.COLDIST SET COLVALUE='VAL3 ', VALCOUNT=26 WHERE COLNAME = 'C1' AND TABNAME = 'TESTTABLE' AND TABSCHEMA = 'DB2INST1' AND TYPE = 'F' AND SEQNO = 3; UPDATE SYSSTAT.COLDIST SET COLVALUE='VAL4 ', VALCOUNT=9 WHERE COLNAME = 'C1' AND TABNAME = 'TESTTABLE' AND TABSCHEMA = 'DB2INST1' AND TYPE = 'F' AND SEQNO = 4; The incorrect RUNSTATS results as above might occur when following conditions are true: (1) The table has compressed index, (2) The index has pseudo deleted keys. The problem is more likely to occur in an index where there is a high degree of commonality in the index keys. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Error Description * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 version 10.1 fixpack 4 * **************************************************************** | |
Local-Fix: | |
To workaround the SQL0437W warning you might consider: 1. Running RUNSTATS without column details. 2. Update SYSSTAT.COLUMNS COLCARD value manually to reflect the number of distinct values in SYSSTAT.COLDIST table. 3. REORG problematic table and indexes for the table to get rid of pseudo deleted keys, followed by a new RUNSTATS with all desired column options. Note that problem might reoccur again after introduction of new pseudo deleted keys in the index and subsequent RUNSTATS operation. | |
verfügbare FixPacks: | |
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
Problem was first fixed in DB2 version 10.1 fixpack 4 | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC99527 IC99703 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 17.02.2014 03.06.2014 03.06.2014 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
Problem behoben lt. FixList in der Version | |
10.1.0.4 |