Db2 – How to identify overflow rows in DB2 LUW

db2db2-luw

Situation: DB2 LUW database with several tables reporting overflow.

We are attempting to determine the cause of the overflow. The tables in question do NOT have varchar columns. Million+ rows are inserted per month. Updates to rows are mostly limited to changing a single column from a value of 0 to 1. Tables are compressed. Clustered index.

Is anyone aware of a DB2 for LUW utility that can be used to identify the specific rows which have overflowed? We are hoping that by identifying the rows we can uncover the cause of the overflow.

Currently there are two causes being entertained – the combination of compression/update or the presence of the clustered index.

Best Answer

You are likely correct to suspect compression as the cause of overflows. Having a clustered index by itself does not cause row movement outside of a reorg.

You can use the db2dart (Database Analysis and Reporting Tool) utility to dump formatted table pages; the "Record Type" field will identify which records have overflown. For example, run

db2dart yourdb /dd /tsi 2 /tn YOURTABLE /ps 0 /np 0 /v n

to dump all pages (/ps 0 /np 0) from the table YOURTABLE (note the uppercase name) in tablespace 2.

Overflown record will appear like this: 

        Slot 203:

           Offset Location = 1580  (x62C)
           Record Length = 12  (xC)

           Record Type = Pointer Record (tombstoned) (PUNC)

               Overflow Record Location:  Page 10  Slot 33

with their actual data in the new location:

        Slot 33:

           Offset Location = 3528  (xDC8)
           Record Length = 26  (x1A)

           OverFlow Record with Back Pointer

               Backpointer Location:  Page 7  Slot 203

           Compressed Record:  Row_Compressed

           Actual Record Length = 52  (x34)

           Record Type = Table Data Record (FIXEDVAR) (PUNC)

           Record Flags = 0

You can use the verbose flag (/v y) to include the actual row data in the report. For a large table you probably want to select a subset of pages by specifying a sensible value for /np.

Don't forget to FLUSH BUFFERPOOL ALL before running db2dart on an active database, otherwise the data on disk, where the utility reads them, may not reflect changes still in the bufferpool.

Related Question