|
Problem IC75077
|
Status: Closed |
INACCURATE CLUSTERING STATISTICS IN SYSCAT.INDEXES FOR CERTAIN P ARTITIONED INDEXES WHEN 'DETAILED INDEX' STATISTICS IS REQUESTED |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
Clustering statistics in syscat.indexes are incorrect for certai
n partitioned indexes if few partitions are empty.
For example:
.
Table spanning across 7 partition and 2 partitioned index
created on it.
select varchar(indschema,20), varchar(indname,20), clusterratio,
clusterfactor from
syscat.indexes where tabname ='TEST1' with ur;
1 2 CLUSTERRATIO
CLUSTERFACTOR
-------------------- -------------------- ------------
------------------------
DB2INST1 INDX1 100
-1.00000000000000E+000
DB2INST1 INDX2 -1
+1.00000000000000E+000
2 record(s) selected.
select varchar(indschema,20), varchar(indname,20),
DATAPARTITIONID, CLUSTERRATIO, CLUSTERFACTOR
from syscat.indexpartitions where tabname ='TEST1'
1 2 DATAPARTITIONID
CLUSTERRATIO CLUSTERFACTOR
-------------------- -------------------- ---------------
------------ ------------------------
DB2INST1 INDX1 0
100 -1.00000000000000E+000
DB2INST1 INDX1 1
-1 +5.00000000000000E-001
DB2INST1 INDX1 2
100 -1.00000000000000E+000
DB2INST1 INDX1 3
-1 +5.00000000000000E-001
DB2INST1 INDX1 4
-1 +0.00000000000000E+000
DB2INST1 INDX1 5
-1 +1.00000000000000E+000
DB2INST1 INDX1 6
100 -1.00000000000000E+000
DB2INST1 INDX2 0
100 -1.00000000000000E+000
DB2INST1 INDX2 1
-1 +1.00000000000000E+000
DB2INST1 INDX2 2
100 -1.00000000000000E+000
DB2INST1 INDX2 3
-1 +1.00000000000000E+000
DB2INST1 INDX2 4
-1 +1.00000000000000E+000
DB2INST1 INDX2 5
-1 +1.00000000000000E+000
DB2INST1 INDX2 6
100 -1.00000000000000E+000
.
In this case partition 0, 2 and 6 are empty and we see for index
INDX1 CR=100, where as for INDX2 it is showing -1.
. |
| Problem Summary: |
Clustering statistics in syscat.indexes are incorrect for certai
n partitioned indexes if few partitions are empty.
For example:
.
Table spanning across 7 partition and 2 partitioned index
created on it.
select varchar(indschema,20), varchar(indname,20), clusterratio
clusterfactor from
syscat.indexes where tabname ='TEST1' with ur;
1 2 CLUSTERRATIO
CLUSTERFACTOR
-------------------- -------------------- ------------
------------------------
DB2INST1 INDX1 100
-1.00000000000000E+000
DB2INST1 INDX2 -1
+1.00000000000000E+000
2 record(s) selected.
select varchar(indschema,20), varchar(indname,20),
DATAPARTITIONID, CLUSTERRATIO, CLUSTERFACTOR
from syscat.indexpartitions where tabname ='TEST1'
1 2 DATAPARTITIONID
CLUSTERRATIO CLUSTERFACTOR
-------------------- -------------------- ---------------
------------ ------------------------
DB2INST1 INDX1 0
100 -1.00000000000000E+000
DB2INST1 INDX1 1
-1 +5.00000000000000E-001
DB2INST1 INDX1 2
100 -1.00000000000000E+000
DB2INST1 INDX1 3
-1 +5.00000000000000E-001
DB2INST1 INDX1 4
-1 +0.00000000000000E+000
DB2INST1 INDX1 5
-1 +1.00000000000000E+000
DB2INST1 INDX1 6
100 -1.00000000000000E+000
DB2INST1 INDX2 0
100 -1.00000000000000E+000
DB2INST1 INDX2 1
-1 +1.00000000000000E+000
DB2INST1 INDX2 2
DB2INST1 INDX2 3
-1 +1.00000000000000E+000
DB2INST1 INDX2 4
-1 +1.00000000000000E+000
DB2INST1 INDX2 5
-1 +1.00000000000000E+000
DB2INST1 INDX2 6
100 -1.00000000000000E+000
.
100 -1.00000000000000E+000
DB2INST1 INDX2 3
-1 +1.00000000000000E+000
DB2INST1 INDX2 4
-1 +1.00000000000000E+000
DB2INST1 INDX2 5
-1 +1.00000000000000E+000
DB2INST1 INDX2 6
100 -1.00000000000000E+000
.
In this case partition 0, 2 and 6 are empty and we see for index
INDX1 CR=100, where as for INDX2 it is showing -1. |
| Local Fix: |
You could manually update the statisics.. |
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
apar ic75077
module engn_sqno
fixed >= v97 fpk6 |
| Workaround |
You could manually update the statisics.. |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 16.03.2011
08.12.2011
08.12.2011 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FPk6
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
