DB2 compression

How Db2 data compression works

Compression is enabled in the table-space or table definition with a supported COMPRESS clause. Db2 examines representative rows, finds recurring byte sequences, and creates a compression dictionary. When a new row contains one of those sequences, Db2 writes a shorter token instead. An associated expansion dictionary maps the tokens back to their original bytes. Applications still receive the same SQL values; no column expression is required to decompress a row.

The dictionary belongs to a page set, which usually means a table space or an individual partition. Partition-level dictionaries are useful because customer, date, or regional partitions can have different data patterns. Dictionary pages occupy some space, so a tiny object might gain little or even no net benefit. Large rows with repeated names, codes, blanks, prefixes, and similar text often compress well. Already compressed, encrypted, or highly random values usually offer fewer reusable patterns.

Fixed-length and Huffman compression

Db2 supports dictionary-based algorithms whose availability depends on the Db2 level, object type, function level, and processor. Fixed-length compression replaces patterns with fixed-size symbols. Huffman compression uses variable-length encodings so frequent patterns can receive especially short representations. Huffman compression requires supported IBM Z hardware and eligible objects, including a universal table space.

The object definition, subsystem default such as TS_COMPRESSION_TYPE, and eligibility rules influence which algorithm Db2 chooses. The catalog column COMPRESS_USED is valuable because it records the algorithm actually used after relevant utility or data activity. Do not infer the active algorithm only from a desired DDL clause.

Adaptive compression and automatic dictionary creation

A compressed object does not necessarily have a dictionary the moment its DDL is committed. If an empty or nearly empty page set receives INSERT or MERGE activity, Db2 can wait until enough representative data exists, then build a dictionary automatically after an internally determined threshold is reached. This behavior is often described as adaptive dictionary creation: Db2 adapts to incoming data without requiring the DBA to run a utility first.

Rows written before the dictionary exists are not retroactively revisited by that automatic event. Subsequent rows can be compressed while earlier rows remain uncompressed. A mixture is valid and explains why an object can have a dictionary while PCTROWCOMP is below 100. REORG is the normal way to rewrite the whole target and apply a suitable dictionary consistently.

Automatic creation is convenient, but a dictionary sampled from an early, unusual data population might not represent long-term values. For example, the first load could contain only one region or one month. Monitor page savings after representative data arrives and consider a dictionary-replacing REORG when the distribution materially changes.

Creating and replacing dictionaries with REORG

For a compressed table space, REORG TABLESPACE without KEEPDICTIONARY normally builds a new dictionary from the current data and compresses rows as they are reloaded. This both reorganizes the object and lets the dictionary reflect today's values. Utility output includes compression information; IBM messages such as DSNU234I or DSNU244I report effectiveness, depending on the utility path.

1
2
3
4
5
6
7
REORG TABLESPACE PRODDB.ORDERTS
  SHRLEVEL CHANGE
 
-- Reuse the current dictionary instead:
REORG TABLESPACE PRODDB.ORDERTS
  SHRLEVEL CHANGE
  KEEPDICTIONARY

KEEPDICTIONARY reuses the existing dictionary. Reuse can avoid building a replacement and can be efficient when the data distribution remains stable. It is not automatically the best choice. If product descriptions, customer geography, encoding, or application content has changed, the old dictionary can match fewer patterns and save fewer pages. Also, REORG with KEEPDICTIONARY does not produce the normal compression report, so use fresh RUNSTATS and catalog measures when evaluating the result.

A practical replacement decision

• Keep the dictionary when its savings are good and data patterns are stable.
• Replace it when representative values have changed or savings have deteriorated.
• Test large partitions independently because each can have a different pattern.
• Retain utility messages and before-and-after catalog values as change evidence.

LOAD behavior and partially compressed data

