If _areastatus VST cannot be used, what's advise for TYPE II Area space planning?Run DBANALYS reports, which can be relied upon to report the Free Blocks available in Type II Areas.
The number of
blocks per database object are reported in the respective FREE, RM and INDEX DELETE Chain analysis
For example: ("
object" refers to the object number of the table or index in that Storage Area)
FREE CHAIN ANALYSIS
1 block(s) found in the free chain of Master object 0 (Cluster Master)
21 block(s) found in the free chain of Index object 12 (Index PUB.Customer.CustNum)
24 block(s) found in the free chain of Index object 13 (Index PUB.Customer.Comments)
19 block(s) found in the free chain of Index object 14 (Index PUB.Customer.CountryPost)
23 block(s) found in the free chain of Index object 15 (Index PUB.Customer.Name)
3 block(s) found in the free chain of Index object 16 (Index PUB.Customer.SalesRep)
192 block(s) found in the free chain of Table object 2 (Table PUB.Customer)It is never-the-less in TYPE II Areas a bit more than just knowing how much free space there is (like in Type I Storage Areas). It's also about knowing the space utilization of
specific objects in that Area.
It is important to remember that once a database block is formatted as a
record block, it will remain a record block and its
deleted entries will be:
- If Type I, reused as new records for any table's records that are added.
- If Type II, the blocks will only ever be reused by the same object until the object definition is removed from the area when it is either deleted or a tablemove is executed. In other words, those free clusters will only be available for that object's records for the life of that table in a Type II Storage Area.
For
index blocks, when all the entries in a block are deleted, that block will become a free block (well technically first added to the Index Delete Chain then a Free Block):
- If Type I, the Free Block is then available as a record block or an index block again as space is needed.
- If Type II, the space freed will only be reused by the same index object until the object definition is either deleted or a tablemove with indexes or an indexmove is executed.
- IDXCOMPACT will not release empty index blocks back into the general pool. They are still tied to the index being compacted. When the indexes are in a TYPE II area, then the blocks associated are in clusters and these are managed by the cluster manager (0). While the b-tree is truncated during the IDXCOMPACT routine, the blocks are still available for use in the cluster for that index object only for its lifetime in that Storage Area.
It is a matter of knowing how much space and growth the individual objects in a Type II Storage Area need or will need. Just because there are "
xblocks free", doesn't mean that these blocks will be available for the next great data load. Space above the current High Water Mark (Empty blocks) may be used instead. Those
xblocks need to belong to the object(s) involved in the load to be available for use by each object.
Another good measure of taking DBANALYS regularly, is in being able to plot the database's growth characteristics and plan accordingly.
For example:
- TABLE A may create xmillion records in one go per month, then delete them incrementally over the month. The current Free Blocks (or total space occupied by this table) are not going to cause unexpected database growth, but may need extra space added in time if for example, these record's schema changes (larger records).
- TABLE B may create xmillion records over a period of a month and never delete or update them. This table's growth needs to be monitored and space periodically added in order to accommodate its growth trend (perhaps a periodic data archiving project would be prudent).
- TABLE C may create xthousands small records per day and continually update them throughout their lifespan ending in deletion. This table's growth needs to be monitored and space periodically added in order to accommodate it's growth trend.
There is no utility in existence or planned that would run through a chain and release Free Blocks back to the general pool. The space occupied by an object is considered the space needed by an object through time. There is an overhead allocating this space to the object initially in order to be able to maintain/manage it through the cluster chain manager and so afford online features against or available to Type II Areas only.
The only way to 'de-allocate' these clusters would be either:
- To dump records, delete records (or truncate the area), delete that object's related schema then load the schema and data back, or
- An idxmove or tablemove (with parallel index move). The blocks formerly allocated to that index/table in a Type II Storage Area, will be released back onto a general free blocks chain (Master object 0?) for reuse by any of the remaining objects in that Storage Area. Effectively the *move routine re-creates the records new to the new area cluster chains.
