DB2 Version 9.7 for Linux, UNIX, and Windows

Enabling compression in an existing table

You can modify an existing table to take advantage of the storage-saving benefits of compression using the ALTER TABLE command.

About this task

You must decide whether you want to use row compression only, value compression only, or both types of compression. Row compression will almost always yield benefits in terms of storage savings, as it attempts to replace data patterns that span multiple columns within a row with shorter symbol strings. Value compression can offer savings when you have a many rows with columns that contain the same value, or when you have columns that contain the default value for the data type of the column. When value compression is enabled, you can also specify that columns that assume the system default value for their data types can be further compressed with the COMPRESS SYSTEM DEFAULT option.

Restrictions

If you attempt to apply compression to columns that contain system default values using the COMPRESS SYSTEM DEFAULT clause, you must also specify VALUE COMPRESSION. Otherwise, a warning is returned, and system default values are not stored using minimal space.

If you are planning to enable value compression, be aware that the row size can, in some cases, grow as result of the overhead imposed by the database manager in dealing with certain data types. You can determine the impact that value compression has on row size using the information provided about this option in the documentation for the CREATE TABLE statement.

Before you begin

This task assumes that you have a table that currently does not employ row or value compression, and that you want to activate one or both of these storage-saving features.

Procedure

  1. Formulate an ALTER TABLE statement.
    • If you want to use row compression, include the COMPRESS YES clause.
    • If you want to use value compression, include the ACTIVATE VALUE COMPRESSION clause. If you want to compress system default values, include the COMPRESS SYSTEM DEFAULT clause.
  2. Run the ALTER TABLE statement At this point, all subsequent rows appended, inserted, loaded or updated will use the new, compressed format. However, existing uncompressed rows will remain uncompressed.
  3. Optional: If you want compression applied to the existing rows of the table, perform a table reorganization using the REORG command. Alternatively, you can wait until the uncompressed rows are next updated; at that point any rows changed will be stored in the new compressed format.

Results

If you altered the table, but did not perform a REORG, the format of the existing rows or columns of the table are not modified in any way, although any subsequent rows appended, updated, inserted or loaded will take advantage of whatever compression you have enabled. If you did perform a REORG, then whatever type of compression you enabled with the ALTER TABLE statement will apply to all rows of the table.

Example

Example 1: Applying row compression to an existing table CUSTOMER.

   ALTER TABLE CUSTOMER      COMPRESS YES

Example 2: Applying row, value and system default compression to the SALARY column of an existing table EMPLOYEE_SALARY.

ALTER TABLE EMPLOYEE_SALARY 
ALTER SALARY COMPRESS SYSTEM DEFAULT
COMPRESS YES ACTIVATE VALUE COMPRESSION;

REORG TABLE EMPLOYEE_SALARY
Parent topic: Row compression