DB2 Storage and STOGROUPs

DB2 Storage and STOGROUP s

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.

CAUTION

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 ; 

After creating these STOGROUP s, you can balance the load on the volumes by assigning some of the table spaces to TESTSG1 and some to TESTSG2 . If one volume runs out of space, the other can serve as the backup.

The maximum number of volumes used by a storage group is 133 (even though DB2 allows more than 133 volumes to be defined to a storage group). It usually is difficult to monitor more than 3 or 4 volumes to a STOGROUP , however. All volumes in a storage group must be of the same type (for example, 3380, 3390, and so on).

CAUTION

Be sure to assign only DASD volumes of the same type to a single STOGROUP . When you mix multiple types of disks together in a single storage group, problems can ensue. For example, if DB2 must extend a STOGROUP -managed data set and the volumes are of different types, an extend failure will occur.

Using DFSMS with DB2

Another solution for avoiding multi-volume storage groups is to use DFSMS, or SMS for short. SMS stands for System Managed Storage. With SMS, the system determines where data sets are to be placed, easing the burden of data set creation and management on database administration.

You can define a DB2 STOGROUP with VOLUMES("*") to indicate SMS managed storage. When the "*" is specified in the VOLUMES clause, SMS will be used to assign a volume to the table space and index space data sets in that STOGROUP .

Using SMS, you can define storage and management classes to identify differing data set requirements. Storage and management classes are grouped into SMS storage groups.

ACS routines are used to assign DB2 table space and index data sets to SMS classes and Storage Groups. ACS stands for Automatic Class Selection . ACS is used to define policies for data set naming, volume naming, restrictions on usage, and other policies for data set creation and management.

ACS uses the data set name to decide where to place the data set. Many methods can be devised with specific naming standards to assign SMS classes based on the names of the DB2 data sets.

CAUTION

Do not confuse DB2 STOGROUP s with SMS Storage Groups. An SMS Storage Group refers to a set of volumes in an installation; a DB2 STOGROUP refers to a set of volumes containing a set of data. Different STOGROUP s can share the same disk volume or volumes. One disk volume can only belong to one SMS Storage Group.

With the new efficient DASD that is available, SMS is a more viable option than it was for past releases of DB2. However, if you want to ensure specific data set placement for all DB2 data sets, avoid SMS.

When using SMS, use ACS to differentiate between table spaces and index data sets and place them on different devices. This requires more setup work, but is required for achieving acceptable performance.

One possible scenario is to let SMS handle the majority of your DB2 data set placement, but use non-SMS data set placement techniques for high volume data sets to separate data from indexes on separate volumes or to ensure parallelism. In this way, SMS can be used to minimize the effort for the bulk of your data set placement tasks , while allowing you to target your "high need" data sets to specific devices.

SMS and Partitioned Table Spaces

One of the benefits of partitioning a table space is to spread the data across multiple physical devices. If you turn over data set placement to SMS, this benefit might be lost. There are three options for using SMS with partitioned table spaces:

• SMS manages everything ” If the number of volumes in the Storage Group is much larger than the number of partitions in the table space, SMS might place each partition on a separate volume. However, this is by no means assured. To be certain that each partition is placed on a different volume, use another option.

  If each partition is more than half a volume in size , however, you can be sure that SMS will place each partition on a separate volume, because two partitions will not fit on one volume. In this scenario, allowing SMS to manage everything might be an acceptable choice.

  CAUTION

  Be aware that space fragmentation on the volumes might result in a lack of volumes with sufficient free space, possibly resulting in REORG s failing due to lack of space.

  
  

• One SMS storage group assigned per partition ” An SMS storage group consisting of only one volume can be defined for each table space partition. The ACS routine then assigns an SMS storage group to each partition. This method is similar to creating a DB2-defined partitioned table space using one STOGROUP for each partition.

  The advantage of this method is strict data set placement. The disadvantage is the complexity of the ACS routines required and the need for many SMS storage groups to be defined.

