Hands-on lab for table and index compression in Db2

This lab focuses on table and index data compression in Db2. The emphasis is on row-organized tables and on the practical choices between COMPRESS YES STATIC, COMPRESS YES ADAPTIVE, VALUE COMPRESSION, and index compression.

1. Lab goals

By the end of the lab you should be able to:

Distinguish the current compression options for row-organized tables in Db2
Estimate savings for tables with no active compression
Estimate additional savings for tables that are already compressed
Estimate potential savings for indexes with no compression
Enable compression for tables and indexes
Reorganize objects so the compression is actually materialized

2. Required software and how to obtain it

For this lab, preferably use:

Db2 LUW 12.1 or 11.5.x on Linux. Community Edition is sufficient.
An account with instance owner privileges

You can obtain IBM Db2 from IBM's official product download channel.

3. Current compression options for row-organized tables

When a table is row-organized, the relevant options are the following:

Option	Typical clause	Main use
Classic row compression	`COMPRESS YES STATIC`	Stable dictionary, useful when you want predictable compression after `REORG`
Adaptive compression	`COMPRESS YES ADAPTIVE`	Adaptive dictionaries, often the most interesting choice for OLTP tables with evolving data patterns
Value compression	`ACTIVATE VALUE COMPRESSION`	Removes redundant storage of `NULL`, empty strings, and repeated values in specific columns
System default compression	`ALTER col COMPRESS SYSTEM DEFAULT`	Avoids explicitly storing system default values such as zeros or blanks
Index compression	`ALTER INDEX ... COMPRESS YES`	Reduces the physical size of indexes, especially in OLTP and DW environments with many repeated prefixes

In practice:

Static and adaptive compression cannot be used simultaneously on the same table
Value compression can be combined with either row-compression mode
Index compression is controlled separately

What distinguishes `STATIC` from `ADAPTIVE`

In both cases, Db2 reduces row size by replacing repeated data patterns with shorter symbols. The real difference is where the compression dictionary is established and how often it can adapt to the real data pattern.

`COMPRESS YES STATIC`

With static compression, Db2 uses only one global dictionary. That dictionary tries to capture the most repeated patterns found in a representative sample of the whole table.

The global dictionary can be created automatically after a threshold of inserted data is reached, or it can be created through REORG.
Once created, that dictionary does not keep adjusting itself to the local data patterns of each page.

STATIC usually works well when:

The table has highly repetitive row patterns
The most common values change little over time

`COMPRESS YES ADAPTIVE`

With adaptive compression, Db2 uses two layers of compression:

The same global dictionary used by static compression
Additional dictionaries at the level of specific pages, which make it possible to compress local repetition patterns more effectively

Db2 can dynamically create page-level compression dictionaries based on the repeated patterns that exist on that page. If needed, that local dictionary can later be recreated automatically.

The global dictionary still exists and still compresses global patterns. The additional gain from adaptive mode comes from this second layer of compression, which captures local repetition that the global dictionary does not represent as well and replaces those locally repeated values using symbols mapped through the page dictionary.

Adaptive compression is often a good choice when:

There are many optional columns
There are text strings with partial repetition, but not in a perfectly uniform way across the whole table
The data profile changes over time, for example by channel, campaign, operational state, or new load patterns

Think of an order table like the one used in this lab:

Some pages become dominated by ONLINE orders
Others accumulate more PARTNER orders
Some ranges contain more remarks, promo_code, and gift_message
Others have different combinations of shipping_mode, sales_rep, or approval_code

This lab does not assume adaptive compression is the first choice. It measures first:

PCTPAGESSAVED_STATIC
PCTPAGESSAVED_ADAPTIVE

and only then decides. If the difference is irrelevant, the global dictionary is already doing most of the work and static compression may be sufficient. If the difference is significant, that is a sign that adaptive compression can exploit additional local repetition to achieve better compression.

What about column-organized tables?

Column-organized tables have their own compression mechanisms. That topic is important, but it is outside the scope of this lab.

For this article:

We work only with tables whose data is row-organized (TABLEORG = 'R')
The SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO function is specific to row compression and returns zero rows for column-organized tables

4. Create the lab database

Before creating the database, switch to the Db2 instance owner account.

Example:

su - db2inst1

If the instance owner has another name in your environment, replace db2inst1 with the correct account name.

Now create the database:

db2 "create database COMPLAB pagesize 32768"

5. Create the schema objects

We will create objects for a simple business scenario that is still large enough to show data and index compression clearly.

In addition to the main tables, we create a small table and a volatile table that will be excluded from compression analysis.

cat > /tmp/complab_schema.sql <<'SQL'
CONNECT TO COMPLAB;

CREATE SCHEMA biz;

CREATE TABLE biz.customer_master
(
    customer_id        INTEGER NOT NULL,
    customer_name      VARCHAR(80) NOT NULL,
    segment            VARCHAR(20) NOT NULL,
    country_code       CHAR(2) NOT NULL,
    region_name        VARCHAR(20) NOT NULL,
    email_address      VARCHAR(120),
    phone_number       VARCHAR(24),
    marketing_opt_in   SMALLINT NOT NULL DEFAULT 0,
    credit_class       CHAR(1) NOT NULL DEFAULT 'B',
    status_code        CHAR(1) NOT NULL DEFAULT 'A',
    comments           VARCHAR(160),
    created_ts         TIMESTAMP NOT NULL,
    filler_code        CHAR(12) NOT NULL DEFAULT 'STANDARD',
    PRIMARY KEY (customer_id)
);

