DB2 Segmented Tablespace

What is a Segmented Tablespace?

A segmented tablespace divides the storage into segments that are allocated to one or more tables. Each segment contains a fixed number of pages, and DB2 allocates segments exclusively for a single table, which means that data from different tables cannot share the same segment. This segmentation is optimized for tables with frequent row insertions, updates, and deletions. This design minimizes fragmentation, reduces wasted space, and improves access performance for multi-table environments.

Key Characteristics:

• Segmented Storage: Divides the tablespace into segments, each containing a set of pages.
• Single-Table Segments: Each segment is assigned to a single table, ensuring data from different tables is isolated.
• Reduced Fragmentation: Minimizes space wastage by grouping pages for each table together within segments.
• Optimized for Frequent Data Changes: Works well for tables with high insert, update, and delete activities.

Creating a Segmented Tablespace

To create a segmented tablespace, use the CREATE TABLESPACE statement, specifying the segmented type by including the SEGSIZE parameter, which defines the number of pages per segment.

Syntax -

CREATE TABLESPACE tablespace_name
...
BUFFERPOOL bufferpool_name
...;

• SEGSIZE: The number of pages per segment, defining the segmented nature of the tablespace.

Examples - Creating a Segmented Tablespace

CREATE TABLESPACE EMPLTSSM
IN EMPLDB
USING STOGROUP EMPLSG
PRIQTY 1000
SECQTY 200
BUFFERPOOL BP1
SEGSIZE 16
LOCKSIZE ROW
CLOSE YES;

• Creates a segmented tablespace named EMPLTSSM in the EMPLDB database.
• Uses EMPLSG as the storage group.
• PRIQTY and SECQTY specify primary (1000 KB) and secondary (200 KB) space allocation.
• Utilizes buffer pool BP1 to optimize data access.
• Defines each segment to contain 16 pages.
• Specifies row-level locking (LOCKSIZE ROW) for finer control over data access.
• Sets the tablespace to close when not in use (CLOSE YES), freeing resources.

Altering a Segmented Tablespace

To modify a segmented tablespace, use the ALTER TABLESPACE statement. This allows adjustments to storage allocations, buffer pools, and closing behavior.

Examples - Altering a Segmented Tablespace

ALTER TABLESPACE EMPLTSSM
PRIQTY 1200
SECQTY 300
BUFFERPOOL BP2
LOCKSIZE PAGE;

• Increases primary space allocation to 1200 KB and secondary space allocation to 300 KB.
• Changes buffer pool to BP2 for optimized data access.
• Sets locking level to page-level locking (LOCKSIZE PAGE).

Deleting a Segmented Tablespace

To delete a segmented tablespace, use the DROP TABLESPACE statement. This removes the tablespace and all associated data within it.

Examples - Deleting a Segmented Tablespace

DROP TABLESPACE SEGMENTED_TS;

Deletes the SEGMENTED_TS tablespace, along with all associated tables and indexes.