February 11, 2008

DB2 Sliding Scale Space Allocation

As a production support DBA I used to get weekly reports on dataset size and extents. The main purpose of this was to figure out which datasets are near the maximum number of extents and schedule an alter primary/secondary quantity and reorganize the table space or index space to reduce the number of extents.

In most cases this was enough to keep you from having a production outage; however, you could still have an application go into production with a feature that isn't really used. Then months or years later the feature is turned on and volumes of data start pouring in causing you an outage due to running out of extents. 

With the improved space allocation in DB2 8 you have increased data availability and reduced the possibility of an outage caused by lack of space. You are also more likely to reach the maximum dataset size before you reach the VSAM limit of 255 extents. The other nice thing is you aren't required to specify a primary or secondary space quantity when creating or altering a table space or index space. 

Now I’ve been trained over the years to try and keep a table space and index space in as few extents as possible to improve performance. From my testing it seems that DASD has gotten so much faster that having multiple extents isn't really a problem.

The Rules
I would like to first give you a little information about the rules used to allocate primary and secondary space allocations and then take a look at some different scenarios to see if having multiple extents really impacts on fetch and insert processing.

It may not be obvious but the new space allocation affects both DB2 managed data sets and any existing user defined datasets that require additional extents.

In the old days we would always specify a primary and secondary space allocation. You would try and make the primary quantity large enough to hold all the data to improve performance. However, you had to be carful because if the data set needed to extend to another volume the primary allocation would be used to allocate the first data set on that volume. This could waste a lot of space or worse it could fail due to a shortage of space on the candidate volumes. With the new method you can let DB2 choose the value for both primary and especially the secondary with sliding scale allocation. This will increase the possibility of reaching the maximum data set size before running out of extents. 

In general the old technique was to define a large primary and a small secondary thus causing a problem with running out of extents. The new technique is to define a small primary and let the sliding scale allocation of secondary take bigger and bigger allocations and then smaller allocations if needed.

See the DB2 Administration Guide for a detailed description of the rules used for space allocation.

The system zparms that have an impact on allocation are TSQTY, IXQTY and MGEXTSZ. The descriptions below are from the Installation Guide.

TSQTY--Specify the amount of space in KB for primary and secondary space allocation for DB2-defined datasets for table spaces that are being created without the USING clause. A value of 0 indicates that DB2 is to use a default value of one cylinder for a non-LOB table space or 10 cylinders for a LOB table space.

IXQTY--Specify the amount of space in KB for primary and secondary space allocation for DB2-defined datasets for index spaces that are being created without the USING clause. A value of 0 indicates that DB2 is to use a default allocation of one cylinder.

MGEXTSZ--Specify whether secondary extent allocations for DB2-managed data sets are to be sized according to a sliding scale that optimizes the likelihood of reaching the maximum dataset size before secondary extents are exhausted.

If you select NO, the default value, you'll manage secondary extent allocations manually. For nonpartitioned table spaces and nonpartitioned index spaces, when all extents of the first dataset are exhausted, the primary space allocation of each subsequent dataset is always the PRIQTY setting.

If you select YES, DB2 automatically optimizes the secondary extent allocations of datasets for table spaces and index spaces that have a SECQTY value of greater than zero. When all secondary extents are exhausted for the first dataset of a nonpartitioned table space or a nonpartitioned index space that has a SECQTY value of greater than zero, the primary space allocation of each subsequent dataset is the larger of the SECQTY setting and the value that is derived from the sliding scale algorithm. When the sliding scale is used, secondary extent allocations that are allocated earlier are smaller than those allocated later, until a maximum allocation is reached. The maximum allocation is 127 cylinders for datasets with a maximum size of 16 GB or less, and 559 cylinders for datasets with a maximum size of 32 GB or 64 GB.

Productivity Improvements
From my testing I’ve found that letting DB2 manage the space allocation can greatly improve productivity in your application development environments as well as reduce the risk of having an outage in production due to running out of extents. However, in production you maybe concerned with what the impact of having multiple extents will have on your application performance.

Next week I'll examine this issue of multiple extents and see if it is still an issue or whether the DASD technology improved to the point extents just don’t matter anymore.