CREATE TABLE biz.sales_order
(
    order_id           BIGINT NOT NULL,
    customer_id        INTEGER NOT NULL,
    order_date         DATE NOT NULL,
    channel_code       VARCHAR(12) NOT NULL,
    order_status       VARCHAR(16) NOT NULL,
    country_code       CHAR(2) NOT NULL,
    currency_code      CHAR(3) NOT NULL,
    payment_method     VARCHAR(20) NOT NULL,
    shipping_mode      VARCHAR(20) NOT NULL,
    sales_rep          VARCHAR(30) NOT NULL,
    promo_code         VARCHAR(20),
    approval_code      CHAR(12),
    gift_message       VARCHAR(120),
    remarks            VARCHAR(200),
    fraud_score        SMALLINT NOT NULL DEFAULT 0,
    total_amount       DECIMAL(13,2) NOT NULL,
    last_update_ts     TIMESTAMP NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE TABLE biz.sales_order_line
(
    line_id            BIGINT NOT NULL,
    order_id           BIGINT NOT NULL,
    line_no            SMALLINT NOT NULL,
    sku_code           VARCHAR(30) NOT NULL,
    product_family     VARCHAR(30) NOT NULL,
    warehouse_code     CHAR(4) NOT NULL,
    tax_code           CHAR(3) NOT NULL,
    quantity           SMALLINT NOT NULL,
    unit_price         DECIMAL(11,2) NOT NULL,
    discount_pct       DECIMAL(5,2) NOT NULL DEFAULT 0,
    serial_number      VARCHAR(40),
    line_comment       VARCHAR(120),
    backorder_flag     SMALLINT NOT NULL DEFAULT 0,
    fulfilled_flag     SMALLINT NOT NULL DEFAULT 1,
    PRIMARY KEY (line_id)
);

CREATE TABLE biz.small_reference
(
    ref_code           CHAR(4) NOT NULL,
    ref_group          VARCHAR(20) NOT NULL,
    ref_description    VARCHAR(80) NOT NULL,
    PRIMARY KEY (ref_code)
);

CREATE TABLE biz.session_work_queue
(
    queue_id           BIGINT NOT NULL,
    session_token      CHAR(24) NOT NULL,
    worker_name        VARCHAR(30) NOT NULL,
    payload_type       VARCHAR(20) NOT NULL,
    payload_ref        VARCHAR(40),
    status_code        CHAR(1) NOT NULL,
    created_ts         TIMESTAMP NOT NULL,
    PRIMARY KEY (queue_id)
);

ALTER TABLE biz.session_work_queue VOLATILE CARDINALITY;

CREATE TABLE biz.tab_comp_baseline
(
    tabname            VARCHAR(128) NOT NULL,
    pre_npages         INTEGER NOT NULL,
    pagesize           INTEGER NOT NULL,
    st_pct             SMALLINT NOT NULL,
    ad_pct             SMALLINT NOT NULL,
    st_mb              DECIMAL(20,2) NOT NULL,
    ad_mb              DECIMAL(20,2) NOT NULL,
    PRIMARY KEY (tabname)
);

CREATE INDEX biz.ix_order_customer_date
    ON biz.sales_order (customer_id, order_date);

CREATE INDEX biz.ix_order_status_country
    ON biz.sales_order (order_status, country_code);

CREATE INDEX biz.ix_order_salesrep
    ON biz.sales_order (sales_rep);

CREATE INDEX biz.ix_line_order
    ON biz.sales_order_line (order_id);

CREATE INDEX biz.ix_line_sku
    ON biz.sales_order_line (sku_code);

CREATE INDEX biz.ix_line_wh_family
    ON biz.sales_order_line (warehouse_code, product_family);

CREATE INDEX biz.ix_queue_status
    ON biz.session_work_queue (status_code, created_ts);

COMMIT;
CONNECT RESET;
SQL

db2 -tvf /tmp/complab_schema.sql

6. Generate and load representative data

We will load data with typical business patterns:

Country codes, channels, payment methods, and statuses with strong repetition
Optional columns with many NULLs
Semi-repetitive text
Enough volume for compression to produce visible results
A small table and a VOLATILE table that do not justify evaluation

6.1 Load `customer_master`

cat > /tmp/complab_load_customers.sql <<'SQL'
CONNECT TO COMPLAB;

INSERT INTO biz.customer_master
WITH d(n) AS
(
    VALUES 0,1,2,3,4,5,6,7,8,9
),
nums(n) AS
(
    SELECT
        a.n * 10000 +
        b.n * 1000 +
        c.n * 100 +
        d1.n * 10 +
        e.n + 1
    FROM d a, d b, d c, d d1, d e
    WHERE
        a.n * 10000 +
        b.n * 1000 +
        c.n * 100 +
        d1.n * 10 +
        e.n + 1 <= 100000
)
SELECT
    n,
    'Customer ' || VARCHAR(n),
    CASE MOD(n, 5)
        WHEN 0 THEN 'Enterprise'
        WHEN 1 THEN 'Corporate'
        WHEN 2 THEN 'SMB'
        WHEN 3 THEN 'Retail'
        ELSE 'Partner'
    END,
    CASE MOD(n, 6)
        WHEN 0 THEN 'PT'
        WHEN 1 THEN 'ES'
        WHEN 2 THEN 'FR'
        WHEN 3 THEN 'DE'
        WHEN 4 THEN 'IT'
        ELSE 'NL'
    END,
    CASE MOD(n, 6)
        WHEN 0 THEN 'Iberia'
        WHEN 1 THEN 'Iberia'
        WHEN 2 THEN 'France'
        WHEN 3 THEN 'DACH'
        WHEN 4 THEN 'SouthEU'
        ELSE 'Benelux'
    END,
    CASE
        WHEN MOD(n, 20) = 0 THEN NULL
        ELSE 'contact' || VARCHAR(MOD(n, 12000)) || '@example.com'
    END,
    CASE
        WHEN MOD(n, 15) = 0 THEN NULL
        ELSE '+351210' || RIGHT(DIGITS(100000 + MOD(n, 900000)), 6)
    END,
    CASE WHEN MOD(n, 4) = 0 THEN 1 ELSE 0 END,
    CASE MOD(n, 4)
        WHEN 0 THEN 'A'
        WHEN 1 THEN 'B'
        WHEN 2 THEN 'B'
        ELSE 'C'
    END,
    CASE WHEN MOD(n, 25) = 0 THEN 'H' ELSE 'A' END,
    CASE
        WHEN MOD(n, 7) = 0 THEN 'Annual contract customer'
        WHEN MOD(n, 11) = 0 THEN 'Prefers consolidated monthly invoice'
        ELSE 'Standard sales account'
    END,
    TIMESTAMP('2023-01-01-08.00.00') + MOD(n, 700) DAYS,
    CASE WHEN MOD(n, 10) = 0 THEN 'STANDARD' ELSE 'STANDARD' END
FROM nums
WHERE NOT EXISTS
(
    SELECT 1
    FROM biz.customer_master c
    WHERE c.customer_id = nums.n
);

COMMIT;
CONNECT RESET;
SQL

db2 -tvf /tmp/complab_load_customers.sql

6.2 Load `sales_order`

This block creates 500000 orders with many repeated patterns and some sparse columns. The load runs in batches of 25000 rows with COMMIT between batches to avoid SQL0964C in environments with more conservative log settings. The NOT EXISTS clause makes the load repeatable without duplicate-key errors.

Inside each 25000-row batch, we create local micro-batches of 250 orders. In each micro-batch, several long text columns repeat the same local patterns and the same local identifier many times, but those patterns change in the next micro-batch. We do this because we are trying to reproduce a scenario where adaptive compression is more advantageous than static compression.

db2 connect to COMPLAB

for start_n in 1 25001 50001 75001 100001 125001 150001 175001 200001 225001 250001 275001 300001 325001 350001 375001 400001 425001 450001 475001
do
  end_n=$((start_n + 24999))

  db2 "
  INSERT INTO biz.sales_order
  WITH d(n) AS
  (
      VALUES 0,1,2,3,4,5,6,7,8,9
  ),
  nums(n) AS
  (
      SELECT
          a.n * 100000 +
          b.n * 10000 +
          c.n * 1000 +
          d1.n * 100 +
          e.n * 10 +
          f.n + 1
      FROM d a, d b, d c, d d1, d e, d f
      WHERE
          a.n * 100000 +
          b.n * 10000 +
          c.n * 1000 +
          d1.n * 100 +
          e.n * 10 +
          f.n + 1 BETWEEN ${start_n} AND ${end_n}
  )
  SELECT
      BIGINT(n),
      1 + MOD(n, 100000),
      DATE('2023-01-01') + MOD(n, 730) DAYS,
      CASE MOD(n, 4)
          WHEN 0 THEN 'ONLINE'
          WHEN 1 THEN 'PARTNER'
          WHEN 2 THEN 'DIRECT'
          ELSE 'FIELD'
      END,
      CASE MOD(n, 6)
          WHEN 0 THEN 'CREATED'
          WHEN 1 THEN 'PAID'
          WHEN 2 THEN 'ALLOCATED'
          WHEN 3 THEN 'SHIPPED'
          WHEN 4 THEN 'CLOSED'
          ELSE 'CLOSED'
      END,
      CASE MOD(n, 6)
          WHEN 0 THEN 'PT'
          WHEN 1 THEN 'ES'
          WHEN 2 THEN 'FR'
          WHEN 3 THEN 'DE'
          WHEN 4 THEN 'IT'
          ELSE 'NL'
      END,
      CASE MOD(n, 3)
          WHEN 0 THEN 'EUR'
          WHEN 1 THEN 'EUR'
          ELSE 'USD'
      END,
      CASE MOD(n, 5)
          WHEN 0 THEN 'CARD'
          WHEN 1 THEN 'CARD'
          WHEN 2 THEN 'TRANSFER'
          WHEN 3 THEN 'TRANSFER'
          ELSE 'INVOICE'
      END,
      CASE MOD(n, 4)
          WHEN 0 THEN 'STANDARD'
          WHEN 1 THEN 'STANDARD'
          WHEN 2 THEN 'EXPRESS'
          ELSE 'PICKUP'
      END,
      'REP_' || RIGHT(DIGITS(100 + MOD(n, 35)), 3),
      CASE
          WHEN MOD(n, 5) IN (0,1,2,3) THEN
              'L'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || '-P'
              || RIGHT(DIGITS(1000 + MOD(INTEGER((n - 1) / 250), 97)), 4)
          WHEN MOD(n, 15) = 0 THEN
              'X'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || '-P'
              || RIGHT(DIGITS(1000 + MOD(INTEGER((n - 1) / 250), 53)), 4)
          ELSE NULL
      END,
      CASE
          WHEN MOD(n, 6) IN (0,1,2,3) THEN 'APR' || RIGHT(DIGITS(100000000 + MOD(n, 900000000)), 9)
          ELSE NULL
      END,
      CASE
          WHEN MOD(n, 6) <> 5 THEN
              'LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' wrap pack LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' insert note LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' carrier tag LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
          ELSE NULL
      END,
      CASE
          WHEN MOD(n, 10) = 0 THEN
              'LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' route bundle LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' route bundle LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' route bundle LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' route bundle priority desk hold'
          WHEN MOD(n, 14) = 0 THEN
              'LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' split stage LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' split stage LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' split stage LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' split stage late dock'
          ELSE
              'LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' pack wave LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' pack wave LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' pack wave LOC'
              || RIGHT(DIGITS(100000 + INTEGER((n - 1) / 250)), 6)
              || ' pack wave regular dock'
      END,
      CASE
          WHEN MOD(n, 50) = 0 THEN 90
          WHEN MOD(n, 10) = 0 THEN 30
          ELSE 0
      END,
      DECIMAL(20 + MOD(n, 8000) + (MOD(n, 100) / 100.0), 13, 2),
      TIMESTAMP('2024-01-01-09.00.00') + MOD(n, 365) DAYS + MOD(n, 86400) SECONDS
  FROM nums nsrc
  WHERE NOT EXISTS
  (
      SELECT 1
      FROM biz.sales_order so
      WHERE so.order_id = BIGINT(nsrc.n)
  )"

  db2 commit
done

db2 connect reset

6.3 Load `sales_order_line`

This block generates two rows per order, which produces 1000000 rows with useful repetition for both data and index compression. Here too we use batched loading with COMMIT, and the NOT EXISTS filter avoids duplicates if the block is repeated. In this case we process 25000 orders per batch.

As in sales_order, we introduce long patterns that repeat strongly within local micro-batches of 250 orders, but change in the next micro-batch.

db2 connect to COMPLAB

for start_id in 1 25001 50001 75001 100001 125001 150001 175001 200001 225001 250001 275001 300001 325001 350001 375001 400001 425001 450001 475001
do
  end_id=$((start_id + 24999))

  db2 "
  INSERT INTO biz.sales_order_line
  SELECT
      BIGINT(o.order_id * 10 + l.line_no),
      o.order_id,
      l.line_no,
      'SKU-' || RIGHT(DIGITS(100000 + MOD(o.order_id + l.line_no, 2500)), 6),
      CASE MOD(o.order_id + l.line_no, 6)
          WHEN 0 THEN 'LAPTOP'
          WHEN 1 THEN 'SERVER'
          WHEN 2 THEN 'STORAGE'
          WHEN 3 THEN 'NETWORK'
          WHEN 4 THEN 'SOFTWARE'
          ELSE 'SERVICE'
      END,
      CASE MOD(o.order_id + l.line_no, 4)
          WHEN 0 THEN 'LISB'
          WHEN 1 THEN 'MADR'
          WHEN 2 THEN 'PARI'
          ELSE 'FRAN'
      END,
      CASE MOD(o.order_id + l.line_no, 3)
          WHEN 0 THEN 'NOR'
          WHEN 1 THEN 'RED'
          ELSE 'RED'
      END,
      SMALLINT(1 + MOD(o.order_id + l.line_no, 5)),
      DECIMAL(15 + MOD(o.order_id + l.line_no, 900) + (MOD(o.order_id, 100) / 100.0), 11, 2),
      DECIMAL(CASE WHEN MOD(o.order_id + l.line_no, 8) = 0 THEN 10.00 ELSE 0.00 END, 5, 2),
      CASE
          WHEN MOD(o.order_id + l.line_no, 20) = 0 THEN
              'SN'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || '-'
              || RIGHT(DIGITS(100000 + MOD(o.order_id, 900000)), 6)
          ELSE NULL
      END,
      CASE
          WHEN MOD(o.order_id + l.line_no, 9) = 0 THEN
              'LOC'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || ' bundle step LOC'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || ' bundle step LOC'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || ' bundle step'
          ELSE
              'LOC'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || ' pick step LOC'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || ' pick step LOC'
              || RIGHT(DIGITS(100000 + INTEGER((o.order_id - 1) / 250)), 6)
              || ' pick step'
      END,
      CASE WHEN MOD(o.order_id + l.line_no, 17) = 0 THEN 1 ELSE 0 END,
      CASE WHEN MOD(o.order_id + l.line_no, 13) = 0 THEN 0 ELSE 1 END
  FROM biz.sales_order o,
       (VALUES 1, 2) AS l(line_no)
  WHERE o.order_id BETWEEN ${start_id} AND ${end_id}
    AND NOT EXISTS
    (
        SELECT 1
        FROM biz.sales_order_line sol
        WHERE sol.line_id = BIGINT(o.order_id * 10 + l.line_no)
    )"

  db2 commit
done

db2 connect reset

6.4 Load the small table and the `VOLATILE` table

cat > /tmp/complab_load_auxiliary.sql <<'SQL'
CONNECT TO COMPLAB;

INSERT INTO biz.small_reference
SELECT *
FROM
(
    VALUES
    ('PT01', 'COUNTRY', 'Portugal'),
    ('ES01', 'COUNTRY', 'Spain'),
    ('FR01', 'COUNTRY', 'France'),
    ('DE01', 'COUNTRY', 'Germany'),
    ('IT01', 'COUNTRY', 'Italy'),
    ('NL01', 'COUNTRY', 'Netherlands'),
    ('ONL1', 'CHANNEL', 'Online direct channel'),
    ('PAR1', 'CHANNEL', 'Partner resale channel'),
    ('DIR1', 'CHANNEL', 'Direct sales channel'),
    ('FLD1', 'CHANNEL', 'Field account channel')
) AS src(ref_code, ref_group, ref_description)
WHERE NOT EXISTS
(
    SELECT 1
    FROM biz.small_reference r
    WHERE r.ref_code = src.ref_code
);

INSERT INTO biz.session_work_queue
WITH d(n) AS
(
    VALUES 0,1,2,3,4,5,6,7,8,9
),
nums(n) AS
(
    SELECT
        a.n * 1000 +
        b.n * 100 +
        c.n * 10 +
        d1.n + 1
    FROM d a, d b, d c, d d1
    WHERE
        a.n * 1000 +
        b.n * 100 +
        c.n * 10 +
        d1.n + 1 <= 5000
)
SELECT
    BIGINT(n),
    'TOK' || RIGHT(DIGITS(100000000 + MOD(n, 900000000)), 8) || RIGHT(DIGITS(100000000 + MOD(n * 7, 900000000)), 8) || RIGHT(DIGITS(1000 + MOD(n, 9000)), 5),
    'WORKER_' || RIGHT(DIGITS(100 + MOD(n, 20)), 3),
    CASE MOD(n, 4)
        WHEN 0 THEN 'ALLOC'
        WHEN 1 THEN 'PICK'
        WHEN 2 THEN 'PACK'
        ELSE 'SHIP'
    END,
    CASE
        WHEN MOD(n, 5) = 0 THEN 'ORD-' || VARCHAR(100000 + MOD(n, 500000))
        ELSE NULL
    END,
    CASE MOD(n, 3)
        WHEN 0 THEN 'N'
        WHEN 1 THEN 'R'
        ELSE 'D'
    END,
    TIMESTAMP('2026-01-01-00.00.00') + MOD(n, 30) DAYS + MOD(n, 86400) SECONDS
FROM nums
WHERE NOT EXISTS
(
    SELECT 1
    FROM biz.session_work_queue q
    WHERE q.queue_id = BIGINT(nums.n)
);

COMMIT;
CONNECT RESET;
SQL

db2 -tvf /tmp/complab_load_auxiliary.sql

7. Gather statistics and establish a baseline

Before estimating compression, update the statistics:

cat > /tmp/complab_runstats_before.sql <<'SQL'
CONNECT TO COMPLAB;

RUNSTATS ON TABLE biz.customer_master WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE biz.sales_order WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE biz.sales_order_line WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE biz.small_reference WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE biz.session_work_queue WITH DISTRIBUTION AND DETAILED INDEXES ALL;

CONNECT RESET;
SQL

db2 -tvf /tmp/complab_runstats_before.sql

8. Estimate compression for tables with no active compression

This is the first important operational scenario: row-organized tables with no active compression yet.

Header legend:

Header	Meaning
`tab`	Table name
`npg`	Current `NPAGES`
`card`	Estimated table cardinality
`psz`	Table space page size
`mode`	Current row compression mode
`cur_pct`	Current percentage of saved pages
`st_pct`	Savings estimate with `STATIC`
`st_row`	Estimated average row size with `STATIC`
`st_pages`	Estimated pages saved with `STATIC`
`st_mb`	Estimated MB saved with `STATIC`
`ad_pct`	Savings estimate with `ADAPTIVE`
`ad_row`	Estimated average row size with `ADAPTIVE`
`ad_pages`	Estimated pages saved with `ADAPTIVE`
`ad_mb`	Estimated MB saved with `ADAPTIVE`

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(c.tabname) AS VARCHAR(24)) AS tab,
    CAST(c.npages AS VARCHAR(8)) AS npg,
    CAST(c.card AS VARCHAR(12)) AS card,
    CAST(c.pagesize AS VARCHAR(8)) AS psz,
    CAST(CASE WHEN ci.rowcompmode IN ('A', 'S') THEN ci.rowcompmode ELSE '-' END AS VARCHAR(4)) AS mode,
    CAST(ci.pctpagessaved_current AS VARCHAR(6)) AS cur_pct,
    CAST(ci.pctpagessaved_static AS VARCHAR(6)) AS st_pct,
    CAST(ci.avgrowsize_static AS VARCHAR(8)) AS st_row,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_static / 100.0), 20, 2) AS VARCHAR(12)) AS st_pages,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_static / 100.0) * c.pagesize / 1048576.0, 20, 2) AS VARCHAR(10)) AS st_mb,
    CAST(ci.pctpagessaved_adaptive AS VARCHAR(6)) AS ad_pct,
    CAST(ci.avgrowsize_adaptive AS VARCHAR(8)) AS ad_row,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_adaptive / 100.0), 20, 2) AS VARCHAR(12)) AS ad_pages,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_adaptive / 100.0) * c.pagesize / 1048576.0, 20, 2) AS VARCHAR(10)) AS ad_mb
