The best way to add a new index to an existing database with a large number of records is to define the new index as
inactive in the Data Dictionary or .df file. This will postpone the generation of the index keys.
It is strongly recommended that
largefiles are enabled for the database to avoid hitting file maximum sizes, which requires the Enterprise RDBMS license. Large files support was introduced in Progress 9.1C on most platforms. It is enabled by default since OpenEdge 12.
To enable large files use the following command:
$ proutil <dbname> -C enablelargefiles
Method 1: Run IDXBUILD offlineThe quickest way to activate an index is to take the database offline and build the index with the IDXBUILD utility
- A verified backup of the database is required in the event the idxbuild fails, which will render the database inaccessible until the IDXBUILD completes
- After-imaging must be disabled before running an IDXBUILD
Example:
- proutil sports2000 -C idxbuild -B 32000 -TB 64 -TM 32 -TMB 128 -TF 60 -SG 64 -thread 1 -threadnum 4 -mergethreads 4 -datascanthreads 4 -pfactor 90 -rusage -SS /path/pcs-base.srt
- Choose the some option.
- Provide the table name
- Provide the index name
- Enter ! to end the table and index selection.
- Enter y to indicate sufficient sort space.
Refer to Articles:
Method 2. Run IDXACTIVATE online If the database cannot be taken offline, then add the index as
INACTIVE and use IDXACTIVATE to build the index ideally with an active unique index and Database Client Notification active (-usernotifytime). For further information refer to Article:
What is IDXACTIVATE? Example: IDXACTIVATE is scoped to building the inactive index from the key-fields in 500 records at a time, reporting on clients blocking activation every 10 seconds
$ proutil sports2000 -C idxactivate pub.customer.name useindex cust-num recs 500 refresh 10
