A valid and verified database backup needs to be taken before attempting any repair routine. If an index rebuild does not complete for any reason, the database is inaccessible by any means until the flag in the master block is cleared by a successful index build. Otherwise, the backup will need to be restored.
How does IDXBUILD use the .xb file?
The. xb file is only created when a selective idxbuild operation is run on a restricted subset of indexes. This file is only created when the 'some', 'area', 'table or 'schema' IDXBUILD Options are used for IDXBUILD. The .xb file is never created when the idxbuild 'all' option is used.
Since OpenEdge 10.0B, when an idxbuild is interrupted the list of selected indexes is retained in a file named <dbname>.xb. This .xb file is used when the IDXBUILD utility is restarted, so that only the original subset listing of indexes are rebuilt in subsequent runs. The second idxbuild does not remember where it left off. In other words it uses the <dbname>.xb file to rebuild all listed indexes it was building prior to the point of failure.
Why does a failed idxbuild result in the subsequent idxbuild, rebuilding all indexes?
When the IDXBUILD utility is unable to locate the <dbname>.xb file, it will assume ALL indexes on the subsequent run and will deactivate all indexes for the database then rebuild them. Depending on the size of the database and the number of indices, the subsequent rebuild all indexes for a database instead of the original selected subset can be a very costly oversight with regards to time and system resources.
It is encouraged that if a subset IDXBUILD terminates for any reason, before re-running the index build operation again, first ensure that the <dbname>.xb file exists in the current working directory where the IDXBUILD command is being issued and back it up as an extra precaution.
If the .xb file does not exist, then a <dbname>.xb text file can be manually created. This text file must contain the same subset listing of all tables/indexes that were being rebuilt in the prior failure. Failure to adhere to this recommendation will result in index corruption. In most cases, IDXBUILD will finish without reporting errors, however, when the application makes use of these damaged indexes (that were missing in the .xb file), the consequences will be realized until the remaining indexes are rebuilt.
It is however possible, even when an .xb file exists, to re-run the idxbuild on a very small subset of indexes in order to gain access to the database once the master block is cleared. For example: to add more area extent space for the indexes to be built in or additional sort space for the index build operation itself. Once these operations are completed, a rebuild of all inactive indexes can be carried out.
Manually creating the dbname.xb file:
- The file needs to have <dbname>.xb for the file name. Example: sports2000.xb
- This <dbname>.xb file must exist in the database directory where the IDXBUILD command is going to be issued. When the IDXBUILD utility is run, it looks in the current directory for the <dbname.xb> file.
- The contents of the <dbname>.xb file has the following format which needs to be repeated for all tables and indexes associated with the failed IDXBUILD.
tableowner
tablename
indexname
- The file also needs to have an "!" exclamation mark for the final entry and a new line.
- When the IDXBUILD utility is rerun, it will locate the <dbname>.xb file and display to the screen all tables and indexes which need to be rebuilt then prompt for approval prior to starting the IDXBUILD. It will then ask for information regarding sort space and then proceed with the rebuild.
- On successful completion of a subset index rebuild the <dbname>.xb file is deleted.
Note: If you answer ‘N’ to the prompt “Do you want to continue Y/N?” during a failed or partial selective IDXBUILD attempt, OpenEdge will delete the existing <dbname>.xb file. This requires manually recreating the .xb file before the IDXBUILD can be restarted.
Example: sports2000.xb file:
PUB
customer
name
PUB
customer
custnum
PUB
order
custorder
PUB
order
orderdate
!
<CR here>