FROM
(
    SELECT
        t.tabschema,
        t.tabname,
        t.npages,
        t.card,
        ts.pagesize
    FROM syscat.tables t
    JOIN syscat.tablespaces ts
      ON ts.tbspace = t.tbspace
    WHERE t.tabschema = 'BIZ'
      AND t.type = 'T'
      AND t.tableorg = 'R'
      AND t.compression = 'N'
      AND t.card > 0
) AS c,
TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(c.tabschema, c.tabname)) AS ci
WHERE ci.object_type = 'DATA'
ORDER BY c.npages DESC"
db2 connect reset

Interpretation:

st_pct estimates the gain if you enable classic row compression
ad_pct estimates the gain if you enable adaptive compression
st_mb and ad_mb translate the percentage into potentially saved physical space

9. Schema-level estimate with prior filtering

This is the most useful approach when you want to work in batches without evaluating everything indiscriminately. The example below filters first by schema, minimum table size, and volatile tables. To keep the output focused on the decision, it shows only the columns that help compare STATIC and ADAPTIVE quickly.

db2 connect to COMPLAB
db2 "
WITH candidates AS
(
    SELECT
        t.tabschema,
        t.tabname,
        t.npages,
        t.card,
        ts.pagesize
    FROM syscat.tables t
    JOIN syscat.tablespaces ts
      ON ts.tbspace = t.tbspace
    WHERE t.tabschema = 'BIZ'
      AND t.type = 'T'
      AND t.tableorg = 'R'
      AND t.card > 0
      AND t.npages > 1000
      AND t.compression = 'N'
      AND COALESCE(t.volatile, ' ') <> 'C'
)
SELECT
    CAST(RTRIM(c.tabname) AS VARCHAR(24)) AS tab,
    CAST(c.npages AS VARCHAR(8)) AS npg,
    CAST(ci.pctpagessaved_static AS VARCHAR(6)) AS st_pct,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_static / 100.0), 20, 2) AS VARCHAR(12)) AS st_pages,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_static / 100.0) * c.pagesize / 1048576.0, 20, 2) AS VARCHAR(10)) AS st_mb,
    CAST(ci.pctpagessaved_adaptive AS VARCHAR(6)) AS ad_pct,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_adaptive / 100.0), 20, 2) AS VARCHAR(12)) AS ad_pages,
    CAST(DECIMAL((DECIMAL(c.npages,20,2) * ci.pctpagessaved_adaptive / 100.0) * c.pagesize / 1048576.0, 20, 2) AS VARCHAR(10)) AS ad_mb
