Module 7: DB2 Tables and Tablespaces

Tablespace Types

DB2 has four types of tablespaces. The type decides how many tables it can hold and how the space is organized.

Simple is the default type. One simple tablespace can store more than one table.
Rows of different tables are mixed together on the same pages.
Depending on the application, storing more than one table might enable faster retrieval for joins using these tables.
Consider it for highly related tables, where table rows are inserted and deleted together and accessed together.

The tablespace is divided into equal segments. Each segment can hold only one table.
A segment consists of a logically contiguous set of n pages, where n is from 4 to 64.
The SEGSIZE parameter decides the allocation size for the tablespace. No segment is allowed to contain records for more than one table.
Segmented tablespaces are ideal for storing more than one table, especially relatively small tables.
Sequential access and mass delete of a particular table are more efficient, because DB2 can drop whole segments.
LOCK TABLE on a table locks only the table, not the entire tablespace.
A segmented tablespace is created with the SEGSIZE clause:-
CREATE TABLESPACE SEGTS
   IN MM01DB
   USING STOGROUP SYSDEFLT
   SEGSIZE 16
   PRIQTY 100
   SECQTY 100;

A partitioned tablespace is primarily used for very large tables.
The entire tablespace can hold only one table, divided into partitions.
Utilities can be run on one partition at a time, and individual partitions can be independently recovered and reorganized.
Partitioned tablespaces are explained in detail on the next page.

A universal tablespace is the modern type, available from DB2 9 for z/OS.
It comes in two flavors: partition-by-growth and partition-by-range.
Universal tablespaces combine the easy growth of segmented spaces with the partition-level utilities of partitioned spaces.
Universal tablespaces are explained in detail two pages ahead.