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-220.127.116.111318 DB_ADM_STOGRP 1 N +6.72500000000000E+000 +1.00000000000000E+002 - - 2014-05-08-18.104.22.168712 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
I really liked the level of detail in this post; it’s perfectly geared for the DBA. Some discussions of storage can get too wonky, some too thin on detail. This was a perfect primer with sample commands that I can use to quickly get a storage group setup and some ASTs created and using it. Thanks!
First I must outline that your webpage and the posts in it (plus the effort to generate them) is simply awesome.
Now let’s go to the point,
You wrote “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.”.
Since in our shop we manage out table/tablespaces assigment in ‘the old mainframe school’ way (1 table = 1 Tablespace), we’re concerned about how we’re going to accomplish not just the performance but the practical side of knowing exactly where our DB2 objects are located.
WIll AST be the only way to manage our DB2 storage in the near future? We’d like to continue the way we manage our data file location/allocation, but it seems that we won’t be able to.
Thanks again for the wonderful contents of your webpage.
The Old-School way of doing it has been fading away for years. I still hear of a few large shops doing things that way, but I have a very hard time seeing the advantage of it. I do still often separate out my few very large or very active tables to their own tablespaces, but I fail to see the true advantage of doing it for every table. I like it so I can keep a busy table from swamping my bufferpool or dedicate bufferpool resources to it.
One reason is the ability of admin_move_table to move tables between tablespaces in an online manner. You can very much control and know what disks specific objects are on, but you’re not telling me that each tablespace has it’s own filesystem, are you?
With the newer model, you could continue to have each table in a separate tablespace, but have those tablespaces spread across a small number of storage groups and grouped by storage characteristics or content characteristics. Tablespaces can then be easily moved between storage groups, online, if you discover that you have to separate something out. If anything, the addition of the storage group layer makes this easier and more seamless.