FROM candidates c,
     TABLE
     (
         SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(c.tabschema, c.tabname)
     ) AS ci
WHERE ci.object_type = 'DATA'
ORDER BY
    c.npages DESC,
    c.card DESC"
db2 connect reset

Practical notes on the filters:

t.tableorg = 'R' prevents column-organized tables from entering the analysis
t.npages > 1000 avoids spending time on objects that are too small
COALESCE(t.volatile, ' ') <> 'C' excludes tables declared as volatile

Before moving on, it is worth checking explicitly why each table is included in or excluded from the analysis:

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(tabname) AS VARCHAR(24)) AS tab,
    CAST(npages AS VARCHAR(8)) AS npg,
    CAST(COALESCE(volatile, ' ') AS VARCHAR(4)) AS vol,
    CAST(compression AS VARCHAR(4)) AS comp,
    CASE
        WHEN tableorg <> 'R' THEN 'excluded: not row-organized'
        WHEN card = 0 THEN 'excluded: no useful statistics'
        WHEN COALESCE(volatile, ' ') = 'C' THEN 'excluded: volatile table'
        WHEN npages <= 1000 THEN 'excluded: too small'
        WHEN compression <> 'N' THEN 'excluded: already compressed'
        ELSE 'included in evaluation'
    END AS reason
