DB2 FOR LUW / DB2FORLUW / 970 - DB2

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.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL                                                          * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 9.7 fixpack 10                        * 
****************************************************************

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.

Problem was first fixed in DB2 version 9.7 fixpack 10

not known / see Local fix

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

20.02.2014
13.11.2014
13.11.2014

9.7.FP10