Fast/Reload performance
In general, UAI/LAI is the fastest way of performing a database file reorganization.
Two key options affect both FILELOAD/FLOD performance and Online performance:
- Whether the unload/reload is to be split up into passes.
- Whether Ordered Index data is to be unloaded via UAI OINDEX and reloaded via LAI.
One pass versus multiple for unload/reload
In general, unloading and reloading data in a single pass provides optimal Online performance:
- It minimizes the number of list pages used for a particular fieldname-value pair.
- It ensures that index data associated with a particular fieldname-value pair will tend to be localized on disk, minimizing head movement and maximizing cache hit ratios.
If it is not possible to unload and reload the data in a single pass because of limitations in intermediate sort work space, there are a few things to keep in mind:
- If you are not sorting the data on the UAI, it is more efficient to unload
the data into multiple separate data sets and then load these data sets in
multiple reload steps.
By doing this, you avoid having to skip input records after the first reload step.
In addition, if unloading Ordered Index data, you eliminate the need to skip intermediate records to get to the Ordered Index data in each reload step.
Finally, you reduce the amount of Ordered Index data that must be scanned on each reload step, since each step must scan all Ordered Index data unloaded.
- If you must sort the data on the UAI, you might actually get better performance
by regenerating the Ordered Index data, rather than unloading it via UAI OINDEX and then reloading it.
This is especially true if you have a large amount of Ordered Index data and you are using many reload passes. You might consider using
UAI INVISIBLEin this case, if you want to preserve invisible Ordered Index values.
When unloading and reloading data in a single pass, it is generally more efficient to use the OINDEX parameter of UAI to unload the Ordered Index data than to regenerate the Ordered Index data, because the Ordered Index data will already be in sorted order in the old database file.
Notable UAI OINDEX and UAI INVISIBLE memory requirements
You must be aware, however, that using the UAI OINDEX or UAI INVISIBLE feature can place significant real memory requirements on the reload, because the reload must map old record numbers to new record numbers. The table that is used for this mapping must reside in real storage for adequate performance, since the entries tend to be accessed in arbitrary order. Any paging for this mapping table could have disastrous effects on reload performance.
The amount of storage required for this mapping table depends on two factors:
- The range of record numbers being loaded.
The number of entries in the mapping table will equal the range of record numbers unloaded. This will generally be BSIZE times BRECPPG for hash key files, and BHIGHPG times BRECPPG for other files.
For example, if you are loading data unloaded from an entry order file with BHIGHPG of 15,000 and BRECPPG of 100, the mapping table will have 1,500,000 entries. By splitting the unload or reload into multiple passes, you will reduce the number of entries in the mapping table.
- Whether the data was sorted on the UAI.
If the data was sorted, the record number mapping will tend to be random and thus require 3 bytes per entry in the record mapping table. If the data was not sorted, the data will have a structure that allows the record mapping table to require only 1 byte per entry.
Thus, the maximum possible storage requirement for the record mapping table would be 16 million (maximum record numbers allowed in a Model 204 file) times 3 bytes or 48 million bytes.