LOAD can build a dictionary while records are being loaded into an object defined for compression. It needs a sample before it can complete the dictionary, so records used to build that initial sample can remain uncompressed. To avoid extra processing, LOAD does not necessarily return to rewrite those first rows; rows processed after dictionary creation are compressed. A later REORG can rewrite the complete object.

Exact behavior depends on LOAD mode, whether the target is empty, object type, SHRLEVEL, and the presence of an existing dictionary. LOAD REPLACE is a common dictionary-building path. Supported LOAD invocations can specify KEEPDICTIONARY to use an existing dictionary. Do not generalize one LOAD rule to every combination of REPLACE, RESUME, SHRLEVEL, and legacy object type; check the utility syntax for your Db2 release before changing production JCL.

1
2
3
4
5
6
7
8
9
LOAD DATA REPLACE
  INTO TABLE PROD.ORDERS
  INDDN SYSREC
 
-- When supported and an existing dictionary should be retained:
LOAD DATA REPLACE
  KEEPDICTIONARY
  INTO TABLE PROD.ORDERS
  INDDN SYSREC

Compression ratio, PAGESAVE, and PCTROWCOMP

Teams use the phrase compression ratio in different ways. A 3:1 ratio can mean that 300 units of original data occupy about 100 compressed units. A two-thirds space reduction can describe the same result. Always define the formula in a report. Db2 catalog fields use more specific meanings and should not be casually substituted for one another.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT DBNAME,
       TSNAME,
       PARTITION,
       PAGESAVE,
       COMPRESS_USED
  FROM SYSIBM.SYSTABLEPART
 WHERE DBNAME = 'PRODDB'
   AND TSNAME = 'ORDERTS'
 ORDER BY PARTITION;
 
SELECT CREATOR,
       NAME,
       PCTROWCOMP,
       STATSTIME
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'PROD'
   AND NAME = 'ORDERS';

PAGESAVE estimates the percentage of pages saved because of compression. PCTROWCOMP is the percentage of rows observed as compressed when RUNSTATS collected statistics. An object can show a high PCTROWCOMP but modest PAGESAVE if each compressed row saves little space. Conversely, stale statistics can make the two values appear inconsistent. Run RUNSTATS after representative processing and inspect STATSTIME before drawing a conclusion. COMPRESS_USED identifies the algorithm actually used, not a percentage.

CPU and I/O trade-offs

Compression exchanges processor work for reduced data movement. Writes require encoding, and rows must be expanded when Db2 needs their original bytes. That cost can increase CPU per row. At the same time, fewer pages can mean fewer synchronous reads, less DASD space, smaller image copies, less log or utility data in some workflows, and more logical rows held in the same buffer pool. Reduced I/O and improved buffer-pool residency often offset the encoding work, especially for I/O-bound workloads.

There is no universal winning ratio. A CPU-constrained system with hot rows already resident in memory might see less benefit than an I/O-constrained warehouse. Batch scans can read fewer pages, but still expand many rows. Update-heavy applications pay compression work frequently, while read-mostly history data may amortize dictionary creation over years. Compare class 1 and class 2 CPU, synchronous reads, getpages, buffer hit behavior, utility elapsed time, page counts, and DASD allocation under a representative workload.

Hardware compression and zEDC

Hardware capabilities can reduce compression cost, but terminology matters. Db2 table-space data compression is the dictionary-based feature that determines how table rows are stored in Db2 page sets. Supported IBM Z processors can accelerate parts of Db2 compression, and Huffman compression has its own hardware and object prerequisites.

zEDC, the IBM Z Enterprise Data Compression facility, accelerates eligible z/OS compression services for data streams and data sets. It can benefit surrounding processes such as eligible sequential utility input or output when the software and allocation options use those services. It does not replace a Db2 table-space compression dictionary, does not by itself turn COMPRESS YES on, and is not what SYSTABLEPART.COMPRESS_USED reports. An environment can use Db2 row compression, zEDC for eligible utility data sets, both, or neither.

CDDS: compression dictionary data sets

