DB2 table space design options (PBG, PBR, logging)

PBG — partition-by-growth

A partition-by-growth table space is a UTS whose partitions Db2 manages as data grows. One table, segmented pages inside each partition. When an insert needs space past the current partition’s DSSIZE, Db2 adds a partition, until MAXPARTITIONS is reached. No range key is required.

• Best for small and medium tables, or tables with no useful partitioning column
• Partition-level utilities and parallelism still exist, but you do not choose which key lives in which part
• Partitioned indexes are not supported; REORG REBALANCE is not; LOAD PART is not; ALTER/ROTATE PARTITION is not
• Space must be Db2-managed so Db2 can create new data sets as parts fill
• If you expect far more than ~64 GB, IBM suggests considering PBR instead

1
2
3
4
5
6
7
8
9
CREATE TABLESPACE CUSTTS
  IN APPDB
  USING STOGROUP APPSTO
  MAXPARTITIONS 25
  SEGSIZE 32
  DSSIZE 4G
  LOGGED
  DEFINE YES
  TRACKMOD YES;

PBR — partition-by-range

A partition-by-range UTS stores one table with segmented organization inside parts that follow key ranges (CREATE TABLE PARTITION BY RANGE). CREATE TABLESPACE uses NUMPARTS and SEGSIZE and omits MAXPARTITIONS.

PBR can use absolute or relative page numbering (RPN). RPN supports larger, more flexible DSSIZE—including increasing DSSIZE on a single part as an immediate ALTER in documented cases. PAGENUM RELATIVE is the modern default on many subsystems (PAGESET_PAGENUM).

1
2
3
4
5
6
7
8
CREATE TABLESPACE SALETS
  IN APPDB
  USING STOGROUP APPSTO
  NUMPARTS 12
  SEGSIZE 32
  DSSIZE 8G
  PAGENUM RELATIVE
  LOGGED;

LOGGED and NOT LOGGED

LOGGED (default) writes data changes to the recovery log. Indexes and XML/LOB auxiliary objects inherit the logging attribute of the base table space. NOT LOGGED skips that logging. You cannot specify either on table spaces in DSNDB06.

NOT LOGGED is for tables you can reload or recopy if something fails—work tables, staging, some load pipelines. After NOT LOGGED activity the space is often in a recover-pending-like situation for log-based recovery; you rely on a new image copy or a replace load. Turning logging back on with ALTER ... LOGGED is a careful operation (usually with a copy). Never make your system of record NOT LOGGED because inserts looked faster in a unit test.

TRACKMOD, DEFINE, DEFINE NO

TRACKMOD YES maintains modified-page bits in space maps so incremental COPY can find changed pages. TRACKMOD NO avoids that CPU and contention; incremental copy is less effective. High-insert data-sharing tables sometimes use TRACKMOD NO plus more frequent full copies.

DEFINE YES (default) defines the VSAM data set when you CREATE the space. DEFINE NO waits until the first SQL insert or LOAD that needs the data set. DEFINE NO saves empty clusters for hundreds of partitions you might not populate yet. First-touch allocation then happens on an application thread—coordinate with SMS and your error handling. DEFINE NO versus DEFINE YES can also be altered later with rules.

MEMBER CLUSTER, MAXROWS, GBPCACHE, ERASE

• MEMBER CLUSTER YES — UTS in data sharing; members tend to insert into different page ranges, reducing space-map hot spots. Clustering for sequential read by cluster key is weaker. Specify only on PBG/PBR (or pending convert-to-UTS).
• MAXROWS — maximum rows per page (1–255). Leaving free slots helps later UPDATEs that lengthen varying rows, at the cost of density.
• GBPCACHE — CHANGED, ALL, SYSTEM, or NONE: which pages go to the group buffer pool in data sharing. Wrong GBPCACHE is a performance and coherency conversation with your sysprog, not a casual CREATE option.
• ERASE YES — overwrite Db2-managed data sets when the space is dropped (security). ERASE NO is faster drop.

Pending definition changes

On universal table spaces, many ALTER TABLESPACE options do not rewrite pages in place. Semantic checking happens immediately; the catalog grows a SYSIBM.SYSPENDINGDDL row; the object is AREOR (advisory REORG-pending). SQL continues. When you are ready, REORG TABLESPACE SHRLEVEL CHANGE or REFERENCE materializes BUFFERPOOL, DSSIZE, SEGSIZE, MAXPARTITIONS increases, MEMBER CLUSTER flips, convert-to-UTS, and similar pending items.

• SHRLEVEL NONE REORG does not apply pending changes
• DROP PENDING CHANGES on ALTER TABLESPACE forgets unapplied ALTERs
• Pending support is a UTS feature—classic segmented/simple spaces need immediate ALTERs or unload/reload
• Some later ALTERs fail with -20385 while pending changes exist

1
2
3
4
5
ALTER TABLESPACE APPDB.CUSTTS
  BUFFERPOOL BP8K0
  MAXPARTITIONS 40;
-- Object is AREOR until:
--   REORG TABLESPACE APPDB.CUSTTS SHRLEVEL CHANGE

REORG-pending state

REORP (restrictive REORG-pending) is different from AREOR. REORP means you must REORG (often SHRLEVEL REFERENCE on the whole space or affected parts) before SQL can use the object normally—classic example: adding a partition in a way that leaves keys out of order, or completing some recoveries. DISPLAY DATABASE shows REORP versus AREOR. Application symptoms are unavailable-resource errors, not “it is just slow.”

Materializing pending changes clears AREOR and SYSPENDINGDDL for those objects. If both table space and index pending changes exist, REORG the table space so both apply.

Explain It Like I'm Five

PBG is a stack of notebooks: when the last notebook is full, the teacher hands you a new one automatically, until you hit the maximum stack height. PBR is notebooks labelled A–G and H–Z—you always know which book a name belongs in. LOGGED is keeping a diary of every scribble so you can undo it; NOT LOGGED is scribbling on a whiteboard you will wipe and rewrite from a photocopy. DEFINE NO is not buying the notebook until the first child actually needs to write. Pending changes are “we ordered new covers, but they only go on when we REORG-tidy the shelf.” REORP is “this notebook is taped shut until you tidy it.” AREOR is “you can still write, but the new covers are in a box in the office.”

Exercises

1. Pick PBG or PBR for a 2 GB code table and for a 4 TB account-history table keyed by account number. Explain.
2. Write CREATE TABLESPACE clauses for NOT LOGGED, DEFINE NO, and TRACKMOD NO, and say when that combination is acceptable.
3. What command and SHRLEVEL apply a pending BUFFERPOOL change?
4. Why would a data-sharing shop consider MEMBER CLUSTER YES?
5. How do you tell AREOR from REORP on DISPLAY DATABASE, and which one still allows SQL?

Frequently asked questions