In DB2 version 8 IBM finally solved our erternal problems about calculating the proper allocation for our DB2 tablespaces and indexspaces. DB2 version 8 was released long ago so I believe all installations are running at least DB2 version 8. I also hope that most installations have changed their space allocation procedures for DB2 spaces to use the new allocation algorithm. If not please regard this tip as a reminder.

Occationally some of us allocates our own tablespaces and indexspaces. All we need to know is that if you specify neither PRIQTY nor SECQTY you will automatically get the optimal allocation starting in DB2 version 8. If you really want to specify PRIQTY and SECQTY then specify -1 which is the same as no specification. If you want to change SECQTY on an existing tablespace or indexspace then set it to -1. This will trigger the new allocation algorithm to kick in for future extents.

And why do I recommend the new allocation algorithm. Well, because it starts with a small primary allocation and then increases every new secondary allocation in such a manner that the space never runs out of extents. When the underlying VSAM dataset reaches its maximum size DB2 allocates a new VSAM dataset for the space and the allocation scheme repeats itself. When the space finally reaches the upper limit in DB2 it is over. Please note that a partition in a partitioned space is always kept in a single VSAM dataset. It is only non-partitioned spaces that may be spread over several VSAM datasets.

Finally the battle against DB2-spaces running out of space has ended. I have spent many hours in this battle. This is one of the rare solutions where IBM has made life easier for us. If you want to read all the details and do not rely on my superficial description then look it up in SQL reference.