FROM syscat.tables
WHERE tabschema = 'BIZ'
  AND type = 'T'
ORDER BY tabname"
db2 connect reset

In this lab, under normal conditions, you should observe:

SMALL_REFERENCE excluded because of size
SESSION_WORK_QUEUE excluded because it is marked VOLATILE
CUSTOMER_MASTER, SALES_ORDER, and SALES_ORDER_LINE included in the evaluation set

10. Compare `STATIC` and `ADAPTIVE` before compression

Before enabling compression, this analysis lets you compare STATIC and ADAPTIVE directly and choose the more advantageous mode for each table.

db2 connect to COMPLAB
db2 "
WITH candidates AS
(
    SELECT
        t.tabschema,
        t.tabname,
        t.npages,
        t.card,
        t.compression,
        ts.pagesize
    FROM syscat.tables t
    JOIN syscat.tablespaces ts
      ON ts.tbspace = t.tbspace
    WHERE t.tabschema = 'BIZ'
      AND t.type = 'T'
      AND t.tableorg = 'R'
      AND t.card > 0
      AND t.npages > 1000
      AND t.compression = 'N'
      AND COALESCE(t.volatile, ' ') <> 'C'
)
SELECT
    CAST(RTRIM(c.tabname) AS VARCHAR(24)) AS tab,
    CAST(c.npages AS VARCHAR(8)) AS npg,
    CAST(ci.pctpagessaved_static AS VARCHAR(6)) AS st_pct,
    CAST(ci.pctpagessaved_adaptive AS VARCHAR(6)) AS ad_pct,
    CAST(ci.pctpagessaved_adaptive - ci.pctpagessaved_static AS VARCHAR(6)) AS adv_pct,
    CAST(DECIMAL(
        (DECIMAL(c.npages,20,2) * (ci.pctpagessaved_adaptive - ci.pctpagessaved_static) / 100.0) * c.pagesize / 1048576.0,
        20, 2
    ) AS VARCHAR(10)) AS extra_mb
FROM candidates c,
     TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(c.tabschema, c.tabname)) AS ci
WHERE ci.object_type = 'DATA'
ORDER BY adv_pct DESC, npg DESC"
db2 connect reset

Header legend:

Header	Meaning
`tab`	Table name
`npg`	Current `NPAGES`
`st_pct`	Estimate with `STATIC`
`ad_pct`	Estimate with `ADAPTIVE`
`adv_pct`	`ADAPTIVE - STATIC` difference in percentage points
`extra_mb`	Additional MB potentially saved by `ADAPTIVE`

11. Identify explicitly where `ADAPTIVE` brings an advantage

The result of the previous section is used to choose the main candidate table. The lab logic is the following:

If a large table shows a material adv_pct, that table is the best candidate for adaptive compression
If another table also benefits strongly from compression in absolute terms, but shows a smaller difference between STATIC and ADAPTIVE, it is still a good target for compression, but it can start with STATIC
After enabling compression and reorganizing, the measured data should confirm those two ideas: the table chosen for ADAPTIVE should approach the adaptive estimate, and the table left on STATIC may still show additional potential in the final analysis

In the intended scenario for this lab, both large tables should show strong compression gains, but sales_order should show the clearest advantage for adaptive compression.

12. Estimate gains for uncompressed indexes

For indexes, the classic use case is simple: indexes without physical compression, on large tables, with a lot of repeated keys or prefixes.

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(indname) AS VARCHAR(28)) AS idx,
    CAST(RTRIM(tabname) AS VARCHAR(24)) AS tab,
    CAST(compress_attr AS VARCHAR(4)) AS attr,
    CAST(index_compressed AS VARCHAR(4)) AS icomp,
    CAST(pct_pages_saved AS VARCHAR(8)) AS pct_saved,
    CAST(num_leaf_pages_saved AS VARCHAR(10)) AS leaf_saved
