Release 10.1A: OpenEdge Data Management:
Database Administration

> ## PROUTIL IDXBUILD qualifier

> Rebuilds an index, packing or consolidating index records to use disk space as efficiently as possible.

> proutil `db-name` -C idxbuild [ all |table [`owner-name.`]`table-name` | area `area-name` | schema `schema-owner` | activeindexes | inactiveindexes] [ -threads `n` ] [ -threadnum `n` ] [ -T `dir-name` | -SS `sort-file-directory-specification` ] [ -TB `blocksize` ] [ -TM `n` ] [ -B `n` ] [ -SG `n` ]

> #### Parameters

> ``db-name``

> Specifies the database you are using.

> `all`

> Specifies that you want to rebuild all your indexes. PROUTIL automatically rebuilds all your indexes without asking about disk space requirements.

> `table [`owner-name.`]`table-name``

> Specifies that you want to rebuild the indexes defined for the named table. When the `table` option is used on the command line, PROUTIL automatically rebuilds the indexes defined for the table without asking about disk space requirements.

> `area `area-name``

> Specifies that you want to rebuild all the indexes defined in the named area. When the `area` option is used on the command line, PROUTIL automatically rebuilds the indexes defined for the area without asking about disk space requirements.

> `schema `schema-owner``

> Specifies that you want to rebuild all the indexes owned by the named `schema-owner`. When the `schema` option is used on the command line, PROUTIL automatically rebuilds the indexes owned by the `schema-owner` without asking about disk space requirements.

> `activeindexes`

> Specifies that you want all currently active indexes rebuilt.

> `inactiveindexes`

> Specifies that you want all currently inactive indexes rebuilt. When an inactive index is rebuilt, it becomes active.

> `-threads `n``

> For Enterprise licenses, IDXBUILD is a multi-threaded operation by default. The `-threads` parameter allows you to turn off multi-threading. By default, `n` is equal to 1, indicating that the IDXBUILD is multi-threaded. Setting `n` to 0 turns off multi-threading.

> `-threadnum `n``

> For Enterprise licenses, when IDXBUILD is executed in a multi-threaded mode, the `-threadnum` parameter allows you to control the maximum number of threads created. By default, the maximum number of threads, `n`, is equal to the number of CPUs. Setting `n` to 0 turns off multi-threading.

> `-`T` `dir-name``

> Specifies the name of the directory where the temporary files are stored. If you do not use this parameter, PROUTIL places temporary files in the current working directory.

> ``-SS` `sort-file-directory-specification``

> Identifies the location of a multi-volume sort file specification. If you use the Sort Directory Specification (`-SS`) parameter, PROUTIL does not use the Temporary Directory (`-T`) parameter.

> ``-TB` `n``

> Specifies that the index rebuild will be performed using Speed Sort. `n` indicates the allocated block size, in kilobytes.

> ``-TM` `n``

> Specifies the merge number. `n` indicates the number of blocks or streams to be merged during the sort process.

> ``-B` `n``

> Specifies the number of blocks in the database buffers.

> ``-SG` `n``

> Specifies that the index rebuild will use index grouping. `n` indicates the number of index groups used by IDXBUILD and must be a value between 8 and 64. The default value is 48. Note that a temporary file is created for each index group.

> A large `-SG` value requires more memory allocation and more file handles. To determine the amount of memory (in kilobytes) needed for each index group, add 1 to the merge number (the value of `-TM`) and multiply the sum by the speed sort block size (the value of `-TB`). Memory consumption for each index group equals (`-TM` + 1) * `-TB`.

> If you do not specify `all`, `table`, `area`, or `schema`, the following menu appears:

>           Index Rebuild Utility
>           =====================
>           Select one of the following:
>           All           (a/A) - Rebuild all the indexes
>           Some          (s/S) - Rebuild only some of the indexes
>           By Area       (r/R) - Rebuild indexes in selected areas
>           By Schema     (c/C) - Rebuild indexes  by schema owners
>           By Table      (t/T) - Rebuild indexes in selected tables
>           By Activation (v/V) - Rebuild selected active or inactive indexes
>           Quit     (q/Q) - Quit, do not rebuild
>           Enter your selection:

> Table 20–2 describes the options:

> Table 20–2: PROUTIL IDXBUILD options
> Option | Action
> --- | ---
> All | Prompts you to verify whether you have enough disk space for index sorting.
> Some | Prompts you for the indexes you want to rebuild by first entering the table name, and then the index name. IDXBUILD then prompts you to verify whether you have enough disk space for index sorting.
> By Area | Prompts you for the area containing the indexes you want to rebuild, then prompts you for the indexes in the area, then prompts you to verify whether you have enough disk space for index sorting.
> By Schema | Prompts you for the schema owner of the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
> By Table | Prompts you for the table containing the indexes you want to rebuild, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
> By Activation | Prompts you to chose active or inactive indexes, then prompts you for the indexes, then prompts you to verify whether you have enough disk space for index sorting.
> Quit | Quits without rebuilding any indexes.

> In addition to rebuilding an index, IDXBUILD also:

> - Compresses index blocks to minimize space usage.

> - Activates deactivated indexes in the database.

> - Repairs corrupted indexes in the database. (Index corruption is typically signaled by error messages.)

> #### Notes

> - Backup your database prior to executing IDXBUILD. Under certain conditions, if the index rebuild crashes, the only method of recovery is to restore a backup.

> - Use IDXFIX to repair and IDXCOMPACT to compress online.

> - Use the Temporary Directory (-`T`) startup parameter to identify or redirect temporary files created by the PROUTIL utility to a specified directory when sorting and handling space issues.

> - Use the Speed Sort (-`TB`), Merge Number (-`TM`), Sort Grouping (`-SG`) and Blocks in Database Buffers (-`B`) startup parameters to improve index rebuild performance.

> - IDXBUILD does not repair corrupted record data.

> - Use the following formulas to determine whether you have enough free space to sort the indexes:

> - If you rebuild all the indexes in your database, sorting the indexes requires free space that can be as much as 75 percent of the total database size.
> - If you rebuild an individual index, sorting that index requires free space that can be the size of one index entry * the number of records in the file * 3.
> - The Index Rebuild utility rebuilds an index or set of indexes in three phases:

> - The utility scans the database by area, clearing all index blocks that belong to the indexes and rebuilds and adds those blocks to the free-block list.
> - The utility scans the database by area and rebuilds all the index entries for every data record. If you chose to sort the index, the utility writes the index entries to the sort file. Otherwise, the utility writes the index entries to the appropriate index at this point.
> - If you indicated that you have enough space to sort the indexes, the utility compacts the index by sorting the index entries in the sort file into groups and entering those entries in one index at a time.
> - For more information about using the IDXBUILD qualifier, see Chapter 13, " Managing Performance."

Copyright © 2005 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095