DB2z UPDATE机制分析

简单理解，UPDATE是一个先delete然后insert的一个符合操作，所以update后record的位置变化取决于insert的机制。

对于UPDATE操作，如果record长度没有发生变化或者长度变短 ，record在page内的位置不会发生变化；如果长度增加 ：1.如果当前所在page的剩余空间可以容下增加后的长度，那么其他record向前移动回收空间，这条record放在所有记录的后面。2.如果当前所在page的空间不能容下增加后的长度，DB2会就近寻找空间（向前或者向后16个page），如果没有找到就做整个tablespace(partition)的扫描，如果依然没能找到，就会extend这个tablespace(partition)，将记录放在最末。

下面是做过的一些实验，可以帮助理解上面的结论。

Case 1: Update record 1 and set varchar field from 0 length to 80 bytes (page has sufficient space to hold extended record)
Result: Row 1 moved to the end of the page, and other records are shifted to occupy the record 1's space. All records' page offset not changed.

Case 2: Update record 2 and set varchar field from 0 length to 80 bytes (page hasn't sufficient space to hold extended record)
Result: Record 2 moved to the last page of the table space(partition), new page offset assigned. The original record 2 is overwritten with a pointer to the last page and the new offset, record 2's space is not reallocated.

Case 3: Update record 3 and set varchar field from 0 length to 80 bytes
Result: Record 2's space is reallocated, record 3 moved to the end of the page(after record 1), all other records shifted up. Page offset not changed.

Case 4: Update record 4 and set char field from '0' to '1' (length not changed)
Result: Record 4's position is not changed, the record is updated as is.

Case 5: Update record 1 and set varchar field from '=========' to '--------' (length decreased)
(record 3 is at the bottom of the page, record 1 is before record 3)
Result: Record 1 is updated and position is not changed. The rest of the '===========' is not reallocated.

Case 6: Update record 1 and set varchar field from '---------' to '========' (length increased back to 80)
Result: Record 1 is moved to the end of the page, the records after record 1 are shifted up.

--------------------------------------------------------------------------------------------------------------------------
Case 7: Delete record 118 from page 5 to make some free space (record 118 is the first record on page 5), then update record 4's varchar field on page 1 and increase the length to 80.
Result: After delete, the record 118 on page 5 is marked as free. Page header and last 4 bytes on the end of the page was changed.
After update, the space on the fifth page is reallocated, and all records shifted, the record 4 is appended to the end of the page, page offset is 0001. On the original location of record 4, a pointer is placed, pointing to record offset 1 on page 5.