The percentage of Overflows over Rows Read

For each table, using a table snapshot  compute TBROVP = Overflows * 100 / Rows Read (+1 if you want to avoid division by zero). See the example below:

If your monitor is not on,turn on it with the command:

db2 update monitor switches using UOW on

or use the command below, but you will have to stop and start your instance to the command validate.

db2 update dbm cfg using DFT_MON_TABLE on

Apply the following command to get the rows read and overflow for each table:

db2 get snapshot for tables on sample

                                    Table Snapshot

First database connect timestamp     = 04/24/2009 10:05:22.224876
Last reset timestamp                 =
Snapshot timestamp                   = 04/24/2009 10:05:24.674842
Database name                        = SAMPLE
Database path                        = /home/db2inst1/db2inst1/NODE0000/SQL00001/
Input database alias                 = SAMPLE
Number of accessed tables            = 2

  Table Schema        =  FRUIT
 Table Name          =  APPLE
 Table Type          =  Uuser
 Data Object Pages   = 1
 Index Object Pages  = 6
 Rows Read           = 312347
 Rows Written        = 213
 Overflows           = 6546
 Page Reorgs         = 0

 Table Schema        =  FRUIT
 Table Name          =  GRAPE
 Table Type          = USer
 Data Object Pages   = 1
 Index Object Pages  = 6
 Rows Read           = 564721
 Rows Written        = 0
 Overflows           = 431
 Page Reorgs         = 0

In the example above, we can calculate the TBROVP with the values in bold:

Table Apple:

(6546*100)/(1+ 312347) = 2.09573937

Table Grape:

(431*100)/(1+564721) =  0.0763207383

A table write overflow occurs when a VARCHAR column is updated such that its length increases and, because the row is now larger (or wider), the row no longer fits on the data page where it was originally stored. DB2 relocates the row to a new data page and places a pointer in the original location to the new location.

A table read overflow occurs when DB2 attempts to read the row from its original location, then discovers the pointer and has to go read the row from its new location. Read overflows are particularly expensive because DB2 is now forced to do double the logical read I/O and probably double the physical I/O as well.

As a rule of thumb, when TBROVP exceeds 3% for any given table, then that table should be reorganized. Several companies use this metric in place of the reorgchk utility as an indicator for when to REORG a table. As a plus for avoiding reorgchk, the catalog statistics will not be updated so dynamic SQL access plans won’t be disrupted and performance should be more predictable.

db2 reorg table <table_name>

or execute if you need, a reorg online

db2 reorg table <table_name> inplace allow write access