DB2 Simple Tablespace

What is a Simple Tablespace?

A simple tablespace is a basic type of tablespace that stores tables and their data without complex features like partitioning or segmentation. It is one of the types of tablespaces available in DB2. However, it is less commonly used today due to limitations in flexibility and performance compared to newer tablespace types.

A simple tablespace is a basic storage container that holds tables and their data pages in a straightforward manner. Unlike other tablespace types, such as segmented or partitioned, a simple tablespace:

• Does not support advanced data organization (like segmentation or partitioning).
• Can store multiple tables within a single tablespace, which may lead to potential fragmentation over time.
• Has limited flexibility in managing storage, making it less ideal for large or complex applications.

Key Characteristics:

• Straightforward structure: Ideal for simple use cases without complex storage requirements.
• Shared storage: Multiple tables can be stored within a single simple tablespace, which may cause space management issues.
• Page-level locking: Supports page-level locking for data access control.

Creating a Simple Tablespace

To create a simple tablespace in DB2, we use the CREATE TABLESPACE statement with the SIMPLE type (implicitly defined as the default when no segmentation or partitioning is specified).

Syntax -

Refer Table Space syntax for all the parameters and its meaning.

Examples - Creating a Simple Tablespace

CREATE TABLESPACE EMPLTSSM
IN EMPLDB
USING STOGROUP EMPLSG
PRIQTY 500
SECQTY 100
BUFFERPOOL BP0
CLOSE YES;

• Creates a simple tablespace named EMPLTSSM within the EMPLDB database.
• Uses EMPLSG to allocate storage volumes.
• Allocates 500 KB of primary space and 100 KB of secondary space if more is needed.
• Utilizes buffer pool BP0 to optimize data access.
• Sets the tablespace to close when not in use, freeing resources.

Altering a Simple Tablespace

To modify an existing simple tablespace, use the ALTER TABLESPACE command. We can adjust parameters like primary and secondary space allocations, change the buffer pool, or set the tablespace to open or close when not in use.

Examples - Altering a Simple Tablespace

ALTER TABLESPACE EMPLTSSM
PRIQTY 600
SECQTY 150
BUFFERPOOL BP1
CLOSE NO;

• Increases primary space to 600 KB.
• Updates secondary space allocation to 150 KB.
• Changes buffer pool to BP1.
• Keeps the tablespace open even when not in use.

Deleting a Simple Tablespace

To delete a simple tablespace, use the DROP TABLESPACE statement. This action removes the tablespace and any tables or indexes stored within it.

Examples - Deleting a Simple Tablespace

DROP TABLESPACE EMPLTSSG;