DB2 Basics: Storage Groups

What is a Storage Group?

A storage group is a layer of abstraction between the data in your database and disk. It is only used with Automatic Storage Tablespaces (AST). It allows us to group tablespaces together to live in similar places. Storage groups were first introduced in a roundabout way with automatic storage databases in DB2 8.2. These databases allowed you to specify one or more paths for the entire database, and DB2 would manage spreading the data across them. It soon became clear that a level between the old school “control where everything goes” and the newer “I don’t care, just spread it across as many read/write heads as possible” was needed. Personally, I’m just fine with having only two locations for my data. I could manage that just fine in with the old methodology with DMS tablespaces, and I manage it just fine in my more recent databases with storage groups.

With DB2 10.1, IBM introduced this middle level of control. We can now create a number of storage groups. This was introduced as a way to handle multi-temperature data and disks of varying speeds. But it’s clear that we can use it in ways beyond that. I use it to separate my administrative, event monitor, and performance data to a separate filesystem from the regular database data – mostly so that if that data gets out of control, it doesn’t affect the rest of my database. If you do have SSD or different speeds of disk, the multi-temperature approach sure makes sense.

This image represents a standard DMS tablespace with file containers:

This image represents an AST tablespaces within a storage group:

You can see the additional level of abstraction represented by a storage group in gray. Assuming a tablespace is added after both storage paths are added to the storage group, DB2 will create tablespace containers on each storage path. Interestingly, all the old DMS standards like keeping all the tablespace containers for a tablespace the same size still apply with AST tablespaces and storage groups. DB2 will continue to stripe things across the tablespace containers in the same way that it does for DMS tablespaces.

Automatic Storage Tablespaces

Storage groups can only be used with automatic storage tablespaces. Automatic storage tablespaces are essentially DMS under the covers, with mechanisms for automatically extending them. They combine the best of both SMS and DMS tablespaces in that they can have the performance of DMS tablespaces, but the ease of administration like SMS tablespaces. IBM had actually deprecated both SMS and DMS tablespace types (for regular data) in favor of AST tablespaces. This means that in the future, our ability to use these tablespace types may be removed.

How to Create a Storage Group

Unless you specified AUTOMATIC STORAGE NO on the CREATE DATABASE command or have upgraded a database all the way from DB2 8.2 or earlier, you likely already have a default storage group in your database, even if you have not worked with storage groups at all. You can look at the storage groups in a datatbase with this SQL:

select  substr(sgname,1,20) as sgname, 
        sgid, 
        defaultsg, 
        overhead, 
        devicereadrate, 
        writeoverhead, 
        devicewriterate, 
        create_time 
    from syscat.stogroups 
    with ur

SGNAME               SGID        DEFAULTSG OVERHEAD                 DEVICEREADRATE           WRITEOVERHEAD            DEVICEWRITERATE          CREATE_TIME
-------------------- ----------- --------- ------------------------ ------------------------ ------------------------ ------------------------ --------------------------
IBMSTOGROUP                    0 Y           +6.72500000000000E+000   +1.00000000000000E+002                        -                        - 2014-05-07-17.44.21.791318
DB_ADM_STOGRP                  1 N           +6.72500000000000E+000   +1.00000000000000E+002                        -                        - 2014-05-08-18.49.16.108712

  2 record(s) selected.

Notice that a lot of disk characteristics that you may be used to seeing at the tablespace level are now available at the storage group level. Tablespaces can be created or altered to inherit disk settings from the storage group. Assuming each storage group is associated with similar kinds of disks, it makes sense to do things this way. To alter an existing AST tablespace to inherit from the storage group, use this syntax:

alter tablespace TEMPSPACE1 overhead inherit transferrate inherit
DB20000I  The SQL command completed successfully.

Creating a Storage Group and AST Tablespace

Creating a new storage group is easy if you know what filesystems you want associated with it:

db2 "create stogroup DB_ADM_STOGRP on '/db_data_adm/SAMPLE'"
DB20000I  The SQL command completed successfully.

Then creating an automatic storage tablespace using that storage group can be done simply as well:

db2 "create large tablespace DBA32K pagesize 32 K managed by automatic storage using stogroup DB_ADM_STOGRP autoresize yes maxsize 4500 M bufferpool BUFF32K overhead inherit transferrate inherit dropped table recovery on"
DB20000I  The SQL command completed successfully.

Since storing permanent data in DMS and SMS tablespaces has been deprecated, it is clear that IBM’s direction is to eliminate the use of these in favor of AST and storage groups.

See these blog entries for more detailed information on Automatic Storage Tablespaces:
(AST) Automatic Storage – Tips and Tricks
Automatic Storage Tablespaces (AST): Compare and Contrast to DMS