Overflows in tablesnapshot

db2udbgirl

Table level snapshot showed me a entry as below

Table Schema = CARD
Table Name = DEALER
Table Type = User
Data Object Pages = 3480
Index Object Pages = 2622
Rows Read = 6570376
Rows Written = 0
Overflows = 157542
Page Reorgs = 0

So I did an reorgchk and it showed me entries as below
/home/card30] > db2 reorgchk update statistics on table card.dealer

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: CARD.DEALER
CARD DEALER 136882 52514 6386 6586 - 56121620
38 52 96 **-
----------------------------------------------------------------------------------------

Index statistics:

F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM
EMPTY LEAFS) * INDEXPAGESIZE) > 50
F6: (100 - PCTFREE) * ((INDEXPAGESIZE - 96) / (ISIZE + 12)) ** (NLEVELS
- 2) * (INDEXPAGESIZE - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5)
< 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20

SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS
F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: CARD.DEALER
CARD GME_DEALER_KEY 136882 339 0 2 10 10110 136882
100 46 0 6 0 -*---
CARD GM_AP_DEALER_KEY 136882 155 0 2 7 639 136882
100 86 0 0 0 -----
CARD LAAM_DEALER_KEY 136882 301 0 2 23 0 136882
100 88 0 0 0 -----
CARD NA_DEALER_KEY 136882 367 0 2 30 270 136882
100 88 0 0 0 -----
CARD NA_IPTV_CLAIM_KEY 136882 376 0 3 31 4 136882
100 88 101 0 0 --*--
CARD SAP_DEALER_KEY 136882 297 0 2 8 93879 136882
100 47 0 40 0 -*-*-
CARD XIF10DEALER 136882 160 0 2 8 4 78520
43 62 0 0 0 *----
CARD XIF11DEALER 136882 66 0 2 3 1074 169
98 63 2 0 0 -----
CARD XIF12DEALER 136882 156 0 2 12 9644 38656
12 50 1 6 0 **---
CARD XIF13DEALER 136882 64 0 2 3 0 6
100 65 2 0 0 -----
CARD XIF1DEALER 136882 108 0 2 3 83403 2
100 38 2 37 0 -*-*-
CARD XIF2DEALER 136882 224 0 2 14 6 97791
71 66 0 0 0 *----
CARD XIF3DEALER 136882 69 0 2 7 0 289
97 60 2 0 0 -----
CARD XIF4DEALER 136882 63 0 2 5 0 27
100 66 2 0 0 -----
CARD XIF5DEALER 136882 65 0 2 2 1964 6
97 64 2 1 0 -----
CARD XIF6DEALER 136882 71 0 2 4 1 36
95 58 2 0 0 -----
CARD XIF7DEALER 136882 90 15 2 12 7604 56
94 55 2 5 16 -----
CARD XIF8DEALER 136882 64 0 2 6 0 24
96 65 2 0 0 -----
CARD XIF9DEALER 136882 65 0 2 4 4033 3
100 64 2 2 0 -----
SYSIBM SQL060213134634900 136882 124 0 2 4 0 136882
100 87 0 0 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.
Hence I ran the following 2 commands manually
1) db2 runstats on table card.dealer and
2) db2 reorg table card.dealer

Both completed successfully but still tablesnapshot shows an Overflow.
Table Schema = CARD
Table Name = DEALER
Table Type = User
Data Object Pages = 3480
Index Object Pages = 2622
Rows Read = 6570376
Rows Written = 0
Overflows = 157542
Page Reorgs = 0
Table Reorg Information:
Node number = 0
Reorg Type =
Reclaiming
Table Reorg
Allow Read Access
Recluster Via Table Scan
Reorg Data Only
Reorg Index = 0
Reorg Tablespace = 20
Start Time = 04/18/2006 20:21:06.151482
Reorg Phase = 3 - Index Recreate
Max Phase = 3
Phase Start Time = 04/18/2006 20:21:10.247186

May I know how to resolve this problem.

Thanks
db2udbgirl.

Apr 18 '06 #1

Visu

You will have to update the statistics after the re-org, also don't
forget to reset the snapshot counters to get the latest info

Apr 19 '06 #2

db2udbgirl

Thanks Visu. When I reset the monitor I got the expected output as
below
Table Schema = CARD
Table Name = DEALER
Table Type = User
Data Object Pages = 3480
Index Object Pages = 2622
Rows Read = 0
Rows Written = 0
Overflows = 0
Page Reorgs = 0

Apr 19 '06 #3

Ian

db2udbgirl wrote:

Table level snapshot showed me a entry as below

Table Schema = CARD
Table Name = DEALER
Table Type = User
Data Object Pages = 3480
Index Object Pages = 2622
Rows Read = 6570376
Rows Written = 0
Overflows = 157542
Page Reorgs = 0

So I did an reorgchk and it showed me entries as below

...

Hence I ran the following 2 commands manually
1) db2 runstats on table card.dealer and
2) db2 reorg table card.dealer

Both completed successfully but still tablesnapshot shows an Overflow.
...
May I know how to resolve this problem.

Solving an overflow problem usually involves both a REORG and a review
of the application -- i.e. why did you get overflows in the first place?

Is it because the app inserts a record with NULL or very short strings
into the VARCHAR fields and then goes back and UPDATEs the VARCHARs with
longer string values? Is PCTFREE too small?

So, find the source of the problem, otherwise you'll be running REORG on
a regular basis to try and get rid of overflows.

Apr 19 '06 #4