Consultor Eletrônico

Status: Verified

GOAL:

How to build an index list for idxbuild or idxfix containing only inactive indexes?

GOAL:

How to use 4GL to generate a list of inactive indexes to use with idxbuild or idxfix?

FIX:

This small piece of code will generate a file with the list of inactive indexes within your database, in the format that's needed to redirect to an idxbuild, idxfix command utility.
Alternatively, connect to the database via Data Dictionary and run a Detailed Table report to determine which indexes are inactive for a table.

OUTPUT TO index.file.
FOR EACH _file WHERE _file-number > 0 AND NOT _file-name BEGINS "SYS":
FOR EACH _index OF _file WHERE _active = NO:
DISPLAY _file-name SKIP _index-name SKIP WITH NO-LABELS.
END.
END.
OUTPUT CLOSE.

Find below the usage of the "index.file" within a sample for idxbuild and a sample for idxfix, as tested with version 9.1D on UNIX.

For idxbuild, you will need to modify "index.file" by:
a) Adding in the first (empty) line:

some

b) Adding the following 3 lines at the end of your list of indexes:
!
y
y

Then run the following Progress utility, which will rebuild and re-activate the indexes on successful completion.
$ proutil <dbname> -C idxbuild -TB 24 -TM 32 -B 1024 < index.file

For idxfix on-line, you will need to modify "index.file" by
a) Removing the first empty line.
b) Adding the following 2 lines at the top:

3
some

c) Adding at the end of your list of indexes, the following lines:

!
y
y
y
all
y

and you run the following Progress utility:
proutil <dbname> -C idxfix -TB 24 -TM 32 < index.file

NOTE: an ONLINE idxfix, will build the inactive indexes but not activate them. To activate them the above idxfix needs to re-run offline, which would be faster than running it offline from the start. The offline idxbuild is preferable.


** Progress strongly recommends you backup your database /before/ attempting any repair routine. If you cannot complete an index rebuild against your database, you will need to resort to the backup. **