DB2 FOR LUW / DB2FORLUW / A10 - DB2

Optimizer profile is not saved properly if we want to exclude 
one or more columns from distribution statistics and this will 
create problem when 'USE PROFILE' option is given. 
 
--Num_freq and Num_quantile is 0 for column 'B' as we dont want 
distributs stats to be collected on this. 
 
$ runstats on table sahanant.tmpp on all columns with 
distribution on all columns and columns (a NUM_FREQVALUES 5 
NUM_QUANTILES 5, b NUM_FREQVALUES 0 NUM_QUANTILES 0) default 
NUM_FREQVALUES 6 NUM_QUANTILES 6 set profile only 
 
--Note: statistics_profile does not include columns 'B' in the 
runstats command 
 
$ select substr(tabname,1,10) as tabname, 
substr(statistics_profile,1,500) as statistics_profile from 
syscat.tables where tabname='TMPP' and tabschema='SAHANANT' 
 
TABNAME    STATISTICS_PROFILE 
-------------    -------------------------------- 
TMPP          RUNSTATS ON TABLE "SAHANANT"."TMPP" ON ALL COLUMNS 
WITH DISTRIBUTION ON ALL COLUMNS AND COLUMNS ("A" NUM_FREQVALUES 
5 NUM_QUANTILES 5) DEFAULT NUM_FREQVALUES 8 NUM_QUANTILES 8 
 
 
$ runstats on table sahanant.tmpp use profile; 
 
-- We have collected distribution stats on column 'B' which is 
wrong. 
select substr(colname,1,10) as colname, type, seqno, 
substr(colvalue,1,10) as colvalue, valcount from syscat.coldist 
where tabname='TMPP' and tabschema='SAHANANT' order by 
colname,type,seqno 
 
COLNAME    TYPE SEQNO  COLVALUE   VALCOUNT 
---------- ---- ------ ---------- -------------------- 
..... 
.. 
B          F         1 1                             3 
B          F         2 2                             3 
B          F         3 7                             3 
B          F         4 10                            3 
B          F         5 12                            3 
B          F         6 14                            3 
B          Q         1 1                             3 
B          Q         2 722                         715 
B          Q         3 1406                       1428 
B          Q         4 2185                       2143 
B          Q         5 2906                       2858 
B          Q         6 3620                       3573 
 
As the above example indicates, runstats has collected 
distribution stats on column 'B' whereas the intention here is 
to exclude it.

**************************************************************** 
* USERS AFFECTED:                                              * 
* ALL PLATFORMS                                                * 
**************************************************************** 
* PROBLEM DESCRIPTION:                                         * 
* See Error Description                                        * 
**************************************************************** 
* RECOMMENDATION:                                              * 
* Upgrade to DB2 version 10.1 Fix Pack 4 or higher.            * 
****************************************************************

Do not use statistics profile for tables where we need to 
exclude some columns from distribution statistics - use explicit 
runstats command with NUM_FREQVALUES 0 or NUM_QUANTILES 0 for 
those columns.

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

First fixed in DB2 version 10.1 Fix Pack 4.

not known / see Local fix

forerunner  : APAR is sysrouted TO one or more of the following: IC99051 
follow-up : 

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

20.01.2014
02.06.2014
02.06.2014