DB2 10.5 for Linux, UNIX, and Windows
DB2 10.5 for Linux, UNIX, and Windows
When data is deleted from a column-organized table, the storage extents whose pages held data that was all deleted 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.
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.
When a row in a column-organized table is updated, the 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 where the update took place must be deleted before any space reclamation can occur.