Db2 table spaces

What a table space stores

A Db2 table space is a set of disk volumes’ worth of data sets that actually hold table data. Every table lives in a table space. Physically, a table space is a page set of VSAM linear data sets. The space is divided into equal-sized pages. Db2 reads a page from disk into a buffer pool, or writes a page from the buffer pool to disk, as a unit.

Page size is controlled by the buffer pool assigned to the table space: 4 KB (default), 8 KB, 16 KB, or 32 KB. Data in most table spaces can be compressed, packing more rows per page. Indexes live in related index spaces, not inside the table’s data pages—but they share the same database administrative world.

1
2
3
4
5
-- Explicit placement: database + table space
CREATE TABLE HR.EMPLOYEE (
    EMPNO CHAR(6) NOT NULL,
    PRIMARY KEY (EMPNO)
) IN APPDB.EMPTS;

When you see IN APPDB.EMPTS, APPDB is the database and EMPTS is the table space that will hold EMPLOYEE’s rows. You can also omit an existing space on CREATE TABLE and let Db2 implicitly create a partition-by-growth or partition-by-range table space—common for quick work, less ideal when standards demand named spaces.

Pages vs rows

A row is a logical unit in SQL. A page is a physical I/O unit. Many rows usually share a page. That is why REORG, free space, and compression show up in DBA conversations even when your SELECT only names columns.

Segmented, partitioned, and universal table spaces (intro)

Universal table spaces (recommended)

Universal table spaces (UTS) combine benefits of partitioning and segmented organization. Each UTS always contains only a single table. Two main flavors:

• Partition-by-growth (PBG) — Db2 adds partitions as the table grows (growth-based partitioning), controlled with options such as MAXPARTITIONS
• Partition-by-range (PBR) — partitions follow ranges of key values (NUMPARTS and related design), useful when you want range-based utilities and data placement

UTS advantages (vs older non-UTS types) include better space management for varying-length rows, stronger mass-delete behavior in segmented-style organization, localized table scans within segments, and faster reuse of space after drops or mass deletes.

Segmented (non-UTS) — legacy awareness

A segment is a group of pages that holds rows of a single table; all segments in the space are the same size. Deprecated segmented (non-UTS) table spaces could contain more than one table. You may still meet them in older applications. At modern application compatibility levels, CREATE TABLESPACE creates UTS, and CREATE TABLE into non-UTS spaces errors—except special recovery-oriented cases with lower applcompat.

Partitioned — the idea

A partition is a page set corresponding to a data set that can be processed or extended more independently. Large tables can spread partitions across storage groups or device types, run utilities per partition, and enable parallel query I/O. Classic non-UTS partitioned spaces are deprecated; PBR/PBG carry partitioning forward inside UTS.

1
2
3
4
5
-- Mental model: create a space in a database (details vary by type)
CREATE TABLESPACE EMPTS IN APPDB
  USING STOGROUP APPSTO
  PRIQTY 100 SECQTY 100
  DEFINE YES;

Exact CREATE TABLESPACE clauses depend on whether you want PBG, PBR, or (legacy) other types. Treat this page as vocabulary; deeper DDL pages cover every keyword.

Why beginners care about table spaces

You can write excellent SELECT statements and still be blocked because a table space is stopped, in copy-pending, or being REORGed. Error messages and DBA chat often name the space, not only the table. Knowing the hierarchy—database → table space → table—lets you ask better questions: “Which space is EMP in?” “Is it PBG or PBR?” “What page size / buffer pool?”

• Developers — understand IN clauses and why implicit spaces appear in sandboxes
• Operators — start/stop and display at database/space scope
• DBAs — choose type, partitioning, STOGROUP, and buffer pool for growth and recovery

One table per modern space

Plan new designs around one base table per UTS. That simplifies utilities and matches IBM’s current guidance. If a shop still packs multiple tables into an old segmented space, treat it as technical debt, not a pattern to copy.

Explain It Like I'm Five

A table is a list of toys you care about by name. A table space is the box those toys live in. Grown-ups move whole boxes to shelves (disk) or to a table (buffer pool) when you want to play. Fancy boxes can have dividers (partitions) so big collections stay organized. New boxes usually hold only one kind of toy set; old boxes sometimes mixed toys—that mixing is going away.

Exercises

1. In one sentence each, define database, table space, and table.
2. Why might COPY TABLESPACE matter to an application team even if they never run utilities?
3. Contrast PBG and PBR in a single sentence suitable for a standup update.
4. Give one reason multi-table segmented spaces are discouraged for new work.
5. What happens conceptually when Db2 needs a row whose page is not already in a buffer pool?