DB2 - Problembeschreibung
Problem IC71003 | Status: Geschlossen |
AVGLEAFKEYSIZE AND AVGNLEAFKEYSIZE ARE INCORRECT WHEN THE FIRST (OR ONLY) COLUMN OF AN INDEX IS A VARYING LENGTH COLUMN | |
Produkt: | |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 | |
Problembeschreibung: | |
When an index's first (or only) column is a varying-length column, the RUNSTATS utility fails to account for the column in its calculation of AVGLEAFKEYSIZE and AVGNLEAFKEYSIZE. This problem affects the REORGCHK utility which uses the AVGLEAFKEYSIZE and AVGNLEAFKEYSIZE statistical values. In the following example, column i1 is a fixed-length column and column v2 is a varying-length column. Index I2 and I4 have incorrect AVGLEAFKEYSIZE and AVGNLEAFKEYSIZE. create table S90933.T1 (i1 int, v2 varchar(10)); insert into S90933.T1 values (1,'1'),(2,'22'),(3,'333'),(4,'4444'),(5,'55555'); create index S90933.I1 on S90933.T1 (i1); create index S90933.I2 on S90933.T1 (v2); create index S90933.I3 on S90933.T1 (i1,v2); create index S90933.I4 on S90933.T1 (v2,i1); runstats on table S90933.T1 and indexes all; select substr(indname,1,5) as index,substr(colnames,1,10) as columns, avgleafkeysize, avgnleafkeysize from syscat.indexes where tabschema='S90933'; INDEX COLUMNS AVGLEAFKEYSIZE AVGNLEAFKEYSIZE ----- ---------- -------------- --------------- I1 +I1 4 4 I2 +V2 0 0 I3 +I1+V2 7 7 I4 +V2+I1 4 4 4 record(s) selected. Workaround: Manually update the statistics to the correct values. For example: update sysstat.indexes set avgleafkeysize=3, avgnleafkeysize=3 where indschema='S90933' and indname='I2'; update sysstat.indexes set avgleafkeysize=7, avgnleafkeysize=7 where indschema='S90933' and indname='I4'; In DB2 Version 9.7, manually updating AVGLEAFKEYSIZE and AVGNLEAFKEYSIZ will not disable automatic statistics collection for the table. If automatic statistics collection for the database is enabled, and if an automatic runstats occurs for the table, the updated values will be overwritten by the values calculated by the runstats utility. | |
Problem-Zusammenfassung: | |
**************************************************************** * USERS AFFECTED: * * ALL * **************************************************************** * PROBLEM DESCRIPTION: * * See Problem Description above. * **************************************************************** * RECOMMENDATION: * * Upgrade to DB2 Version 9.7 Fix Pack 4. * **************************************************************** | |
Local-Fix: | |
Workaround: Manually update the statistics to the correct values. | |
verfügbare FixPacks: | |
DB2 Version 9.7 Fix Pack 4 for Linux, UNIX, and Windows | |
Lösung | |
First fixed in DB2 Version 9.7 Fix Pack 4. | |
Workaround | |
keiner bekannt / siehe Local-Fix | |
Bug-Verfolgung | |
Vorgänger : APAR is sysrouted TO one or more of the following: IC71004 Nachfolger : | |
Weitere Daten | |
Datum - Problem gemeldet : Datum - Problem geschlossen : Datum - der letzten Änderung: | 08.09.2010 03.05.2011 03.05.2011 |
Problem behoben ab folgender Versionen (IBM BugInfos) | |
9.7.FP4 | |
Problem behoben lt. FixList in der Version | |
9.7.0.4 |