Apr 6 ’11

z/Data Perspectives:  Best Practices in DB2 Storage Management

by Editor in z/Journal

For many DB2 professionals, storage management can be an afterthought, but it really shouldn’t be. The cost of managing storage can be up to 10 times as much as the initial cost of acquiring the storage. Storage issues are vitally important, and unless managed appropriately, can be costly.

DB2 stores data in VSAM Linear Data Sets (LDSs). Each table space and index space requires at least one VSAM data set. But there are many other storage-related objects you will encounter with DB2 for z/OS, including storage groups (both DB2 and DFSMS), system data sets (logs, Boot Strap Datasets [BSDSs], etc.), image copy backups (stored on disk or tape), DB2 library data sets, temporary data sets, etc.

Storage-related information is available from multiple sources, including the DB2 Catalog, Real-Time Statistics (RTS), RUNSTATS, STOSPACE, etc., but the details are scattered and it can be difficult to gain a complete, accurate, up-to-date picture. Furthermore, any historical view into DB2 storage usage must be managed manually.

A responsible DBA with a comprehensive storage strategy can ensure that all DB2 databases have sufficient allocation to satisfy business requirements. They can answer questions such as, “Why is DB2 storage growing when our business isn’t?” Wasted storage will be minimized and a proactive approach to adding more storage when required will be adopted.

Although mainframe disk is usually equated to a 3380 or 3390, the actual physical disk is no longer as simple. Today’s modern storage architecture uses disk arrays, or Redundant Arrays of Independent Disk (RAID). An array is the combination of two or more physical disk devices in a single logical device or multiple logical devices. The array is perceived by the system to be a single disk device.

Another relatively recent change is the use of IBM’s Data Facility Storage Management System (DFSMS) to manage DB2 data. As of DB2 9, DATACLAS, MGMTCLAS, and STORCLAS can be specified in DB2 storage groups. Using DFSMS with DB2 is the sane thing to do because the new disk architectures, with concepts such as log structured files and gigabytes of cache, render conventional database design rules based on data set placement less relevant. In most cases, placement isn’t an issue, and when it is, SMS classes and ACS routines can be used. Keep in mind, too, that as of DB2 10, the system catalog must be managed by DFSMS.

What about extents? Many believe that modern storage devices render extent management obsolete, but that isn’t exactly true. For one thing, the latest extent management features only work with SMS-managed data sets; if your data sets are user-managed, then the old rules apply. For example, as of z/OS 1.7, system-managed data sets can have up to 123 extents on each of 59 volumes for a total of 7,257 extents. Otherwise, the limit remains 255. Also, extent consolidation, introduced in z/OS 1.5, requires SMS-managed STOGROUPs. When a new extent is adjacent to old, the two will be merged automatically. This can result in some extents being larger than the PRIQTY or SECQTY specification.

Even if everything is SMS-controlled, extents can impact performance. Elapsed time can increase with multiple extents if there’s heavy insert activity. For reads and updates, the number of extents shouldn’t impact performance. Regardless, you no longer need to continuously monitor extents and reorganize. It’s still a good practice to periodically clean up extents, but there are other methods of reducing extents that are quicker and easier than REORG.

Also, DB2 V8 introduced sliding scale secondary extents, which cause allocated extent sizes to gradually increase. This helps reduce the number of extents for your DB2 objects as they grow over time and is useful when you can’t predict how rapidly your data will grow.

In terms of storage best practices, it’s a good idea to perform regular and proactive monitoring. For instance, you should be: 1) tracking the space used by your entire DB2 system, individual DB2 databases, and table spaces and indexes; 2) monitoring the storage groups and associated volumes of a DB2 system; 3) monitoring all VSAM data sets for all table spaces and indexes, including used, allocated, primary and secondary quantity, extents, and the volumes they’re on; 4) monitoring alerts for page sets of table spaces and indexes that reach their maximum size/number of data sets; 5) tracking image copy backup data sets, including HSM migration; and 6) deleting Image copy backup data sets that are no longer needed because of DROP, DROP/CREATE, or MODIFY TABLESPACE.

Whenever possible, create alerts to automatically inform you of problems, shortages, and potential errors. Automate remediation tactics so the alert tells you what happened as well as what was done to correct the issue. Tools may be able to assist in automating reaction to shortages, potential errors, superfluous data sets, etc. The better the database system works with the storage systems, the better your database applications will perform. And that’s what it is all about, right?