DB2 table spaces (DDL)

TABLE SPACE in DDL

CREATE TABLESPACE names a space inside a database (IN dbname, default often DSNDB04). USING STOGROUP or USING VCAT says who allocates the data set. One modern universal table space holds one table. That 1:1 rule is the biggest mental shift from old simple spaces that mixed many tables in one VSAM cluster.

1
2
3
4
5
6
7
8
CREATE TABLESPACE EMPTS
  IN HRDB
  USING STOGROUP HRSTO
  MAXPARTITIONS 32
  SEGSIZE 32
  DSSIZE 4G
  BUFFERPOOL BP2
  LOCKSIZE ROW;

Types you will still see

Simple table spaces

Simple table spaces could hold multiple tables with no segmented space map. Mass delete and space reuse were painful. They are deprecated for new work. If you inherit one, the long-term plan is usually to move the table into a UTS (often via pending ALTER + REORG, or UNLOAD/LOAD).

Segmented table spaces

Classic segmented spaces use a segment size (SEGSIZE) so each table owns whole segments. Space reuse and DROP/mass delete are better than simple spaces. They can still hold more than one table. New objects should be UTS instead: you keep segmented management inside each partition without sharing the space among tables.

CREATE TABLESPACE with SEGSIZE and without NUMPARTS or MAXPARTITIONS is the classic segmented (non-UTS) path. Do not copy that pattern from twenty-year-old DDL generators without checking shop standards.

Universal table spaces

Universal table spaces (UTS) combine partitioned structure with segmented space management, one table per space:

• Partition-by-growth (PBG) — MAXPARTITIONS + SEGSIZE. Db2 adds a partition when the current one fills. No range key required.
• Partition-by-range (PBR) — NUMPARTS + SEGSIZE, no MAXPARTITIONS. Partitions follow key ranges on CREATE TABLE PARTITION BY.

PBG is the usual implicit space for CREATE TABLE. PBR is the usual choice for large tables with a natural key (account, date, region). Details and logging options belong on the PBG/PBR design page; here remember the DDL knobs that select the type.

Partitioned table spaces (classic)

NUMPARTS without SEGSIZE creates a classic partitioned table space (not UTS). Range partitioning without segmented pages inside the part. New designs should add SEGSIZE so you get PBR UTS instead.

LOB table spaces and XML table spaces

LOB table spaces (CREATE TABLESPACE ... LOB) hold auxiliary data for CLOB, BLOB, and DBCLOB columns. The base table keeps a ROWID (and inline bytes if you defined inline LOBs). XML table spaces similarly hold XML storage for XML columns. You do not SELECT from these spaces as if they were the base table; Db2 names them and you REORG/COPY them as part of the table’s storage set. Logging and some attributes are inherited from the base table space.

Table space page sizes

Page size is chosen by BUFFERPOOL:

• 4 KB — BP0, BP1, BP2, … typical default
• 8 KB — BP8K0 …
• 16 KB / 32 KB — wide rows, some XML/LOB inline patterns

A row cannot span pages in the ordinary base-table sense (except the overflow/pointer mechanisms for varying rows). If the maximum row length will not fit, CREATE TABLE fails or you must raise the page size. Compression and MAXROWS also interact with page size. Changing BUFFERPOOL on an existing UTS is often a pending definition change materialized by REORG.

DSSIZE

DSSIZE is the data set size for each partition, specified in gigabytes (for example DSSIZE 4G). Each partition occupies one data set (more pieces can exist for non-partitioned index spaces via PIECESIZE, which is an index topic). Total PBG capacity is roughly MAXPARTITIONS × DSSIZE, further limited by page size. IBM notes PBG can grow very large (up to the documented 128 TB class ceiling depending on page size and those two knobs) but recommends PBR once you expect far beyond tens of gigabytes and have a partitioning key.

PBR with relative page numbering (RPN) allows more flexible per-part DSSIZE growth than absolute page numbering. PAGENUM RELATIVE vs ABSOLUTE is specified on CREATE TABLESPACE or taken from PAGESET_PAGENUM.

MAXPARTITIONS, SEGSIZE, and NUMPARTS

• MAXPARTITIONS — ceiling on how many partitions a PBG space may acquire as it grows. Start with a realistic number; increasing it can be a pending ALTER.
• SEGSIZE — pages per segment, multiple of 4, range 4–64. Larger segments can help sequential insert; 32 is a common modern default. Required for UTS.
• NUMPARTS — number of range partitions for PBR (with SEGSIZE) or classic partitioned (without SEGSIZE).

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- PBR UTS: range parts + segmented pages
CREATE TABLESPACE TS1
  IN DSN8D12A
  USING STOGROUP DSN8G120
  NUMPARTS 55
  SEGSIZE 16
  LOCKSIZE ANY;
 
-- PBG UTS: growth parts
CREATE TABLESPACE TSG
  IN DSN8D12A
  USING STOGROUP DSN8G120
  MAXPARTITIONS 20
  SEGSIZE 32
  DSSIZE 4G;

You can also skip CREATE TABLESPACE and use CREATE TABLE PARTITION BY RANGE (...), which creates a PBR space for you, or a plain CREATE TABLE that implicitly creates PBG. Explicit DDL is clearer in production reviews.

Explain It Like I'm Five

A table space is the notebook the table writes in. Simple notebooks let every subject share one messy book. Segmented notebooks give each subject its own chunks of pages. Universal notebooks are fancy: either the notebook grows a new chapter when it runs out of paper (PBG) or you decide “chapter 1 is A–M and chapter 2 is N–Z” (PBR). DSSIZE is how thick each chapter is allowed to be. SEGSIZE is how many pages you buy in a pack. LOB/XML notebooks are extra sketchbooks for huge drawings that do not fit on a normal ruled page.

Exercises

1. Write CREATE TABLESPACE for a PBG space with MAXPARTITIONS 16, SEGSIZE 32, DSSIZE 4G.
2. Change that design to PBR with 12 parts. Which clause appears and which disappears?
3. Why would you pick BP8K0 instead of BP2?
4. Explain why a CLOB column needs a LOB table space in addition to the base UTS.
5. What type do you get if you specify NUMPARTS but forget SEGSIZE?

Frequently asked questions