Storage group

In DB2, a storage group is a logical collection of storage volumes (disk storage) used to store database objects such as tables, indexes, and tablespaces. Storage groups help manage physical storage efficiently and allow administrators to control where database objects reside within the DB2 environment.

What is a Storage Group?

A storage group in mainframe DB2 helps define where database objects will be stored physically by grouping volumes (disks) together. By specifying a storage group for tablespaces or indexspaces, DB2 can distribute data across multiple volumes to optimize performance and resource utilization.

Purpose -

• Efficient storage management: Allocate specific disk volumes for database objects.
• Storage distribution: Spread data across multiple volumes to improve performance.
• Flexibility: Allows DB2 to automatically select volumes within the group when allocating storage for objects.

Key Components -

• Storage Group Name: Unique name identifying the storage group.
• Volumes: List of physical disk volumes where DB2 can store data.

The user should have the SYSADM or SYSCTRL authority to create, alter and delete the storage group.

Creating a Storage Group

We can use CREATE STOGROUP SQL statement to create a storage group. This statement specifies the name of the storage group and the list of volumes associated with it.

Syntax -

CREATE STOGROUP storage_group_name
VOLUMES (volume_name1, volume_name2, ...)
VCAT catalog_name
[DATACLAS dataclass-name
MGMTCLAS mgntclass-name
STORCLAS storageclass-name]
[NO KEY LABEL/KEY LABEL key-label-name]
[ERASE YES | NO];

• storage_group_name: The name of the storage group.
• VOLUMES: Specifies the disk volumes associated with the storage group. We can list multiple volumes, allowing DB2 to use any of the volumes as needed.
• VCAT: Specifies the catalog name where DB2 will store information about the storage group. This is required.
• DATACLAS: (Optional) Uses to identify the SMS data class name to associate with the DB2 storage group.
• MGMTCLAS: (Optional) Uses to identifies the SMS management class name to associate with the Db2 storage group.
• STORCLAS: (Optional) Uses to identifies the SMS storage class name to associate with the Db2 storage group.
• ERASE: (Optional) Specifies whether to erase data when it is no longer needed (YES to erase, NO to retain).

Examples - Creating a Storage Group

CREATE STOGROUP EMPLSG
VOLUMES (VOL1, VOL2, VOL3)
VCAT MYCAT
ERASE NO;

• Creates a storage group named EMPLSG.
• The group includes three volumes: VOL1, VOL2, and VOL3.
• The catalog name is MYCAT.
• Erase option is set to NO, meaning data is not erased when freed.

Altering a Storage Group

We can alter an existing storage group using the ALTER STOGROUP statement. This allows us to add or remove volumes or modify other properties of the storage group.

Syntax -

ALTER STOGROUP storage_group_name
ADD VOLUMES (volume_name1, volume_name2, ...)
| DROP VOLUMES (volume_name1, volume_name2, ...)
| VCAT catalog_name
| DATACLAS dataclass-name
| MGMTCLAS mgntclass-name
| STORCLAS storageclass-name
| ERASE YES | NO;

• ADD VOLUMES: Adds one or more new volumes to the storage group.
• DROP VOLUMES: Removes one or more volumes from the storage group.
• VCAT: Specifies new catalog name.
• ERASE: Changes the erase option.

Examples - Altering a Storage Group

ALTER STOGROUP EMPLSG
ADD VOLUMES (VOL4)
ERASE YES;

• This alters the EMPLSG storage group.
• Adds a new volume VOL4.
• Sets the erase option to YES, meaning data will be erased when freed.

Deleting a Storage Group

To remove a storage group, use the DROP STOGROUP statement. This statement deletes the storage group and all references to it from the database.

Syntax -

DROP STOGROUP storage_group_name;

Examples - Deleting a Storage Group

DROP STOGROUP EMPLSG;

This deletes the EMPLSG storage group from the database.