DB2 storage groups

The storage paths where we can store the data are called a storage group.

We configure the storage groups to depict various storage classes present in the database system. As required, we can allocate the tablespace to the storage group.

Storage groups depict the physical storage which is managed by SMS.

The tablespace can be created to only one storage group, but a single storage group can be associated with various tablespaces. Db2 provides statements like ALTER STOGROUP, RENAME STOGROUP, CREATE STOGROUP, DROP STOGROUP, and COMMENT to manage the storage group.

The storage group can be a group of DASD volumes, volumes in optical libraries, volumes in tape libraries, and virtual input/output storage.

With the help of the table partitioning feature, we can place the table data in various tablespaces. Using this feature storage group is stored on the subset of table data on fast storage, and the remainder is on one or more parts of slower storage.

When defining the storage groups, we should group the storage path according to the quality of service characteristics.

Listing storage groups

We can list the storage groups in the present database:

Syntax:

db2 select * from syscat.stogroups

Let's see the Example to see storage groups in the present database:

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from syscat.stogroups

Creating a storage group

We can use CREATE STOGROUP command to create the storage groups.

If we create a database with the AUTOMATIC STORAGE NO clause, it has no functionality of the default storage group.     

Syntax: [To create a new serogroup. The 'stogropu_name' indicates the name of the new storage group, and 'path' indicates the location where data (tables) are stored]

db2 create stogroup storagegroup_name on 'path

Storagegroup_name is the name of the storage group; we also need to mention the storage paths.

Let's see an example:

We will create a new storage group named new_stogroup on the path D Drive

C:\Program Files\IBM\SQLLIB\BIN>db2 create stogroup stogroup1 on 'D:\'

Output:

To verify we can see the list of storage groups present:

C:\Program Files\IBM\SQLLIB\BIN>db2 select * from syscat.stogroups

Creating tablespace with stogroup

We can create a tablespace and associate it with the storage group.

Here is how you can create a tablespace with a store group:

Syntax:

db2 create tablespace <tablespace_name> using stogroup <stogroup_name>

Let's see an example to create a new tablespace named tablespace1 and the associated storage group which is present in the database.

C:\Program Files\IBM\SQLLIB\BIN>db2 create tablespace tbs using stogroup stogroup1

Output:

Altering a storage group

We can alter the stogroup to alter the definition of the storage group involving the setting media attributes, setting a default storage group, or setting a data tag. We can also add and remove the storage paths from the storage group.

We can modify the location of the storage group:

You can alter the location of a store group by using the following syntax:

Syntax:

db2 alter stogroup add ‘location_source’, ‘location_destination’

Location_source is the old location, and location_destination is the new location.

Let's see an example: we will change the path of new_storagegroup toE:\.

C:\Program Files\IBM\SQLLIB\BIN>db2 alter stogroup stogroup1 add 'D:\', 'E:\'

Output:

Dropping folder path of storagegroup

We can drop the old storage group by using following syntax:

db2 alter stogroup drop ‘/path’

Lets see an example:

The old path of storage group stogroup1 will be dropped

C:\Program Files\IBM\SQLLIB\BIN>db2 alter stogroup stogroup1 DROP ‘D:\’

 

Renaming a storage group

We can rename the storagegroup by the command RENMAE STOGROUP

Syntax:

db2 rename stogroup <old_stg_name> to <new_stg_name>  

Lets see an example of renaming new_storagegroup to stogroup1

C:\Program Files\IBM\SQLLIB\BIN>db2 rename stogroup stogroup1 to st2

Output will be following statement:

DB20000I  The SQL command completed successfully.

Dropping a storage group

Step 1: To drop the storage group initially we need to assign the tavlesapce associated to other storage group

We will create new storage group st2 on D path

Before dropping any storagegroup, you can assign some different storagegroup for tablespaces.

C:\Program Files\IBM\SQLLIB\BIN>db2 create stogroup st2 on 'D:\'

Next we will assign the tablespace to newly created storagegroup

C:\Program Files\IBM\SQLLIB\BIN>db2 alter tablespace tbs using stogroup st2

And then drop the initial storage group

C:\Program Files\IBM\SQLLIB\BIN>db2 drop stogroup stg2