Adjusting adaptive compression settings
Existing tables that have row compression enabled from a pre-Db2® version 12.1 database will be upgraded to have classic row compression enabled. If you want to use adapative compression you must enable it after the upgrade is performed.
Before you begin
The default behavior for compression has changed in Db2 version 10.1, and has the syntax for enabling compression. For details, see ALTER TABLE and CREATE TABLE statement have been changed..
About this task
Procedure
To take advantage of adaptive compression the following steps must be performed.
- Estimate storage space savings by executing the administrative function ADMIN_GET_TAB_COMPRESS_INFO. Compare the generated estimate with the current or actual compression table savings. If the estimated compression savings that can be achieved using adaptive compression meet your requirements, proceed with enabling adaptive compression.
- Perform ALTER TABLE with COMPRESS YES ADAPTIVE clause to enable adaptive compression. Modification of existing data rows and population of new rows will then be automatically subject to adaptive compression. Existing table rows are not immediately subject to adaptive compression as a result of issuing this ALTER statement. Any subsequent modification of existing rows or input of new rows into the table will lead to the application of adaptive compression.
- If you want to compress all existing rows, you can perform a classic table reorganization to immediately have all existing rows compressed, in a table that has been enabled for adaptive compression. The classic table reorganization should ideally be preformed with the RESETDICTIONARY parameter to achieve the maximum compression possible. Subsequent reorganization for the purposes of better compressing data rows may no longer be required. If desired, use the ADMIN_MOVE_TABLE procedure instead of performing a classic table reorganization.