The compression dictionary data set (CDDS) is another feature whose name can cause confusion. The active dictionary used to read a Db2 page set remains associated with that page set. A CDDS is a separately defined VSAM key-sequenced data set that can hold external versions of expansion dictionaries. Its purpose is to make dictionaries available efficiently to eligible log-reading, replication, and continuous-availability processing that must expand compressed row images found in log records.

CDDS support and operational scope have evolved across Db2 releases and maintenance. Historically it was associated with specific GDPS continuous-availability configurations; newer Db2 13 maintenance broadens selected source-side use. Therefore, confirm the required function level, APARs, subsystem parameters such as CDDS_MODE and CDDS_PREFIX, data-sharing requirements, and replication configuration in documentation for the exact installed level.

REORG and LOAD can insert newly built expansion dictionaries into the CDDS when all eligibility conditions are met. REORG TABLESPACE INITCDDS YES is a special initialization path: it copies existing dictionaries for eligible DATA CAPTURE CHANGES objects into the CDDS and does not perform the ordinary table-space reorganization. Treat INITCDDS as dictionary externalization, not as a substitute for a normal REORG that rebuilds rows and replaces a poor dictionary.

1
2
3
4
5
6
7
8
9
10
11
-- Externalize existing eligible dictionaries; this is not a normal REORG:
REORG TABLESPACE PRODDB.ORDERTS
  INITCDDS YES
 
Operational checks:
  Confirm CDDS eligibility for the installed Db2 level
  Define and secure the required VSAM KSDS
  Verify CDDS_MODE and CDDS_PREFIX
  Populate existing dictionaries with INITCDDS YES
  Ensure REORG and LOAD procedures maintain CDDS entries
  Monitor replication and log-reader access

A dictionary can change after REORG, LOAD, or adaptive creation. Log readers might need an older expansion dictionary to interpret records written before the change, which is why CDDS processing can retain multiple dictionary versions. Coordinate third-party utilities and replication products with the CDDS design; a utility that replaces a dictionary but does not perform the required CDDS maintenance can leave downstream processing without the expected external copy.

Operational workflow

• Confirm object eligibility and choose fixed-length or Huffman deliberately.
• Capture current pages, PAGESAVE, COMPRESS_USED, PCTROWCOMP, and STATSTIME.
• Use representative data when allowing LOAD or REORG to build a dictionary.
• Choose KEEPDICTIONARY only after proving that the existing dictionary remains useful.
• Run RUNSTATS after the change and compare CPU, I/O, page counts, and elapsed time.
• Validate CDDS maintenance separately when replication or log readers depend on it.

Explain it like I'm 5

Imagine a book that repeats the words “mainframe computer” hundreds of times. You make a secret card saying that a tiny star means “mainframe computer,” then put a star in the book each time. The book becomes smaller, and the card is the dictionary. Db2 reads the card whenever it needs the full words. REORG can make a better new card after the story changes. zEDC is like a fast packing machine for other boxes; it is not the secret card inside this book. CDDS is a safe outside drawer where another trusted reader can find copies of old secret cards.

Exercises

1. Write DDL for an eligible compressed table space, then list the checks required before selecting Huffman instead of fixed-length compression.
2. An object has PCTROWCOMP 98 and PAGESAVE 22. Explain why both values can be correct and identify which one describes rows and which one describes pages.
3. Compare REORG with and without KEEPDICTIONARY for a table whose values changed after a business acquisition. Recommend a choice and define the evidence you would collect.
4. A LOAD builds a dictionary halfway through its input. Explain why some rows can remain uncompressed and how a later REORG changes the result.
5. Draw two separate flows: Db2 page-set dictionary compression and zEDC compression of an eligible utility output data set. Mark where each technology applies.
6. Design a CDDS readiness checklist covering VSAM definition, subsystem configuration, INITCDDS, utility compatibility, replication testing, and dictionary replacement.