FROM TABLE
(
    SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO('', 'BIZ', 'SALES_ORDER', NULL, NULL)
) AS t
ORDER BY num_leaf_pages_saved DESC"
db2 connect reset

Interpretation:

COMPRESS_ATTR = 'N' and INDEX_COMPRESSED = 'N' mean an index with no compression
PCT_PAGES_SAVED estimates the potential percentage of saved leaf pages
NUM_LEAF_PAGES_SAVED translates the gain into physical pages

Header legend:

Header	Meaning
`idx`	Index name
`tab`	Base table
`attr`	Compression attribute defined for the index
`icomp`	Current compression state of the index
`pct_saved`	Estimated percentage of saved leaf pages
`leaf_saved`	Estimated number of saved leaf pages

You can repeat the same analysis for another table:

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(indname) AS VARCHAR(28)) AS idx,
    CAST(RTRIM(tabname) AS VARCHAR(24)) AS tab,
    CAST(compress_attr AS VARCHAR(4)) AS attr,
    CAST(index_compressed AS VARCHAR(4)) AS icomp,
    CAST(pct_pages_saved AS VARCHAR(8)) AS pct_saved,
    CAST(num_leaf_pages_saved AS VARCHAR(10)) AS leaf_saved
FROM TABLE
(
    SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO('', 'BIZ', 'SALES_ORDER_LINE', NULL, NULL)
) AS t
ORDER BY num_leaf_pages_saved DESC"
db2 connect reset

13. Enable compression on the table and the indexes

We now turn the previous decision into concrete actions:

sales_order will be compressed with ADAPTIVE, because the estimate shows a visible advantage over STATIC
sales_order_line will be compressed with STATIC, not because it compresses poorly, but because the initial gap versus ADAPTIVE is smaller and we want to validate later whether additional headroom still exists
Columns with repeated defaults activate value compression
The relevant indexes are also changed to COMPRESS YES

Before the change, save a persistent baseline for both target tables. That baseline is used later to compare the original estimate with the real result measured after REORG:

db2 connect to COMPLAB
db2 "
MERGE INTO biz.tab_comp_baseline AS b
USING
(
    SELECT
        t.tabname,
        t.npages,
        ts.pagesize,
        ci.pctpagessaved_static AS st_pct,
        ci.pctpagessaved_adaptive AS ad_pct,
        DECIMAL((DECIMAL(t.npages,20,2) * ci.pctpagessaved_static / 100.0) * ts.pagesize / 1048576.0, 20, 2) AS st_mb,
        DECIMAL((DECIMAL(t.npages,20,2) * ci.pctpagessaved_adaptive / 100.0) * ts.pagesize / 1048576.0, 20, 2) AS ad_mb
    FROM syscat.tables t
    JOIN syscat.tablespaces ts
      ON ts.tbspace = t.tbspace,
         TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(t.tabschema, t.tabname)) AS ci
    WHERE t.tabschema = 'BIZ'
      AND t.tabname IN ('SALES_ORDER', 'SALES_ORDER_LINE')
      AND ci.object_type = 'DATA'
) AS src
ON b.tabname = src.tabname
WHEN MATCHED THEN
    UPDATE SET
        pre_npages = src.npages,
        pagesize = src.pagesize,
        st_pct = src.st_pct,
        ad_pct = src.ad_pct,
        st_mb = src.st_mb,
        ad_mb = src.ad_mb
WHEN NOT MATCHED THEN
    INSERT (tabname, pre_npages, pagesize, st_pct, ad_pct, st_mb, ad_mb)
    VALUES (src.tabname, src.npages, src.pagesize, src.st_pct, src.ad_pct, src.st_mb, src.ad_mb)";

db2 "
SELECT
    CAST(RTRIM(tabname) AS VARCHAR(24)) AS tab,
    CAST(pre_npages AS VARCHAR(8)) AS pre_npg,
    CAST(st_pct AS VARCHAR(6)) AS st_pct,
    CAST(ad_pct AS VARCHAR(6)) AS ad_pct,
    CAST(st_mb AS VARCHAR(10)) AS st_mb,
    CAST(ad_mb AS VARCHAR(10)) AS ad_mb
FROM biz.tab_comp_baseline
WHERE tabname IN ('SALES_ORDER', 'SALES_ORDER_LINE')
ORDER BY tabname"
db2 connect reset

Header legend:

Header	Meaning
`tab`	Table name
`pre_npg`	`NPAGES` before compression
`st_pct`	`STATIC` estimate before compression
`ad_pct`	`ADAPTIVE` estimate before compression
`st_mb`	Estimated MB saved with `STATIC` before compression
`ad_mb`	Estimated MB saved with `ADAPTIVE` before compression

Now enable compression on the tables.

cat > /tmp/complab_enable_compression.sql <<'SQL'
CONNECT TO COMPLAB;

ALTER TABLE biz.sales_order
    ALTER fraud_score COMPRESS SYSTEM DEFAULT
    COMPRESS YES ADAPTIVE
    ACTIVATE VALUE COMPRESSION;

ALTER TABLE biz.sales_order_line
    ALTER discount_pct COMPRESS SYSTEM DEFAULT
    ALTER backorder_flag COMPRESS SYSTEM DEFAULT
    ALTER fulfilled_flag COMPRESS SYSTEM DEFAULT
    COMPRESS YES STATIC
    ACTIVATE VALUE COMPRESSION;

ALTER INDEX biz.ix_order_customer_date COMPRESS YES;
ALTER INDEX biz.ix_order_status_country COMPRESS YES;
ALTER INDEX biz.ix_order_salesrep COMPRESS YES;
ALTER INDEX biz.ix_line_order COMPRESS YES;
ALTER INDEX biz.ix_line_sku COMPRESS YES;
ALTER INDEX biz.ix_line_wh_family COMPRESS YES;

COMMIT;
CONNECT RESET;
SQL

db2 -tvf /tmp/complab_enable_compression.sql

14. Materialize the compressed data

Enabling the compression clauses is not enough by itself to reformat existing data immediately. To apply the new physical organization, you need to reorganize.

cat > /tmp/complab_reorg.sql <<'SQL'
CONNECT TO COMPLAB;

REORG TABLE biz.sales_order RESETDICTIONARY;
REORG INDEXES ALL FOR TABLE biz.sales_order ALLOW WRITE ACCESS;

REORG TABLE biz.sales_order_line RESETDICTIONARY;
REORG INDEXES ALL FOR TABLE biz.sales_order_line ALLOW WRITE ACCESS;

RUNSTATS ON TABLE biz.sales_order WITH DISTRIBUTION AND DETAILED INDEXES ALL;
RUNSTATS ON TABLE biz.sales_order_line WITH DISTRIBUTION AND DETAILED INDEXES ALL;

CONNECT RESET;
SQL

db2 -tvf /tmp/complab_reorg.sql

