Advanced DB2 table options

Why these options exist

Column definitions answer “what is a row?” These clauses answer “how should Db2 lay rows down, find them, and record that someone changed them?” Getting them wrong does not always fail CREATE TABLE—it fails six months later as CPU, clustering, or a replication gap.

COMPRESS

COMPRESS YES stores rows in a compressed form on disk, using a dictionary (and, on recent releases, optional Huffman / fixed-length algorithms: COMPRESS YES HUFFMAN or FIXEDLENGTH on the table space). You usually set compression on CREATE TABLESPACE. When CREATE TABLE implicitly creates a PBG space, a table-level COMPRESS clause can apply to that space.

• Saves DASD and can reduce I/O for scans
• Costs CPU to compress and expand; measure with accounting traces
• Dictionary build often needs REORG or LOAD to become effective
• Very short rows may not compress enough to help

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLESPACE ORDTS
  IN ORDDB
  MAXPARTITIONS 8
  SEGSIZE 32
  COMPRESS YES;
 
CREATE TABLE ORD.ORDERS (
    ORDNO INTEGER NOT NULL,
    ...
  )
  IN ORDDB.ORDTS;

Partitioning

Table-level PARTITION BY is how a PBR table declares key ranges. Each PARTITION n ENDING AT (value) sets the high key for that part. MAXVALUE is the last part. Columns in the partition key are usually leading columns of a partitioned index.

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE APP.ACTIVITY (
    CUSTNO        INTEGER NOT NULL,
    LAST_ACTIVITY DATE,
    COL2          CHAR(10),
    CONSTRAINT PK_ACT PRIMARY KEY (CUSTNO)
  )
  IN DBB.TS01
  PARTITION BY RANGE (CUSTNO)
    (PARTITION 1 ENDING AT (999),
     PARTITION 2 ENDING AT (1999),
     PARTITION 3 ENDING AT (2999),
     PARTITION 4 ENDING AT (MAXVALUE));

Without a range key, implicit or explicit PBG partitioning grows by size instead of by key. ALTER PARTITION / ROTATE PARTITION apply to range-partitioned tables, not to PBG. Choose the partitioning key for utilities (REORG PART, COPY PART) and for query pruning, not only for “it looks neat.”

Hash organization

Hash organization places a row by hashing a unique hash key into a preallocated HASH SPACE, aiming for roughly one GETPAGE to fetch by that key. Syntax families you will see include ORGANIZE BY HASH and HASH SPACE on CREATE TABLE (exact clause names follow your SQL Reference). Requirements typically include a unique hash key and a fixed hash space size.

Hash shines for random singleton lookups. It can lose to a clustering index plus sequential detection when a batch file of keys is sorted into cluster order. Hash is not the default for new OLTP tables in most shops; treat it as a measured exception. Some later Db2 directions de-emphasise hash versus UTS + good indexes—check your version’s restrictions before designing a new hash table.

Volatile tables

VOLATILE (as opposed to NOT VOLATILE) tells the optimizer that cardinality is unstable or that index access should be preferred whenever possible. RUNSTATS might say “tiny table, scan it”; VOLATILE says “still use the index.” Typical use: small reference tables that spike, or tables you must not tablespace-scan.

1
2
3
4
5
6
7
8
9
CREATE TABLE APP.CODETAB (
    CODE  CHAR(4)  NOT NULL PRIMARY KEY,
    DESCR VARCHAR(40) NOT NULL
  )
  IN APPDB.CDTS
  VOLATILE;
 
ALTER TABLE APP.CODETAB VOLATILE;
ALTER TABLE APP.CODETAB NOT VOLATILE;

VOLATILE is not the same as a created global temporary table or a volatile table in other database products. The data is still persistent; only optimization bias changes.

Append

APPEND YES skips the search for a page that preserves clustering. Db2 appends inserted (and loaded) rows toward the end of the table space. Inserts can be cheaper and more concurrent; the clustering index becomes less representative until you REORG. APPEND NO (default) tries to insert near the cluster-key neighbours.

Use APPEND YES for high-volume insert tables that you REORG regularly, or when clustering is irrelevant. Do not combine it with a hope that range scans on the cluster key stay perfectly sequential without maintenance.

1
ALTER TABLE APP.EVENT_LOG APPEND YES;

DATA CAPTURE

DATA CAPTURE NONE (default) logs the usual undo/redo information. DATA CAPTURE CHANGES logs extra details so capture programs (SQL replication, IBM InfoSphere CDC, and similar) can reconstruct full row images without always hitting the table. Cost is more log volume. Catalog tables have special rules; DATA CAPTURE CHANGES may be the only ALTER allowed on some catalog tables.

If RESTRICT_ALT_COL_FOR_DCC is YES, some column ALTERs are blocked while DATA CAPTURE CHANGES is on. Plan replication requirements before you freeze the table in that mode.

AUDIT

AUDIT controls whether Db2 writes audit trace/SMF information for the table:

• NONE — no table-level audit (default)
• CHANGES — audit when the table is changed
• ALL — audit reads and changes (heavier)

Auditing is a compliance switch, not a substitute for RACF. It can generate significant SMF. Turn it on for sensitive tables with a plan to collect and retain the records.

1
2
3
4
5
6
7
CREATE TABLE SEC.PAYROLL (
    EMPNO CHAR(6) NOT NULL PRIMARY KEY,
    SALARY DECIMAL(9,2)
  )
  IN SECDB.PAYTS
  AUDIT CHANGES
  DATA CAPTURE CHANGES;

Putting options together

A high-insert event table might be PBG, APPEND YES, COMPRESS YES, AUDIT NONE, DATA CAPTURE NONE. A parent customer table might be PBR on CUSTNO, APPEND NO, VOLATILE NO, DATA CAPTURE CHANGES if it is replicated, AUDIT CHANGES if privacy rules require it. Hash organization would only appear if measurements beat a unique clustering index for that access pattern.

Explain It Like I'm Five

COMPRESS is stuffing winter coats into vacuum bags so the closet holds more. Partitioning is giving each grade its own classroom so you can clean one room without closing the school. Hash organization is putting each backpack on the hook whose number you get by mixing up the nametag—fast if you know the nametag, messy if you wanted them in ABC order. VOLATILE is telling the librarian “always use the card catalog, even if the shelf looks small.” APPEND is tossing new books on the end of the shelf instead of sliding them into ABC order. DATA CAPTURE is keeping a carbon copy of every change for someone down the hall. AUDIT is the hall monitor writing down who opened the closet.

Exercises

1. Write PARTITION BY RANGE for a table keyed on SALEYEAR with parts 2022, 2023, 2024, and MAXVALUE.
2. When would you set APPEND YES on an event log table?
3. Explain DATA CAPTURE CHANGES vs AUDIT CHANGES in one sentence each.
4. Give a good use of VOLATILE and a bad use.
5. Why might hash organization lose to a clustering index for a sorted batch job?

Frequently asked questions