﻿ Database Storage | DB2 and SMS


This page is specifically about DB2 running on z/OS mainframes.
DBAs were not very keen on DFSMS when it first appeared, they liked to be able to manually place their datasets on specific disks to avoid IO contention and make sure that they did not completely lose their systems when a disk crashed. I even heard IBM database consultants telling DBAs to avoid SMS if they could. Things have certainly changed, DB2 V10 now insists that many DB2 datasets have to be DFSMS managed, including the Catalog and Directory databases, so they can take advantage of Extended Format and Extended Addressability volumes.
Maybe this decision was helped when z/OS V1R11 introduced DFSMS 'separation groups' which allow critical datasets like the active logs and bootstrap datasets to be automatically kept on separate volumes and even behind separate controllers by DFSMS. Some of the benefits of DFSMS are explained in the DFSMS section.
The bottom line is that for DB2 to successfully use DFSMS, it is necessary for Storage Administrators to understand the specific requirements of DB2 databases, then work with DBAs to ensure that both DB2 and DFSMS are configured appropriately.

Stogroups and Storage Groups

Stogroups are the DB2 way of grouping volumes together into pools, and Storage Groups are the DFSMS way of grouping volumes. The two groupings are not the same, for one thing, a disk volume can only belong to one Storage Group, but DB2 Stogroups can share volumes. Storage Groups are the only SMS construct that you cannot specify explicitly while creating a data set, they are assigned by the DFSMS ACS routines. You can group storage groups to form Copy pools, which is used to perform DB2 system-level backups.

Before DB2 version 9, Stogroups were defined with an SQL statement like this. Note, no SMS classes, they would be allocated by the DFSMS ACS routines.


From DB2 9 or later, your DBA can specify SMS classes on their DB2 statements.


Storage Group requirements

DB2 has the ability to duplex databases for availability purposes, but it cannot duplex some critical DB2 databases, such as the directory and the DB2 catalog. These databases would benefit from being assigned to a storage class that provides DFSMS duplexing. DB2 10 for z/OS requires that the DB2 catalog and directory table spaces be placed under DFSMS, with a Data class that is defined with Extended Format and Extended Addressability enabled.

The boot strap data set (BSDS) and the active logs can be duplexed by DB2 and it was recommended that you placed the BSDS and dual active logs on separate volumes, under separate controllers. With DFSMS the duplexed copies should be placed in two separate pools, or better still by adding them to DFSMS separation groups, rather than placed by using a Guaranteed Space storage class.
However there is a caveat. It is a long time since anyone manufactured a real 3990 controller, or a 2.8GB 3390 disk. In fact, spinning magnetic disks themselves are almost history now. Modern disk subsystems are built with terabyte disks combined together into RAID systems, where a RAID set will hold several terabytes. These RAID sets are then split into stripes, and 'controllers' and 'DASD' are virtualised and formed within these RAID sets. Or, to put it another way, you could go to a lot of effort to separate out datasets onto different virtual disks and controllers, only to find that the disk subsystem has placed them on the same physical disks anyway.

Ideally the SMS ACS routines should be coded to assign database indexes to one SMS storage class and to assign database table spaces to a different SMS storage class.

DB2 database data is usually very well behaved, in that it grows very slowly, with no real unexpected peaks and troughs. The good news is that this means that you can easily run your main DB2 database pools at 90%+.
In theory, DB2 works best if all the user databases are combined into one big pool. SMS then spreads the data over several disks, and irons out (more or less) DASD hotspots. In practice, this will never work, of course. It is always a good idea to keep production data well segregated from development, and probably also best to keep Acceptance Test data in its own pools too.
Very active DB2 tablespaces should not suffer due to UCB queuing provided you have PAV installed, (parallel access volumes see the PAV section ), as this allows you to have several, highly active data sets on a single volume without performance problems. SuperPAV is best for this, as it will switch aliases from quiet volumes to busy volumes.

DB2 can have 123 file extents on each of 123 volumes, giving a total of 7,257 extents. This can be used to avoid space abends, but be aware that having too many extents will degrade performance.
DB2V8 introduced Sliding Extents which are enabled if MGEXTSZ=YES is set in DNZPARMS. This is set automatically in DB2V9. DB2 starts with a 2 cylinder first extent, then as it allocates new extents, it increments the size by one cylinder, up to a maximum set by the DSSIZE parameter. This is a better option than lots of small extents, as it avoids the performance impact of too many extents.

