Reorganization of partition-by-growth table spaces

Because the REORG TABLESPACE utility cannot reclaim physical space, the excess partitions are empty. If the data needs additional space, the REORG TABLESPACE utility triggers the process to add additional partitions if the maximum number of partitions has not been reached. If the maximum number of partitions has been reached, the REORG TABLESPACE utility fails.

If you specify REORG TABLESPACE PART for a partition-by-growth table space, the utility fails if the data does not fit back into its partition.

This can happen because of the change in the amount of free space during the REORG. To prevent the utility from failing, run REORG TABLESPACE on the entire table space, or modify the PCTFREE parameter to fit the data rows into the partition.

To ensure that the REORG utility is able to condense the data into the minimum number of required partitions, parallelism for the REORG utility does not apply to partition-by-growth table spaces.

If the partition-by-growth table space contains LOB columns, the REORG TABLESPACE utility minimizes partitions by eliminating existing holes, but does not move the data from one partition to another.

When you reorganize a partition-by-growth table space at the partition level, the REORG TABLESPACE utility minimizes partitions by eliminating existing holes.

If there is a compression dictionary, the compression dictionary is copied to all partitions even if the partition is empty.

By default, when you run REORG TABLESPACE against a partition-by-growth table space, DB2® adds new partitions as necessary, to accommodate the growth in size. In the following situations, the addition of new partitions might lead to failure of the utility because of a lack of disk space:

• When REORG TABLESPACE is run against a subset of the partitions in a partition-by-growth table space
• When REORG TABLESPACE AUX NO is run against a partition-by-growth table space in which a table contains LOB columns

For these situations only, you can alleviate space problems by taking one of the following actions:

• Execute ALTER TABLESPACE on the table space to change PCTFREE and FREEPAGE to 0. This action is the preferred solution.
• Instead of running REORG TABLESPACE against a subset of the partitions in a partition-by-growth table space, run REORG on the entire table space.
• Set the REORG_IGNORE_FREESPACE subsystem parameter to YES. Doing so causes the REORG utility not to honor the PCTFREE and FREEPAGE values that are defined for the table space. REORG uses 0 for the PCTFREE and FREEPAGE values when it reloads data into the table space.