• One SMS storage group assigned per partitioned table space ” The third and final alternative to be discussed here is to define one SMS storage group for each partitioned table space. Be sure to assign sufficient volumes to the SMS storage group for all partitions in the table space. SMS will distribute the partitions onto those volumes. Be sure to assign no other table spaces or indexes to this SMS storage group. That way, no other data sets will ever be allocated on these volumes, practically reserving the space for this table space.

This discussion of SMS and DB2 has been brief. A comprehensive study of SMS is beyond the scope of this book. However, if you are implementing SMS with DB2, I recommend that you acquire a good understanding of SMS before proceeding. To do so, obtain and read (at a minimum) the following IBM manuals:

• SG24-5462 : Storage Management with DB2 for OS/390

• SG24-4892 : DFSMS/MVS Technical Overview

• SG24-5272 : DFSMShsm Primer

• SC26-3123 : DFSMS/MVS Implementing System-Managed Storage

Storage Guidelines

When creating DB2 objects, an efficient environment can be created by heeding the following storage guidelines.

Avoid Using SYSDEFLT

The default DB2 storage group is SYSDEFLT . SYSDEFLT is created when DB2 is installed and is used when a storage group is not explicitly stated (and VCAT is not used) in a database, a table space, or an index CREATE statement. I recommend that you avoid using SYSDEFLT . Objects created using SYSDEFLT are hard to maintain and track. Additionally, creating many different DB2 objects from diverse applications on the same DASD volumes degrades performance and, eventually, no more space will remain on the volumes assigned to SYSDEFLT . If you grant the use of SYSDEFLT only to SYSADM s, you can limit its use.

Favor STOGROUP -Defined Data Sets Over User-Defined VSAM

The need for specific VSAM data set definition has diminished as DB2 and disk devices have become more efficient. In general, unless you have very specific data set placement needs, favor using STOGROUP s (with or without SMS) over user-defined VSAM data set definition.

User-Defined VSAM Data Set Definitions

When creating DB2 objects with the VCAT option instead of the STOGROUP option, you must create user-defined VSAM data sets explicitly using the VSAM Access Method Services utility, IDCAMS . You can use two types of VSAM data sets for representing DB2 table spaces and index spaces: VSAM ESDS and VSAM LDS.

VSAM ESDS is an entry-sequenced data set, and VSAM LDS is a linear data set. A linear data set has a 4K CI size and does not contain the control information that entry- sequenced data sets normally contain. VSAM LDS and ESDS data sets are not used as plain VSAM data sets. DB2 uses the VSAM Media Manager to access these data sets. DB2 performs additional formatting of the VSAM data sets, causing them to operate differently than standard VSAM. Therefore, a direct VSAM read and write to a DB2 VSAM data set will fail.

Create DB2 data sets as VSAM linear data sets instead of as VSAM entry-sequenced data sets, because DB2 can use LDS more efficiently .

An example of the IDCAMS data set definition specification follows:

 DEFINE CLUSTER --     (NAME (  vcat  .DSNDBC.  dddddddd.ssssssss  .I0001.A  nnn  ) --      LINEAR --      REUSE --      VOLUMES (  volume list  ) --      CYLINDER (  primary   secondary  ) --      SHAREOPTIONS (3  3) --      ) -- DATA --     (NAME (  vcat  .DSNDBD.  dddddddd.ssssssss  .I0001.A  nnn  )) -- 

where:

vcat	High-level qualifier, indicating an ICF catalog
dddddddd	Database name
ssssssss	Table space name or index name
nnn	Partition number or data set number
volume list	Listing of physical DASD devices
primary	Primary space allocation quantity
secondary	Secondary space allocation quantity

Verify Disk Volumes Assigned to Your STOGROUP s

When you create a STOGROUP , DB2 does not verify that the volumes specified in the VOLUMES clause are valid, existing disk devices. Use care when creating your DB2 storage groups to ensure that only valid disk volumes are specified to the storage group in the CREATE STOGROUP .