DB2 FOR LUW / DB2FORLUW / A10 - DB2

Under rare circumstances, DB2 optimizer may use frequency 
VALCOUNT of a COLVALUE that doesn't match with the value in a 
query. The problem happens on CHAR or VARCHAR data type columns 
only. 
 
For example, 
 
1. In TAB1, value "0A00000005" appears in 1000 rows, and others 
values appear in 1 row only. 
 
db2 "select count(*),c1 from tab1 group by c1" 
 
1           C1 
----------- ---------- 
          1 0000000009 
          1 0A00000000 
       1000 0A00000005 
          1 0A00000019 
          1 0A00000123 
          1 0A99900999 
          1 0B99990099 
 
2. SYSSTAT.COLDIST has valid data, and frequency VALCOUNT of 
"0A00000005" is 1000. 
 
select substr(colvalue, 1, 30) colvalue, valcount 
from sysstat.coldist 
where colname = 'C1' and tabname ='TAB1' and 
tabschema = '<SCHEMA>' and TYPE = 'F' 
 
COLVALUE                       VALCOUNT 
------------------------------ -------------------- 
'0A00000005'                                   1000 
-                                                -1 
-                                                -1 
-                                                -1 
-                                                -1 
 
3. For the following query, optimizer incorrectly uses frequency 
VALCOUNT of "0A00000005" for "c1='0A00000019'" predicate and 
estimate 1000 returned rows. 
 
select * from tab1 where c1='0A00000019'; 
 
      Rows 
     RETURN 
     (   1) 
      Cost 
       I/O 
       | 
      1000 
     DTQ 
     (   2) 
    0.850451 
        0 
       | 
      1000 
     IXSCAN 
     (   3) 
    0.502298 
        0 
       | 
      1006 
 INDEX: SHULIN 
    TAB1_IDX1 
       Q1

**************************************************************** 
* USERS AFFECTED:                                              * 
* All Users                                                    * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 V101FP4 or higher version                     * 
****************************************************************

statistical views can be used to work around the issue.

DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
 DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
 

Fixed on DB2 V101FP4 or higher version

not known / see Local fix

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

16.01.2014
26.06.2014
26.06.2014