15. Verify the real improvements, focusing on `SALES_ORDER`

First confirm that the chosen table actually moved to adaptive compression and compare the current savings with the saved baseline:

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(t.tabname) AS VARCHAR(24)) AS tab,
    CAST(b.pre_npages AS VARCHAR(8)) AS pre_npg,
    CAST(t.npages AS VARCHAR(8)) AS cur_npg,
    CAST(CASE WHEN ci.rowcompmode IN ('A', 'S') THEN ci.rowcompmode ELSE '-' END AS VARCHAR(4)) AS mode,
    CAST(b.st_pct AS VARCHAR(6)) AS pre_st,
    CAST(b.ad_pct AS VARCHAR(6)) AS pre_ad,
    CAST(ci.pctpagessaved_current AS VARCHAR(6)) AS cur_pct,
    CAST(b.ad_mb AS VARCHAR(10)) AS pre_ad_mb,
    CAST(DECIMAL
    (
        CASE
            WHEN ci.pctpagessaved_current BETWEEN 0 AND 99
            THEN ((DECIMAL(t.npages,20,4) * 100.0) / (100.0 - ci.pctpagessaved_current) - DECIMAL(t.npages,20,4)) * ts.pagesize / 1048576.0
            ELSE NULL
        END,
        20, 2
    ) AS VARCHAR(10)) AS cur_mb
FROM syscat.tables t
JOIN syscat.tablespaces ts
  ON ts.tbspace = t.tbspace
JOIN biz.tab_comp_baseline b
  ON b.tabname = t.tabname,
     TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(t.tabschema, t.tabname)) AS ci
WHERE t.tabschema = 'BIZ'
  AND t.tabname = 'SALES_ORDER'
  AND ci.object_type = 'DATA'"
db2 connect reset

The point to verify here is simple:

ROWCOMPMODE should appear as A.
PCTPAGESSAVED_CURRENT should approach the pre_ad baseline.
cur_mb should approach pre_ad_mb, within the normal variation caused by partially filled pages and the real state of the object.

Header legend:

Header	Meaning
`tab`	Table name
`pre_npg`	`NPAGES` before compression
`cur_npg`	`NPAGES` after compression
`mode`	Current row compression mode
`pre_st`	`STATIC` baseline before compression
`pre_ad`	`ADAPTIVE` baseline before compression
`cur_pct`	Current measured savings
`pre_ad_mb`	MB estimated with `ADAPTIVE` before compression
`cur_mb`	MB currently saved

Only after that does it make sense to look at the full set.

16. Validate the real result after compression

First confirm the table attributes:

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(t.tabname) AS VARCHAR(24)) AS tab,
    CAST(t.compression AS VARCHAR(4)) AS comp,
    CAST(t.tableorg AS VARCHAR(4)) AS org,
    CAST(CASE WHEN ci.rowcompmode IN ('A', 'S') THEN ci.rowcompmode ELSE '-' END AS VARCHAR(4)) AS mode,
    CAST(ci.pctpagessaved_current AS VARCHAR(6)) AS cur_pct
FROM syscat.tables t,
     TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(t.tabschema, t.tabname)) AS ci
WHERE t.tabschema = 'BIZ'
  AND t.tabname IN ('SALES_ORDER', 'SALES_ORDER_LINE')
  AND ci.object_type = 'DATA'"
db2 connect reset

Header legend:

Header	Meaning
`tab`	Table name
`comp`	Compression attribute in the catalog
`org`	Table organization
`mode`	Current row compression mode
`cur_pct`	Current measured savings

Then compare the current gain with the estimates stored in the baseline:

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(t.tabname) AS VARCHAR(24)) AS tab,
    CAST(b.pre_npages AS VARCHAR(8)) AS pre_npg,
    CAST(t.npages AS VARCHAR(8)) AS cur_npg,
    CAST(CASE WHEN ci.rowcompmode IN ('A', 'S') THEN ci.rowcompmode ELSE '-' END AS VARCHAR(4)) AS mode,
    CAST(b.st_pct AS VARCHAR(6)) AS pre_st,
    CAST(b.ad_pct AS VARCHAR(6)) AS pre_ad,
    CAST(ci.pctpagessaved_current AS VARCHAR(6)) AS cur_pct,
    CAST(b.st_mb AS VARCHAR(10)) AS pre_st_mb,
    CAST(b.ad_mb AS VARCHAR(10)) AS pre_ad_mb,
    CAST(DECIMAL
    (
        CASE
            WHEN ci.pctpagessaved_current BETWEEN 0 AND 99
            THEN ((DECIMAL(t.npages,20,4) * 100.0) / (100.0 - ci.pctpagessaved_current) - DECIMAL(t.npages,20,4)) * ts.pagesize / 1048576.0
            ELSE NULL
        END,
        20, 2
    ) AS VARCHAR(10)) AS cur_mb
FROM syscat.tables t
JOIN syscat.tablespaces ts
  ON ts.tbspace = t.tbspace
JOIN biz.tab_comp_baseline b
  ON b.tabname = t.tabname,
TABLE(SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(t.tabschema, t.tabname)) AS ci
WHERE t.tabschema = 'BIZ'
  AND t.tabname IN ('SALES_ORDER', 'SALES_ORDER_LINE')
  AND ci.object_type = 'DATA'"
db2 connect reset

The validation here is important:

sales_order should show mode = 'A' and a cur_pct closer to the pre_ad baseline than to pre_st
sales_order_line should show mode = 'S' and a cur_pct close to the pre_st baseline
If sales_order_line still shows a visible gap between cur_pct and pre_ad, that sets up the exact question for the next section: useful compression is already in place, but there may still be additional headroom with ADAPTIVE

Finally, validate index compression:

db2 connect to COMPLAB
db2 "
SELECT
    CAST(RTRIM(indname) AS VARCHAR(28)) AS idx,
    CAST(RTRIM(tabname) AS VARCHAR(24)) AS tab,
    CAST(compress_attr AS VARCHAR(4)) AS attr,
    CAST(index_compressed AS VARCHAR(4)) AS icomp,
    CAST(pct_pages_saved AS VARCHAR(8)) AS pct_saved,
    CAST(num_leaf_pages_saved AS VARCHAR(10)) AS leaf_saved
FROM TABLE
(
    SYSPROC.ADMIN_GET_INDEX_COMPRESS_INFO('', 'BIZ', '', NULL, NULL)
) AS t
WHERE tabschema = 'BIZ'
ORDER BY tabname, indname"
db2 connect reset

Header legend:

Header	Meaning
`idx`	Index name
`tab`	Base table
`attr`	Configured compression attribute
`icomp`	Current compression state
`pct_saved`	Percentage of saved leaf pages
`leaf_saved`	Number of saved leaf pages

17. Verify additional gains after compression

For a table that is already compressed, is there additional headroom if the current mode is not the best one, or if the current gain is still far from the adaptive estimate? In sales_order_line, this is the final test of the lab: static compression brought a strong gain, but there may still be additional benefit if you move to adaptive compression.

