DB2 Version 10.1 for Linux, UNIX, and Windows

Changing or disabling compression for a compressed table

You can change how a table is compressed or disable compression entirely for a table that has adaptive, classic row, or value compression enabled by using one or more of the various compression-related clauses of the ALTER TABLE statement.

About this task

If you deactivate adaptive or classic row compression, index compression is not affected. If you want to uncompress an index, you must use the ALTER INDEX statement.

Procedure

To deactivate compression for a table, or to change from one type of row compression to another:

Issue an ALTER TABLE statement.
- If you want to deactivate adaptive or classic row compression, include the COMPRESS NO clause.
- If you want to change to a different type of row compression, specify the type of compression you want using the COMPRESS YES ADAPTIVE or COMPRESS YES STATIC clauses. For example, if you have a table that currently uses classic row compression, and you want to change to adaptive compression, execute the ALTER TABLE statement with the COMPRESS YES ADAPTIVE clause
- If you want to deactivate value compression, include the DEACTIVATE VALUE COMPRESSION clause.
- If you want to deactivate the compression of system default values, include the COMPRESS OFF option for the ALTER column name clause.
Perform an offline table reorganization using the REORG TABLE command.

Results

If you turned off row compression using the COMPRESS NO clause, all row data is uncompressed.
If you changed from one type of row compression to another, the entire table is compressed using the type of row compression you specified in the ALTER TABLE statement. (See Example 2.)
Deactivating value compression has the following effects:
- If a table had columns with COMPRESS SYSTEM DEFAULT enabled, compression is no longer enabled for these columns.
- Uncompressed columns might cause the row size to exceed the maximum that the current page size of the current table space allows. If this occurs, error message SQL0670N is returned.

Examples

Example 1: Turning off row compression: The following statements turn off adaptive or classic row compression in an table named CUSTOMER and then reorganizes the table to uncompress that data that was previously compressed:

     ALTER TABLE CUSTOMER COMPRESS NO 
     REORG TABLE CUSTOMER

Example 2: Changing from static to adaptive compression: Assumes that the SALES table currently uses classic row compression. The following statements change the type of compression used to adaptive compression:

     ALTER TABLE SALES COMPRESS ADAPTIVE YES 
     REORG TABLE SALES