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
To
specify index compression:
COMPRESS YES- Activates 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 not in universal table spaces and 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.
COMPRESS NO- Specifies that no index compression will be in effect. This is the default option for the CREATE INDEX statement.
For an index that is not in a universal
table space, 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.
For indexes in universal table spaces,
alterations to index compression are a pending change that place the
index in advisory REORG-pending (AREOR) status. To materialize the
pending change, you must reorganize the entire index and all partitions
by running the REORG INDEX utility or the REORG TABLESPACE utility
with SHRLEVEL REFERENCE or SHRLEVEL CHANGE. If the index is defined
with the DEFINE NO attribute and data sets are not created yet, the
alteration is still immediate. Also, for an index that is not in a
universal table space, an alteration to index compression can be a
pending change if other pending changes exist at the index, table,
or table space level when the ALTER INDEX COMPRESS statement runs.