DB2 - Storagegroups

DB2 - Simplified Space Management with Storage Groups

This chapter introduces Automatic Storage and Storage Groups, the modern approach to managing disk space for your Db2 databases.

Introduction to Automatic Storage

In the past, database administrators had to manually manage the individual files (containers) for each tablespace. Automatic Storage simplifies this dramatically. When a database is enabled for automatic storage, you provide it with one or more storage paths, and Db2 takes care of creating, extending, and managing the tablespace containers within those paths.

A Storage Group is a named collection of these storage paths. This allows you to group different types of data onto different tiers of physical storage. For example, you could create a FAST_STORAGE group on SSDs for critical tables and indexes, and a BULK_STORAGE group on traditional HDDs for less frequently accessed data.

When you create a database, a default storage group named IBMSTOGROUP is created automatically, pointing to the database path.

Viewing Storage Groups

You can see the existing storage groups and their associated paths by querying the system catalog.

SELECT SGNAME, SGPATH FROM SYSCAT.STOGROUPPATHS;

Creating and Managing Storage Groups

Let’s walk through a common scenario: adding a new, faster set of disks to your server and creating a new storage group for them.

Creating a Storage Group

Use the CREATE STOGROUP command to define a new storage group. You must provide one or more absolute paths.

-- Assuming you have a mounted filesystem at /ssd/data
CREATE STOGROUP fast_storage ON '/ssd/data';

Altering a Storage Group

As your storage needs grow, you can easily add new paths to an existing storage group. This is a non-disruptive, online operation.

-- Add a new path to the fast_storage group
ALTER STOGROUP fast_storage ADD '/ssd/data2', '/ssd/data3';

-- You can also drop paths, but only if they are not in use by any tablespace
-- First, you would need to rebalance data off the path (see below)
ALTER STOGROUP fast_storage DROP '/ssd/data';

Using Storage Groups with Tablespaces

Once a storage group is defined, you can direct new or existing tablespaces to use it.

Creating a New Tablespace on a Storage Group

When creating a new tablespace, specify the target storage group with the USING STOGROUP clause.

-- Create a new tablespace for important application data on the fast storage
CREATE TABLESPACE app_data USING STOGROUP fast_storage;

Moving an Existing Tablespace to a Different Storage Group

You can move an entire tablespace from one storage group to another online. Db2 will handle moving the data in the background.

-- Move the USERSPACE1 tablespace from the default group to our new fast group
ALTER TABLESPACE USERSPACE1 USING STOGROUP fast_storage;

The Rebalance Operation

After you add a new path to a storage group or move a tablespace, Db2 doesn’t immediately move existing data. It will only use the new paths for new data allocations. To redistribute the existing data across all available paths for better I/O performance, you must initiate a REBALANCE.

A rebalance is an online operation, but it can be I/O intensive. It’s best to run it during off-peak hours.

-- After moving USERSPACE1, rebalance it to spread its data across all paths in fast_storage
ALTER TABLESPACE USERSPACE1 REBALANCE;

You can monitor the progress of a rebalance using the MON_GET_REBALANCE_STATUS table function.

Dropping a Storage Group

You can only drop a storage group if it is not being used by any tablespaces. You must first alter any associated tablespaces to use a different storage group.

-- First, move the tablespace to another group
-- ALTER TABLESPACE app_data USING STOGROUP IBMSTOGROUP;

-- Now, the storage group can be dropped
DROP STOGROUP fast_storage;

Best Practice: Using multiple storage groups is a powerful way to implement a tiered storage strategy, improving performance and managing costs effectively. Plan your storage layout based on the access patterns and importance of your data.