Data Compression in Db2

Disk storage and space management play a critical role in database administration. To address these challenges, Db2 provides data compression technologies that reduce storage requirements and improve performance. As a result, organizations can lower storage costs while improving database efficiency.

By reducing the amount of data stored on disk, compression decreases I/O operations and improves buffer pool utilization. Consequently, queries often run faster and consume fewer system resources.

Types of Data Compression in Db2

Db2 provides several compression techniques for tables. Each technique targets different storage and performance requirements.

1. Row Compression (Classic or Static Compression)

Row compression uses a table-level compression dictionary to identify frequently occurring data patterns. It then replaces those patterns with shorter internal symbols. Therefore, Db2 stores less data while maintaining transparent access for applications.

These patterns may include complete values, portions of values, or combinations of values that appear repeatedly across rows.

2. Adaptive Compression

Adaptive compression extends classic row compression. In addition to a table-level dictionary, it uses page-level compression techniques to achieve higher compression ratios.

As a result, adaptive compression often delivers better space savings for large tables that contain diverse data patterns.

3. Value Compression

Value compression focuses on columns that contain frequently repeated values. For example, columns may contain default values, NULL values, or other commonly occurring entries.

Because Db2 stores these values more efficiently, value compression can reduce storage requirements without relying on a compression dictionary.

How Row Compression Works

When you enable compression, Db2 analyzes the table data and creates a compression dictionary. The dictionary contains frequently occurring byte patterns that Db2 identifies during analysis.

For example, consider the following rows:

NAME	SALARY	ADDRESS
Mike	2000	LONDON 70045 UK
John	3000	LONDON 70045 UK

In this example, the value “LONDON 70045 UK” appears multiple times. Therefore, Db2 can store the value once in the compression dictionary and use shorter internal references for subsequent occurrences.

As a result, Db2 reduces the amount of storage required while allowing applications to access the data normally.

Db2 manages the compression dictionary internally and automatically uses it whenever applications read or write compressed rows.

Enabling Compression

You can enable compression when you create a table or when you modify an existing table.

Adaptive Compression

CREATE TABLE EMPLOYEE
(
    EMPNO INTEGER,
    NAME VARCHAR(50)
)
COMPRESS YES ADAPTIVE;

Classic Row Compression

CREATE TABLE EMPLOYEE
(
    EMPNO INTEGER,
    NAME VARCHAR(50)
)
COMPRESS YES STATIC;

For existing tables, administrators typically run a REORG operation to create the compression dictionary and compress existing rows. After Db2 creates the dictionary, it automatically compresses newly inserted rows.

Value Compression

You define value compression at the column level.

Example:

CREATE TABLE EMPLOYEE
(
    EMPNO INTEGER,
    CITY VARCHAR(30) COMPRESS,
    STATUS CHAR(1) COMPRESS SYSTEM DEFAULT
);

In this example:

• COMPRESS enables value compression for the column.
• COMPRESS SYSTEM DEFAULT allows Db2 to compress values that match the system default for the data type.

Benefits of Compression

Compression offers several advantages.

• It reduces disk storage requirements.
• It lowers I/O activity.
• It improves buffer pool utilization.
• It speeds up table scans and query execution.
• It reduces backup and recovery storage requirements.

Furthermore, many organizations achieve significant cost savings by reducing storage consumption across large databases.

Important Considerations

Although compression provides many benefits, administrators should evaluate their workloads before enabling it.

• Tables with highly repetitive values usually achieve better compression ratios.
• Existing tables generally require a REORG operation before Db2 can compress current data efficiently.
• Traditional out-of-row LOB data does not participate in row compression.
• Compression requires additional CPU resources for compression and decompression activities.

However, in most environments, the reduction in I/O operations outweighs the additional CPU overhead.

Summary

Db2 compression technology helps organizations reduce storage costs and improve database performance. Depending on business requirements, administrators can choose value compression, classic row compression, or adaptive compression.

Ultimately, the right compression strategy can improve storage efficiency, reduce I/O activity, and enhance overall database performance.