Space reclamation for column-organized tables

When data is deleted from a column-organized table, the storage extents whose pages held deleted data are candidates for space reclamation. The space reclamation process finds these extents, and returns the pages that they hold to table space storage, where they can later be reused by any table in the table space.

You can start this process manually by specifying the RECLAIM EXTENTS option for the REORG TABLE command, or you can use an automated approach. If you set the DB2_WORKLOAD registry variable to ANALYTICS, a default policy is applied, and the auto_reorg database configuration parameter is set so that automatic reclamation is active for all column-organized tables. For more information, see Enabling automatic table and index reorganization and Configuring an automated maintenance policy.

You can monitor the progress of a table reorganization operation with the reorganization monitoring infrastructure. The ADMIN_GET_TAB_INFO table function returns an estimate of the amount of reclaimable space on the table, which you can use to determine when a table reorganization operation is necessary.

Note: Because RECLAIMABLE_SPACE output from the ADMIN_GET_TAB_INFO table function is an estimate that applies to the time at which the RUNSTATS command was run, this information might be outdated for column-organized tables.

Space reclamation after deletions

When a row in a column-organized table is deleted, the row is logically deleted, not physically deleted. As a result, the space that is used by the deleted row is not available to subsequent transactions and remains unusable until space reclamation occurs. For example, consider the case where a table is created and one million rows are inserted in batch operation A. The size of the table on disk after batch operation A is 5 MB. After some time, batch operation B inserts another 1 million rows. Now the table uses 10 MB on disk. Then, all of the rows that were inserted in batch operation A are deleted, and the table size on disk remains 10 MB. If a third batch operation C inserts another 1 million rows into the table, 5 MB of extra space is required. (With row-organized tables, the rows that are inserted in batch operation C would use the space that was vacated by the deleted rows from batch operation A.) A REORG TABLE command is required to reclaim the space that was used by the rows that were inserted in batch operation A. An extent cannot be reclaimed until all rows in the extent are deleted.

Note: When large object (LOB) data is deleted from the columnar table, the data storage is not returned to the tablespace for reuse or freed up for future use.

Space reclamation after updates

When a row in a column-organized table is updated, the existing row is first deleted, and a new copy of the row is inserted at the end of the table. This means that an updated row uses space in proportion to the number of times that the row is updated until space reclamation occurs. All rows in the extent that contains an updated row must be deleted before the extent is reclaimed.

Space reclamation after automatic recompress for tables

The Automatic Recompress feature for tables uses the new compression daemon to asynchronously check tables that contain rows that were inserted before compression dictionary creation. It then recompresses those rows in place, leaving empty extents. These empty extents can be reclaimed later with REORG TABLE ... RECLAIM EXTENTS. The freed extents which are reclaimed can then be re-used by other tables in the same table space.