DBzTech-Technology Dossier

Disk storage and space management is a highly discussed topic nowadays. In the context of database management, DB2 V9 leverages Venom technology to compress row data, which reduces I/O, improves efficiency, and provides faster access to data.

Types of Data Compression in DB2

DB2 offers three main types of data compression for tables:

1. Row Compression
2. Value Compression
3. Adaptive Compression

How Data Compression Works

Data compression is achieved by using the COMPRESS YES option in CREATE TABLE or ALTER TABLE statements. Here’s how DB2 handles it:

1. DB2 scans the table data, including all rows, to identify repetitive or duplicate values.
2. Internally, it builds a dictionary-based algorithm that assigns short numeric keys to these repeating values.

Example:

Consider a table with the following rows:

Mike   2000  LONDON 70045 UK
John   3000  LONDON 70045 UK

DB2 detects that the pattern "LONDON 70045 UK" is repetitive. It then assigns a numeric key to this pattern, for example, 02. The table is internally stored as:

Mike   2000   02
John   3000   02

Here, 02 represents "LONDON 70045 UK".

The dictionary mapping these repetitive fields is a hidden object in the database and remains cached for quick access. However, there are cases where compression does not yield space savings; in such scenarios, DB2 will not compress the data.

Important Notes:

• Data compression occurs only when the table dictionary is built, which typically happens during the REORG phase.
• For LOB tablespaces, do not specify the COMPRESS YES option.

Types of Compression in Detail

1. Row Compression: Compresses repeating patterns across multiple columns in a row.
2. Adaptive Compression: Combines classic row compression with adaptive techniques to replace multi-column data patterns with shorter symbols (as shown in the example above).
3. Value Compression: Optimized for columns with repeated values, such as CITY, or columns that contain default values for their data type.

Usage:

• Adaptive Compression: COMPRESS YES ADAPTIVE
• Classic Row Compression: COMPRESS YES STATIC
• Value Compression: Use the COMPRESSION clause. To include system default values, also add COMPRESS SYSTEM DEFAULT.