Forwarded Rows

From DBArtisan
Jump to: navigation, search

Go Up to Space Analyst Expert Guide

IBM DB2 will move rows in a table under certain conditions. One situation might arise when you update a row in a table that has a variable-length column to a larger size that will no longer fit on its original page. Another situation would be if IBM DB2 moves a row when the clustered index column changes. When IBM DB2 creates a forwarding pointer, the pointer remains in place unless one of two things happens. The first is when a row shrinks enough to move back to its original location. The second is when the entire database shrinks. When a database file shrinks, IBM DB2 will reassign the row identifiers, which are used as the row locators, so the shrink process never generates forwarded rows.

Forwarded records can reduce performance at times because additional I/O is involved to first obtain the record pointer to the relocated row, and then the row itself. But when does row forwarding become a problem? For example, just because a table has one hundred forwarded rows, does that mean a performance problem exists? The answer is: it depends. If the table has one hundred rows in it, then yes, a problem exists - 100% of the table suffers from forwarded rows. If the table has three million rows, then the forwarded row situation is likely not causing much fuss in the database.