Scenario: ExampleBANK reclaiming table and index space - Improving row overflow performance

During normal SQL processing, a row value update might result in that row no longer fitting in the original location in the database. When this scenario occurs, the database manager splits the row into two pieces. In the original location, the row is replaced with a pointer. The location that the new pointer indicates is where the larger and new copy of the row can be found. Any subsequent access to the updated row now follows this pointer, causing performance degradation.

Olivia checks whether the table T1 is suffering from the additional performance cost that is associated with overflow pairs:

SELECT T.TABNAME, T.OVERFLOW_ACCESSES FROM TABLE(SYSPROC.MON_GET_TABLE('','',0)) 
			WHERE TABNAME = 'T1' ORDER BY OVERFLOW_ACCESSES

TABNAME                                             OVERFLOW_ACCESSES
--------------------------------------------------- --------------------
T1                                                                   172

  1 record(s) selected.

Olivia notes that T1 might benefit from a CLEANUP reorganization to reduce the number of overflow accesses. Olivia uses the following command on each table:

REORG TABLE T1 INPLACE CLEANUP OVERFLOWS

Olivia can then rerun the original monitor command after this reorganization operation. Olivia notices the number of new pointer or overflow accesses is reduced to 0.

SELECT T.TABNAME, T.OVERFLOW_ACCESSES FROM TABLE(SYSPROC.MON_GET_TABLE('','',0)) 
			WHERE TABNAME = 'T1' ORDER BY OVERFLOW_ACCESSES

TABNAME                                             OVERFLOW_ACCESSES
--------------------------------------------------- --------------------
T1                                                                     0

  1 record(s) selected.