Reorganising with Rebuild

1. On the Database Reorganisation menu, select option 3 - Rebuild data and index files. DP4 displays a message for each stage of the reorganisation. There are three main stages:

   • Rebuilding the data file, and indexing of the dictionary tables. The details of this stage are rather different in release 4.526/4.622 from earlier DP4 releases:

     • Prior to release 4.622, or if the -CORRUPT command tail is specified, REORGDB builds the new data file by reading the original data file and copying records from the old data file to the new file, making any necessary changes due to data independence, or changes to the setting of the encryption/compression flags for the table. If the -ALTSORT command tail was specified the data is partially sorted, so that records that are close together in the primary indexesd are arranged sequentially in the data file. This can dramatically improve the performance of the database. The -SORT command tail arranges records so that records from related tables are grouped (so that, on SALESORD for example, each order would be followed by its order lines). Tests on very large DP4 databases indicate that the -ALTSORT command tail gives all round best performance.
     • From release 4.622, REORGDB builds the new data file by using the existing index to fetch all the data records in order, thus building a perfectly sorted data file. In addition the data in the table is analysed to see if the records would benefit from being being compressed. If enhanced data compression is enabled a more complicated analysis of the data is performed, so that the table can be compressed as efficiently as possible. Unfortunately this means that each table must be read from the existing database twice, which can take a long time if the original data file is badly fragmented.
   • Rebuilding the index file. This may be done in up to four passes: Two to build the primary indexes, and two to build secondary indexes. Creating the primary and secondary indexes separately is actually quicker than doing all the work in a single pass except for small databases. Up to release 4.621 REORGDB uses two passes for each phase of the indexing indexing only part of the data each time because this usually results in a smaller (and hence more efficient) index. This is no longer necessary in 4.622, because the index optimisation has been enhanced and always builds an optimal index file.
   • Optimising the index file, during which the number of index nodes to process and the number of nodes processed so far are displayed. This stage is usually fairly quick, and will not take more than an hour or so for even the largest databases. As already mentioned, as of release 4.622 this phase also ensures that the index is made as small as possible.

2. When the reorganisation utility terminates, press <Enter> to return to the DP4 System menu. Back up the database, using one of the methods described earlier

Crash Recovery

If the machine crashes before the 'Indexing other tables' phase, you can continue the reorganisation by renaming an old index file to the index filename (if you do not have an old index file, use one from another database with the same numbering system and generation number equal to or less than the crashed database), and restarting the reorganisation from the Run program option of the DP4 System Main menu:
REORGDB -CORRUPT
If the machine crashes in the 'Indexing other tables' phase, the original data file has been replaced by a new file called <DB_NAME>.$$D. Rename this file to <DB_NAME>.DAT, copy an old index file to <DB_NAME>.IND and restart the reorganisation with REORGDB -CORRUPT. By now the data file has been rebuilt, so select option 1 from the Database Reorganisation menu; it will be faster than option 3.