|
Problem IC74823
|
Status: Closed |
THE OUTPUT OF REORGCHK AND REORGCHK_IX_STATS ARE NOT MATCHING |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
# create a table with indexes
# load lots of data
# delete some of the data
# runstats on the table and indexes all
Compare the results of output
db2 reorgchk update statistic on table <tb name>
db2 call reorgchk_ix_stats('T','schema.tablename')
Wrong * reported for index : GINAULAK.GI1_TP2 AND
GINAULAK.GI2_TP2
hotellnx95:/home/hotellnx95/ginaulak/PCR059456/NORMALTAB> db2
"reorgchk update statistics on table ginaulak.tp2"
Doing RUNSTATS ....
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA.NAME CARD OV NP FP ACTBLK
TSIZE F1 F2 F3 REORG
----------------------------------------------------------------
------------------------
Table: GINAULAK.TP2
1283 0 150 298 -
292524 0 25 51 -**
Table: GINAULAK.TP2
Data Partition: PART0
0 0 0 104 -
0 0 0 0 -**
Table: GINAULAK.TP2
Data Partition: PART1
517 0 61 104 -
117876 0 30 63 -**
Table: GINAULAK.TP2
Data Partition: PART2
766 0 89 90 -
174648 0 53 100 -*-
----------------------------------------------------------------
------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (Space used on leaf pages / Space available on
non-empty leaf pages) > MIN(50, (100 - PCTFREE))
F6: (100 - PCTFREE) * (Amount of space available in an index
with one less level / Amount of space required for all keys) <
100
F7: 100 * (Number of pseudo-deleted RIDs / Total number of RIDs)
< 20
F8: 100 * (Number of pseudo-empty leaf pages / Total number of
leaf pages) < 20
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL
KEYS LEAF_RECSIZE NLEAF_RECSIZE LEAF_PAGE_OVERHEAD
NLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6 F7 F8 REORG
----------------------------------------------------------------
----------------------------------------------------------------
-----------------------------------------------
Table: GINAULAK.TP2
Index: GINAULAK.GI1_TP2
1283 312 154 4 1238
1283 208 208 132
132 0 99 45 285 49 49 -****
Index: GINAULAK.GI2_TP2
1283 244 121 3 1246
1125 205 205 132
132 0 13 50 23 49 49 **-**
Index: GINAULAK.LI3_TP2
Data Partition: PART0
- - - - -
- - - -
- - - - - - - -----
Index: GINAULAK.LI3_TP2
Data Partition: PART1
517 110 45 2 515
517 208 8 132
566 0 100 44 - 49 40 -*-**
Index: GINAULAK.LI3_TP2
Data Partition: PART2
766 94 0 2 734
766 208 8 132
566 0 100 45 - 48 0 -*-*-
----------------------------------------------------------------
----------------------------------------------------------------
-----------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate
REORG is necessary
for indexes that are not in the same sequence as the base table.
When multiple
indexes are defined on a table, one or more indexes may be
flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the
corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a
dimension index
is equal to the Active blocks statistic of the table.
hotellnx95:/home/hotellnx95/ginaulak/PCR059456/NORMALTAB> db2
"call reorgchk_ix_stats('T','GINAULAK.TP2')"
Result set 1
--------------
TABLE_SCHEMA
TABLE_NAME
INDEX_SCHEMA
INDEX_NAME
DATAPARTITIONNAME
INDCARD NLEAF NUM_EMPTY_LEAFS
NLEVELS NUMRIDS_DELETED FULLKEYCARD
LEAF_RECSIZE NONLEAF_RECSIZE LEAF_PAGE_OVERHEAD
NONLEAF_PAGE_OVERHEAD PCT_PAGES_SAVED F4 F5 F6
F7 F8 REORG
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
-------------------- -------------------- --------------------
----------- -------------------- --------------------
-------------------- -------------------- --------------------
--------------------- --------------- ----------- -----------
----------- ----------- ----------- -----
GINAULAK
TP2
GINAULAK
GI1_TP2
1283 312 154
4 1238 1283
208 208 822
822 0 99 54 151
49 49 --***
GINAULAK
TP2
GINAULAK
GI2_TP2
1283 244 121
3 1246 1125
204 204 822
822 0 13 60 15
49 49 *--**
GINAULAK
TP2
GINAULAK
LI3_TP2
PART0
0 110 110
2 0 0
208 8 -1
-1 -1 100 60 -1
0 100 ----*
GINAULAK
TP2
GINAULAK
LI3_TP2
PART1
517 110 45
2 515 517
208 8 -1
-1 -1 100 44 -1
49 40 -*-**
GINAULAK
TP2
GINAULAK
LI3_TP2
PART2
766 94 0
2 734 766
208 8 -1
-1 -1 100 45 -1
48 0 -*-*-
5 record(s) selected.
Return Status = 0
Next do a reorg on the table:
db2 "reorg table ginaulak.tp2"
and again check it.. sometime it will show mismatch for some
index; it does not always happen though. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* THE OUTPUT OF REORGCHK AND REORGCHK_IX_STATS ARE NOT *
* MATCHING *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 LUW Version 9.7 Fix Pack 5 *
**************************************************************** |
| Local Fix: |
N/A |
| 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 |
Problem was first fixed in Version 9.7 Fix Pack 5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 03.03.2011
17.01.2012
17.01.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.FP5
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
