Building Indexes with Binary Load

Building Indexes with Binary Load in Progress Version 9.1B and later.
How to build indexes with Binary Load in Progress 9.1B and later?

Two parameters were added in Progress 9.1B to rebuild indexes during a binary load : build indexes and -SS.

With these additions, the syntax of a binary load operation is as follows:

$   proutil <dbname> -C load <dump file name> [build indexes] [-TB <n>] [-TM <n>] [-T <temp-dir> | -SS <sort file directory specification>]

1.  build indexes

Indicates that PROUTIL LOAD will simultaneously build the indexes and perform the load, if the table is empty prior to the start of the load.

A binary load with the build indexes option, marks all existing indexes as inactive. 
As the data are loaded, the keys for each row are constructed and added to a sort file, just as index rebuild does when sorting is used. 
At the end of loading the data, the sort files are sorted and the indexes are built, also just as the index rebuild does in other versions.
Once PROUTIL successfully creates the indexes, it marks the indexes active. 

In Progress 9.1B, binary dump file will be dumped in order of one of the indexes in the table (Primary Index by default or the index specified with 'use index). When this index is built while the loading is done it will be able to do so without any sorting at all. However the 'build indexes' option builds all indexes, it is not possible to selectively build indexes during binary load.
 
If the binary dump process created multiple binary dump files (. bd, .bd2, .bd3, etc.) the build indexes option cannot be used. Unless, starting in OpenEdge 10.1x, a -dumplist file was created during the binary dump and used for the subsequent binary load so that all the binary data are all loaded in one session to an empty table prior to the start of the load. Instead, indexes will need to be build after the binary load (without build indexes option).

In addition, for OpenEdge 11 Multi-tenant or Partitioned tables:

If tenant, group, partition, or composite initial is specified, build indexes only builds the index for the specified partition.
For partition and composite initial global indexes are not deactivated during the load.
For a partitioned database when partition or composite initial is not specified, global indexes are deactivated and rebuilt during the load.

Performance of build indexes:

In theory an online binary load that builds indexes while loading data should result in better performance than a binary load with a separate idxbuild session (offline) because when idxbuild is run separately, data has to be written to the database during  binary load and then it has to be re-read for sorting during idxbuild; binary load with the build indexes option eliminates the need for the re-read operation. Since the performant improvements to IDXBUILD starting OpenEdge 10.2B06, it is faster to load data first and IDXBUILD later, as the binary load with build indexes has not been enhanced to take advantage of these IDXBUILD performant parameters: As with all performance considerations, the exact performant gain would need to be gauged in individual application environments.

2.  -SS <sort-file-directory-specification>

When the Sort Directory Specification (-SS) parameter is used, PROUTIL does not use the Temporary Directory (-T) parameter.

The -SS parameter is used to identify the location of a multi-volume sort file specification. This sort file shows the directories and the amount of available space in the directories that can be used for creation of temporary files for sorting. The syntax of the multi-volume sort file specification is the same as that used for index rebuild.

For example, in order to load the customer table, build the indexes, and use a multi volume sort file called /tmp/srtload (the file no longer requires the .srt extension), use the following syntax:

$   proutil dbname -C load customer.bd build indexes -SS /tmp/srtload

Binary Dump and Load between 9.1B and earlier versions:

A binary dump file created using PROUTIL DUMP from Progress version 9.0 or later cannot be loaded with PROUTIL LOAD from Version 8.3 or earlier. The earlier versions of PROUTIL DUMP and LOAD did not use CRC values to establish the criteria for loading, but instead used other mechanisms, such as:

•  Looking up table RECIDs in a target database using the table number stored in the header of the binary dump file
•  Matching table numbers in the header of the binary dump file with table numbers in a target database
•  Comparing the number of fields in the binary dump file with the number of fields in the target database

A pre Progress 9.0 binary dump files created with a previous version of the PROUTIL DUMP utility, can be loaded in Progress 9, because the Progress 9 version of PROUTIL LOAD uses the CRC value established when the file was originally dumped. Consequently, the OpenEdge database maintains backwards compatibility and allows the advantage of building the indexes while you load the previous version's dump file. In this case, all indexes are built from the sort file (since the name of the index used to dump the table is not known).

References to Other Documentation:
Progress Article(s):
How to scope and define a multi-volume srt file for idxbuild   
Error 'build indexes' Option is Valid Only for Empty Tables