November 12, 2007

DB2 9 Mysteries of DB2 Storage Groups

DB2 stores table data in a table space and index data in an index space. The underlying data set holding this data is a VSAM dataset. Let's examine the three options for creating and managing these datasets: DB2 managed, SMS managed and user managed.

DB2 managed--The preferred and IBM-recommended data management method is to let DB2 manage the dataset allocation and storage by use of a storage group. The storage group (STOGROUP) is the foundation and direction of DB2 managed datasets. A storage group is a named object cataloged in the DB2 table, SYSIBM.SYSSTOGROUP, by use of the CREATE STOGROUP statement. When you define a storage group you must identify or list candidate DASD volumes that can be used when VSAM files are allocated to store your data. Make sure all volumes you identify to the storage group are of the same type. In addition, you must identify the ICF catalog (also known as VCAT or VSAM catalog) that will be used.

Using the DB2 managed dataset saves your database administrator (DBA) some work. It also positions you to take advantage of new features IBM introduces to further simplify and manage data storage.

SMS managed--You can let SMS manage the datasets, either when you use DB2 storage groups or when you manually create the datasets yourself. Many shops have combined the use of storage groups with SMS management by using a volume ID of asterisk (*) instead of actual volume numbers. This allows storage-management administrators to write rules for dataset size and placement. However, this can be complicated and error prone to work out when you're getting started with SMS managed data sets. So with DB2 9, IBM has extended the storage group to include SMS management attributes (DATACLAS, MGMTCLAS and STORCLAS). Now you can specify the SMS information when you create the storage group--you don't have to have new rules written when you have a new VCAT or database. 

IBM’s preferred method is to use the storage group without listing any volumes and specify the SMS management attributes. This greatly reduces the workload on storage management and, again, on DBAs.

User managed--With this method, you define and manage your own datasets using VSAM Access Method Services. This provides the greatest control over the physical storage of tables and indexes. You may find some shops use this method for production and then use storage groups for the test and development systems.

Personally, I think the need to manually control VSAM datasets for DB2 is long gone. IBM's direction is not only to use the combination of DB2 managed datasets with storage groups, but to also automate this even further with SMS management attributes within the storage group.

Again, IBM’s direction is to make DB2 data management autonomic, or self-healing. With storage groups and SMS management attributes, you get storage availability. Now the issue is running out of space or extents for a given dataset. With automated space management (also known as sliding scale algorithm), DB2 determines the appropriate algorithm for what space should be allocated. Automated space management occurs when PRIQTY and SECQTY is set to -1 and the Optimize Extent Sizing zparm (MGEXTSZ) is set to YES. 

With the knowledge that the storage is being managed and a failure for secondary allocation is greatly reduced, DBAs can rest at night.