Non-SMS database files are limited to 4GB, which is not big these days. In order to create data sets larger than 4GB, they must be SMS-managed and assigned a data class that is enabled for Extended Addressability (EA). Smaller tablespaces do not need to be defined as Extended Format files, but they do need a Data Class with EXTENT CONSTRAINT REMOVAL=YES defined, however tablespaces bigger that 4GB must be SMS managed.
Another benefit of Extended Format data sets is that you can stripe data across volumes, if they have a storage class with the Sustained Data Rate value set greater than zero. Data striping helps improve the performance of Sequential I/O, and hence, you should consider striping active log data sets. Starting with DB2 9, archive logs are written using the Basic Sequential Access Method (BSAM), instead of Basic Direct Access Method (BDAM) used in previous versions, to exploit DFSMS striping.
Ensure that DFSMS does not alter the LRECL, BLKSIZE, or RECFM of the archive log data sets. Altering these attributes could result in read errors when DB2 attempts to access the log data. DB2 does not issue an error or a warning if you write or alter archive data to an unreadable format. For example, if DB2 successfully writes archive log data to an extended format data set, DB2 issues an error message only when you attempt to read that data, not when the data is written.

Converting DB2 data to SMS

Before conversion, make sure you have the correct management classes in place, have worked out a suitable storage pool plan and your ACS routines are coded to deal with DB2 data. There are two ways to convert DB2 data to SMS. If you can afford it, set up new, empty SMS pools and move the data into it. If you can't afford the space, then you have to convert in place.

Convert by Migration

This is probably the easiest way to convert if you don't have too many databases. You need to have enough spare disks available to allocate empty pools to convert into, and enough system downtime to do the moves. These are two good reasons not to use Convert by Migration for large amounts of data. You can move the data using storage utilities like DFDSS or SAMS, but this is one good opportunity to let your DBAs do the work for you using their own reorg utilities.
There is another advantage of using DBA utilities, they will reorganise the databases internally, and re-optimise the access paths through them. So all the databases are now under SMS, and have just been performance tuned! That makes your SMS systems look good.

Convert in place

This is probably the only practical way to convert hundreds of gigabytes of databases. It is best beforehand, if the DBAs convert the DB2 stogroups to SMS storagegroup equivalents, and make all allocations non specific '*'s. If DBAs want to place data using guaranteed space, suggest they do that later using their reorg facilities.

I'd suggest that you practice this on a small subset of the data. What you want is a discreet application that the DBAs have isolated on a small number of disks, say about 8. That conversion will give everyone the confidence to do the rest. If all your databases are single volume then its easy!, Just use DFSMSDSS CONVERTV or equivalent, volume by volume. If you have multi-volume databases, then all the volumes on which a database exist, have to be converted in the same job. If you have a loads of multi-volume, then you really need to convert all the volumes in one DFDSS statement. This is not as bad as it sounds, I've seen 600GB converted in 90 mins.

DB2 multi-volume allocation

The way DB2 handles multi-volume allocations can be a bit confusing to storage administrators. You cannot define the DB2 files using IDCAMS with a volume(* * *) statement, and you cannot use a dataclass with multi-volume capability. The DB2 file definition is an SQL statement


The files will then be potentially allocated on all the volumes in the SMS storage group, or up to 59 volumes, whichever is smaller. If you look at a VSAM cluster component with an IDCAMS LISTCAT, you will NOT see any candidate volumes. This is because, when DB2 wants to extend a dataset, and the file is full, it issues ALTER ADDVOLUMES to the file until it adds enough candidates for all the volumes in to pool, or up to 59 in total. Once it has successfully allocated another volume, it then issues ALTER REMOVEVOLUMES to delete all the candidates that it has not used.
Do not override this by adding volumes yourself. If the file ends up with more than 59 volumes in total, then the ALTER REMOVEVOLUMES will fail, and DB2 will stop the table. The only way out of this is to use REMOVEVOLUMES yourself to clear out the candidates.

DB2 UDB for Windows, Unix and Linux has two types of tablespaces called SMS (System Managed Space) and DMS (Database Managed Space). SMS in that context is not the same as SMS, or DFSMS discussed here.

DB2 Storage

Lascon latest major updates