Compressing indexes

You can compress your indexes to significantly reduce the physical space requirements for most indexes.

Before you begin

Use the DSN1COMP utility on existing indexes to get an indication of the appropriate page size for new indexes. You can choose 8K, 16K, and 32K buffer pool page sizes for the index. Choosing a 32K or 16K buffer pool instead of a 8K buffer pool accommodates a potentially higher compression ratio, but also increases the potential to use more storage. Estimates for index space savings from the DSN1COMP utility, whether on the true index data or some similar index data, are not exact.

About this task

Index compression is heavily data-dependent, and some indexes might contain data that will not yield significant space savings. Compressed indexes might also use more real and virtual storage than non-compressed indexes. The amount of additional real and virtual storage used depends on the compression ratio used for the compressed keys, the amount of free space, and the amount of space used by the key map. The recommendation is to use index compression where a reduction in index storage consumption is more important than a possible decrease in index performance.

The additional cost of compressed indexes can be zero even in random key updates, as long as index pages can be kept in the buffer pool.

Procedure

Begin general-use programming interface information.To specify index compression:

Specify the compression option by issuing a CREATE INDEX or ALTER INDEX statement.
COMPRESS YES
Start of changeActivates index compression. The buffer pool used to create the index must be 8 KB, 16 KB, or 32 KB in size. The physical page size on disk will be 4 KB. If you create the index with the clause COMPRESS YES, index compression begins as soon as the first index entries are added.
Restrictions:
  • For user-managed index data sets, a compressed index requires a defined control interval size (CISZ) of 4 KB.
  • For DB2®-managed index data sets that are altered to enable compression (ALTER COMPRESS YES), the next utility operation to remove the REBUILD-pending state will not apply the utility REUSE option.
End of change
COMPRESS NO
Specifies that no index compression will be in effect. This is the default option for the CREATE INDEX statement.
End general-use programming interface information.

If you activate or deactivate compression with an ALTER INDEX statement, the index is placed into REBUILD-pending (RBDP) status for partitioned indexes and page set REBUILD-pending (PSRBD) status for non-partitioned indexes. You need to use the REBUILD INDEX utility to rebuild the index, or use the REORG utility to reorganize the table space that corresponds to the index.