Database Design, Objects, and Schema Management
This domain covers physical design in Db2 12: table spaces, indexes, compression, temporal and generated objects, and schema changes a DBA can make online.
Table spaces and indexes
- Universal table spaces are partition-by-range (PARTITION BY RANGE, NUMPARTS) or partition-by-growth (MAXPARTITIONS). PBG handles unpredictable growth; PBR suits purge and roll-in by range and partition-level utilities.
- Choose page size and DSSIZE before data grows. LOBs need their own LOB table spaces and auxiliary tables. Indexes can be clustering, partitioning, or data-partitioned secondary indexes (DPSI).
- Compression choices include classic row compression, Huffman, and page-level compression. Compression trades CPU for space; test with real data.
Schema management
- ALTER TABLE can add columns, change data types within limits, and add partitions. Some changes leave the table in REORG-pending or advisory REORG-pending; check SYSIBM.SYSTABLESPACE and SYSIBM.SYSINDEXES.
- Temporal tables, identity columns, sequences, generated columns, triggers, views, and MQTs are schema objects with dependencies.
Worked example
A table has a growing timestamp key and no obvious purge boundary. A DBA chooses PBG with MAXPARTITIONS high enough for growth and a suitable DSSIZE, rather than PBR with NUMPARTS, because PBR needs partition boundaries and a roll-in/roll-out plan. Reasoning: PBG adds partitions automatically, while PBR requires explicit ALTER ADD PARTITION and careful range planning.
Traps
- Some ALTERs leave pending states; plan for REORG before assuming the change is complete.
- Implicit table space creation hides bad page size or LOCKSIZE choices.
- Adding a DPSI or changing clustering can invalidate packages; rebind as needed.