How To
Summary
In Db2, the column values in a column-organized table are stored in an encoded or compressed format where they are tightly packed together in data pages. Updates to values in a column-organized table are not done in-place like in row-organized tables. Instead, update operations are decomposed into separate delete and insert operations internally. Furthermore, the storage used by the deleted values is not immediately available for reuse after the transaction is completed because Db2 handles storage in "extent" units. An extent is a set of contiguous data pages of a configurable size. Db2 can reclaim a storage extent for reuse only if all the data values in its pages were deleted or inserted elsewhere in an update operation. Storage extents whose pages contain many, but not all, deleted values are considered fragmented extents. In general, workloads with a high number of non-clustered updates or deletes can lead to the table having many highly fragmented extents, which causes storage usage to increase over time. Fragmentation can also cause query performance to degrade due to the extra I/O processing required to process data pages with many deleted values as well as new data pages resulting from inserts or updates that cannot reuse previous data pages.
In this technote, two stored procedures SYSTOOLS.ADMIN_COL_ANALYZE_FRAGMENTATION and SYSTOOLS.ADMIN_COL_DEFRAGMENT_TABLE are introduced to help users identify fragmented column-organized tables and then defragment them.
Document Location
Worldwide
Log InLog in to view more of this document
Was this topic helpful?
Document Information
Modified date:
30 April 2025
UID
ibm16465559