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
Procedure
To deactivate compression for a table, or to change from one type of row compression to another:
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