Using fixed-length compression to compress your data

You can reduce the space required for a table by using data compression. Compressing the data in a table space can significantly reduce the amount of disk space that is needed to store data and can help improve buffer pool performance.

Before you begin

FL 504The TS_COMPRESSION_TYPE subsystem parameter specifies the default compression algorithm that is used to compress Db2 data at the subsystem level.

FL 509 The COMPRESS option of the ALTER TABLESPACE, CREATE TABLE, and CREATE TABLESPACE statements can define the compression algorithm that is used to compress Db2 data at the table space or partition level.

You can use the DSN1COMP utility to determine how well compression of your data will work.

For fixed-length compression, the CPU cost of both compression and decompression increases with smaller compression ratios. Therefore, it is best to avoid the use of compression if the compression ratio, or the percentage of saved space due to compression, is less than 10–20 percent. The additional CPU cost for compression and decompression makes it not worthwhile.

To use fixed-length compression, any of the following must be true:

The table space is defined with the COMPRESS YES clause and the TS_COMPRESSION_TYPE subsystem parameter is set to FIXED_LENGTH.
FL 509 The table space is defined with the COMPRESS YES FIXEDLENGTH clause.

Data in a table space that is defined in the work file database (the table space for declared temporary tables) cannot be compressed.

About this task

When you compress data, bit strings that occur frequently are replaced by shorter strings. Information about the mapping of bit strings to their replacements is stored in a compression dictionary. Fixed-length compression uses Db2 to compress data before it is stored and to decompress the data that is retrieved from a page in the buffer pool. In many cases, using the COMPRESS clause can significantly reduce the amount of disk space needed to store data, but the compression ratio that you achieve depends on the characteristics of your data.

With compressed data, you might see some of the following performance benefits, depending on the SQL workload and the amount of compression:

Higher buffer pool hit ratios
Fewer I/Os
Fewer getpage operations

Procedure

To compress data when Db2 is using fixed-length compression:

Populate the table space with data by taking one of the following actions:

Run the LOAD utility with REPLACE, RESUME NO, or RESUME YES SHRLEVEL CHANGE, and without KEEPDICTIONARY.
Run the REORG utility without KEEPDICTIONARY.
Issue INSERT statements.
Issue MERGE statements.

If no compression dictionary already exists, and the amount of data in the table space reaches a threshold determined by Db2, a compression dictionary is created. After the compression dictionary is built, Db2 uses it to compress all subsequent data added to the table space.