db2 connect to COMPLAB
db2 "
WITH candidates AS
(
    SELECT
        t.tabschema,
        t.tabname,
        t.npages,
        t.card,
        t.compression,
        ts.pagesize
    FROM syscat.tables t
    JOIN syscat.tablespaces ts
      ON ts.tbspace = t.tbspace
    WHERE t.tabschema = 'BIZ'
      AND t.type = 'T'
      AND t.tableorg = 'R'
      AND t.card > 0
      AND t.compression <> 'N'
)
SELECT
    CAST(RTRIM(c.tabname) AS VARCHAR(24)) AS tab,
    CAST(c.compression AS VARCHAR(4)) AS comp,
    CAST(CASE WHEN ci.rowcompmode IN ('A', 'S') THEN ci.rowcompmode ELSE '-' END AS VARCHAR(4)) AS mode,
    CAST(ci.pctpagessaved_current AS VARCHAR(6)) AS cur_pct,
    CAST(ci.pctpagessaved_static AS VARCHAR(6)) AS st_pct,
    CAST(ci.pctpagessaved_adaptive AS VARCHAR(6)) AS ad_pct,
    CAST(DECIMAL
    (
        CASE
            WHEN ci.pctpagessaved_current BETWEEN 0 AND 99
            THEN (DECIMAL(c.npages,20,4) * 100.0) / (100.0 - ci.pctpagessaved_current)
            ELSE NULL
        END,
        20, 2
    ) AS VARCHAR(12)) AS unc_pages,
    CAST(DECIMAL
    (
        CASE
            WHEN ci.pctpagessaved_current BETWEEN 0 AND 99
            THEN
                (
                    ((DECIMAL(c.npages,20,4) * 100.0) / (100.0 - ci.pctpagessaved_current))
                    * (ci.pctpagessaved_adaptive - ci.pctpagessaved_current) / 100.0
                    * c.pagesize
                ) / 1048576.0
            ELSE NULL
        END,
        20, 2
    ) AS VARCHAR(10)) AS extra_mb
FROM candidates c,
     TABLE
     (
         SYSPROC.ADMIN_GET_TAB_COMPRESS_INFO(c.tabschema, c.tabname)
     ) AS ci
WHERE ci.object_type = 'DATA'
ORDER BY c.npages DESC"
db2 connect reset

How to read the result:

If cur_pct is already close to ad_pct, the recoverable additional space will be small.
If mode = 'S' and ad_pct is clearly higher, that is a concrete case for considering adaptive compression.

Header legend:

Header	Meaning
`tab`	Table name
`comp`	Current compression attribute in the catalog
`mode`	Current row compression mode (`S`, `A`, or `-`)
`cur_pct`	Current measured savings
`st_pct`	Estimate with `STATIC`
`ad_pct`	Estimate with `ADAPTIVE`
`unc_pages`	Inferred number of uncompressed pages
`extra_mb`	Estimated additional gain in MB if you move to `ADAPTIVE`

18. Summary

This lab showed several important operational points:

Compression for row-organized tables in Db2 is not a single feature. It involves classic static compression, adaptive compression, and value compression.
ADMIN_GET_TAB_COMPRESS_INFO and ADMIN_GET_INDEX_COMPRESS_INFO let you estimate compression gains.
Not every table should enter the analysis. In this lab, one table was excluded because it was too small and another because it was marked VOLATILE.

A sensible sequence is usually:

Filter candidates based on size, volatility, and usage pattern
Estimate gains for tables and indexes
Enable compression only on objects with a clear return
Reorganize and gather statistics
Measure again

19. Cleanup

When you finish the lab, remove the database and the temporary files:

db2 force applications all
db2 drop database COMPLAB
rm -f /tmp/complab_schema.sql \
      /tmp/complab_load_customers.sql \
      /tmp/complab_load_orders.sql \
      /tmp/complab_load_lines.sql \
      /tmp/complab_load_auxiliary.sql \
      /tmp/complab_runstats_before.sql \
      /tmp/complab_enable_compression.sql \
      /tmp/complab_reorg.sql

Useful IBM documentation

For deeper reading, the most useful IBM references for this topic are:

Menu

Hands-on lab for table and index compression in Db2

1. Lab goals

2. Required software and how to obtain it

3. Current compression options for row-organized tables

What distinguishes `STATIC` from `ADAPTIVE`

`COMPRESS YES STATIC`

`COMPRESS YES ADAPTIVE`

What about column-organized tables?

4. Create the lab database

5. Create the schema objects

6. Generate and load representative data

6.1 Load `customer_master`

6.2 Load `sales_order`

6.3 Load `sales_order_line`

6.4 Load the small table and the `VOLATILE` table

7. Gather statistics and establish a baseline

8. Estimate compression for tables with no active compression

9. Schema-level estimate with prior filtering

10. Compare `STATIC` and `ADAPTIVE` before compression

11. Identify explicitly where `ADAPTIVE` brings an advantage

12. Estimate gains for uncompressed indexes

13. Enable compression on the table and the indexes

14. Materialize the compressed data

15. Verify the real improvements, focusing on `SALES_ORDER`

16. Validate the real result after compression

17. Verify additional gains after compression

18. Summary

19. Cleanup

Useful IBM documentation

More in this area

Hands-on lab for table and index compression in Db2

1. Lab goals

2. Required software and how to obtain it

3. Current compression options for row-organized tables

What distinguishes STATIC from ADAPTIVE

COMPRESS YES STATIC

COMPRESS YES ADAPTIVE

What about column-organized tables?

4. Create the lab database

5. Create the schema objects

6. Generate and load representative data

6.1 Load customer_master

6.2 Load sales_order

6.3 Load sales_order_line

6.4 Load the small table and the VOLATILE table

7. Gather statistics and establish a baseline

8. Estimate compression for tables with no active compression

9. Schema-level estimate with prior filtering

10. Compare STATIC and ADAPTIVE before compression

11. Identify explicitly where ADAPTIVE brings an advantage

12. Estimate gains for uncompressed indexes

13. Enable compression on the table and the indexes

14. Materialize the compressed data

15. Verify the real improvements, focusing on SALES_ORDER

16. Validate the real result after compression

17. Verify additional gains after compression

18. Summary

19. Cleanup

Useful IBM documentation

More in this area

Db2 LUW Articles

What distinguishes `STATIC` from `ADAPTIVE`

`COMPRESS YES STATIC`

`COMPRESS YES ADAPTIVE`

6.1 Load `customer_master`

6.2 Load `sales_order`

6.3 Load `sales_order_line`

6.4 Load the small table and the `VOLATILE` table

10. Compare `STATIC` and `ADAPTIVE` before compression

11. Identify explicitly where `ADAPTIVE` brings an advantage

15. Verify the real improvements, focusing on `SALES_ORDER`