DB2 - Tablespaces

Db2 - Tablespaces

This chapter explains tablespaces, the fundamental storage management structure in Db2 that bridges the gap between logical database objects and physical disk storage.

Introduction to Tablespaces

A tablespace is a logical storage layer within a database that contains objects like tables, indexes, and large objects (LOBs). It acts as an organizational container, grouping related data together. The primary function of a tablespace is to map these logical database objects to the physical storage units, known as containers.

Benefits of Using Tablespaces

• Performance Tuning: You can place different types of data (e.g., tables vs. indexes) or data with different access patterns (e.g., OLTP vs. warehouse) in separate tablespaces. These can then be assigned to different buffer pools or physical disks to optimize I/O performance.
• Storage Management: Tablespaces provide a flexible way to manage how and where your data is stored on the file system.
• Backup and Recovery: You can back up and restore individual tablespaces, which can be much faster than operating on the entire database for very large databases (VLDBs).

Storage Management: The Modern Approach

Automatic Storage Tablespaces (Best Practice)

Automatic Storage is the modern, default, and highly recommended method for managing tablespaces in Db2. When you create a database with automatic storage enabled (which is the default), you define a set of storage paths for the database. From then on, Db2 automatically manages the creation, extension, and addition of containers within these paths as your data grows.

With Automatic Storage Tablespaces, you no longer need to manually define container paths, sizes, or extensions. This greatly simplifies database administration, reduces the risk of ‘tablespace full’ errors, and allows the database to efficiently use all allocated storage. To add more space, you simply add a new path to the database storage group, and Db2 will begin using it automatically.

Legacy Storage Management (Deprecated)

In older versions of Db2, two other types of tablespaces were common. These are now deprecated for user-defined tablespaces and should not be used for new development, though they still exist for system-managed temporary tablespaces and for backward compatibility.

• System Managed Space (SMS): The operating system’s file manager was responsible for managing storage. Tablespace containers were directories, and storage was allocated as files on demand. This type has been deprecated since Db2 v10.1.
• Database Managed Space (DMS): The Db2 database manager controlled the storage space directly. Containers were pre-allocated files or raw devices. This offered better performance than SMS but required more manual administration. This type has also been deprecated for user-defined tablespaces since Db2 v10.1.

Default Tablespaces

When a new database is created, Db2 automatically provisions at least three essential tablespaces:

1. SYSCATSPACE: A critical tablespace that holds the system catalog tables. These tables contain metadata about all objects in the database. It cannot be dropped.
2. USERSPACE1: The default tablespace for all user-created tables and indexes. If you create a table without specifying a tablespace, it will be placed here.
3. TEMPSPACE1: The default system temporary tablespace. Db2 uses this space for internal operations like sorting, joining, and reorganizing tables. A database must have at least one system temporary tablespace.

Page Size and Tablespace Limits

The page size (4 KB, 8 KB, 16 KB, or 32 KB) is a fundamental characteristic of a tablespace and its associated buffer pool. It determines the maximum size of the tablespace and the tables within it. Modern ‘large’ tablespaces, which are the default for automatic storage, support very large sizes.

Page Size	Max Table Size	Max Tablespace Size
4 KB	256 GB	64 TB
8 KB	512 GB	128 TB
16 KB	1024 GB (1 TB)	256 TB
32 KB	2048 GB (2 TB)	512 TB

Note: These are approximate limits for large tablespaces in recent Db2 versions. Always consult the documentation for your specific Db2 version.

Practical Example: Creating a Tablespace

Here is how you create a new automatic storage tablespace and then create a table within it. This assumes your database is enabled for automatic storage.

-- Step 1: Create a buffer pool with a 16K page size
CREATE BUFFERPOOL BP_APP_16K SIZE AUTOMATIC PAGESIZE 16384;

-- Step 2: Create a new tablespace for application data using the new buffer pool
CREATE TABLESPACE TS_APP_DATA
PAGESIZE 16 K
MANAGED BY AUTOMATIC STORAGE
BUFFERPOOL BP_APP_16K;

-- Step 3: Create a table within the new tablespace
CREATE TABLE app_user.transactions (
    transaction_id BIGINT NOT NULL,
    product_id INT,
    transaction_time TIMESTAMP
)
IN TS_APP_DATA;