DB2 storage groups are a set of volumes on disks that hold the data sets in which tables and indexes are stored. The description of a storage group names the group and identifies its volumes and the VSAM (Virtual Storage Access Method) catalog that records the data sets. The default storage group, SYSDEFLT, is created when you install DB2.
Within the storage group, DB2 does the following actions, Allocates storage for table spaces and indexes, Defines the necessary VSAM data sets, Extends and deletes VSAM data sets, Alters VSAM data sets
All volumes of a given storage group must have the same device type. However, parts of a single database can be stored in different storage groups. DB2 can manage the auxiliary storage requirements of a database by using DB2 storage groups. Data sets in these DB2 storage groups are called "DB2-managed data sets. These DB2 storage groups are not the same as storage groups that are defined by the DFSMS storage management subsystem (DFSMSsms).
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.
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.
A DB2 storage group , also known as a STOGROUP , is an object used to identify a set of DASD volumes associated with an ICF catalog, or VCAT. Storage groups and user -defined VSAM are the two storage allocation options for DB2 data set definition. A STOGROUP can be assigned to a database, a table space, or an index. DB2 uses the volumes of the STOGROUP to assign table space and index space data sets to a device.
Define Useful Storage Groups
Define more than one volume per storage group to allow for growth and to minimize out-of-space abend situations. A data set extend failure causes DB2 to check the STOGROUP volume entries and issue a VSAM ALTER ADDVOLUMES for the data set. When defining multiple volumes to a storage group, DB2 keeps track of which volume was specified first in the list and tries to use that volume first. DB2 does not attempt to balance the load on the DASD volumes. Data set allocation is performed by IBM's Data Facility Product (DFP). The order in which the volumes are coded in the CREATE STOGROUP statement determines the order in which the volumes are used by DB2. When the first volume is full, or if for any reason DFP determines that it cannot allocate a data set on that volume, DB2 (through DFP) moves to the next volume.
You cannot retrieve the ordering information for volumes in a STOGROUP from the DB2 Catalog, so make sure you have documentation detailing the order in which the volumes were defined to the storage group. This requires the DBA to explicitly document the order of the volumes in the CREATE STOGROUP statements by saving the DDL or by creating a word processing document or spreadsheet with the details.
Without this information, it is impossible to determine the ordering of volumes in the STOGROUP. If you would rather not administer multiple volume STOGROUP s, specifying only a single volume to a STOGROUP instead, you must be prepared to handle abends resulting from a volume being out of space. Handling out-of-space conditions usually involves one of the following -
Moving the data set to a volume with more space by altering the STOGROUP and then recovering or reorganizing the table space. Adding a volume to the STOGROUP to accommodate additional data set extents, Of course, you can also choose to use SMS to manage DB2 data sets. This option is discussed in the next section. A good method of maintaining DB2 objects on multiple volumes is to define multiple STOGROUP s, each with a different volume as the first listed volume. For example, consider a new application assigned two volumes, called VOL1 and VOL2 . Create two STOGROUP s as follows :
CREATE STOGROUP TESTSG1 VOLUMES('VOL1', 'VOL2') VCAT appl ; CREATE STOGROUP TESTSG2 VOLUMES('VOL2', 'VOL1') VCAT appl ;
You can use Storage Group object options to create, template, alter, and drop DB2 storage groups. A storage group is a DB2 object that represents a named set of DASD volumes controlled by a specified VSAM catalog (VCAT), on which DB2, Allocates storage for tablespaces and indexes, Defines the necessary VSAM data sets, Extends and deletes VSAM data sets as required, DB2 monitors and maintains the storage groups, and uses them to store DB2 table and indexspaces. A storage group can be assigned to a database, tablespace, or index space. All tables that reside in a given tablespace use that tablespace's storage group. Following are some of the ways DB2 helps manage external storage requirements.
When a tablespace is created, DB2 uses VSAM access method services to define the necessary VSAM data sets. After the data sets have been created, they can be processed with access method service commands that support VSAM control-interval (CI) processing. When a tablespace is dropped, DB2 automatically deletes the associated data sets. When a data set in a simple tablespace reaches its maximum size of 2 GB, DB2 can automatically create a new data set. When needed, DB2 can extend individual data sets. There are many useful features that are available from within the Storage Group object panels to help you facilitate the creation and overall management of your DB2 storage groups. These features include -
Object Selection
You can enter selection criteria in many fields to bring up lists of objects to choose from. These fields include VCAT and the VOLSER list. When entering and managing the volumes for your storage group in the VOLSER list, you can use any standard ISPF editor commands to copy, replicate, delete, and move volumes within the VOLSER list. To provide a larger work area for managing the volumes, you can turn the header (top portion of the panel before the VOLSER list) OFF with the HEADER command.
Volume and VCAT Selection Help
When defining a storage group, the names of the volumes or VCAT that the storage group references must be entered. Instead of having to remember and enter specific volume and VCAT names, they can be selected by entering selection criteria to receive a list of volumes or VCATs.
Line Commands
Insert and Delete line commands are supported to assist in manipulating the volume list.
Primary Commands
The following panel-specific primary commands are available to help manage storage groups: Apply Def (apply user defaults to the table definition), Compare (display the original and current object definitions), Header (toggle the display of the panel header fields on or off), Reset (reset all fields to their original values), Source (set all new fields to source field values) Target (set all new fields to target field values)
Template Option
The Template option permits creation of a new storage group, using an existing storage group as a model. Enter selection criteria to receive a more specific list of storage groups.
Alter Option
The Alter Storage Group option lets you change any characteristic of the storage group. While DB2 allows only the addition or removal of volumes from the storage group, we let you change the VCAT or even the storage group name. This is done by dropping, and then recreating the storage group. Like the Template function, the Alter function lets you select from lists of storage groups, VCATs, and volumes.