Kbase P68731: How to perform an idxbuild on original tables / indexes when an idxbuild operation fails.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
14/02/2005 |
|
Status: Verified
GOAL:
How to perform an idxbuild on original tables / indexes when an idxbuild operation fails.
GOAL:
Why does a failed idxbuild result in the subsequent idxbuild, rebuilding all indexes?
GOAL:
How to work around a failed idxbuild when the .xb file is missing?
GOAL:
What is a .xb file used for?
FIX:
The idxbuild qualifier to the proutil command, accomplishes most of its work without displaying messages, unless it encounters an error condition.
If an idxbuild is interrupted while rebuilding a selected subset of indexes, the list of selected indexes is usually retained in a file named <dbname>.xb. The <dbname>.xb file is used by the proutil command when the idxbuild utility is restarted so that only the original listing of indexes are rebuilt when the idxbuild utility is run again.
If the idxbuild utility is unable to locate the <dbname>.xb file, it will assume ALL on the subsequent idxbuild command and will deactivate all indexes for the database. This action results in the user having to rebuild all indexes for a database instead of the original selected subset. This can be a very costly oversight in regards to time.
In versions of Progress prior to 9.1D09, if an idxbuild terminates for any reason, the DBA should ensure that the <dbname>.xb file exists in the current working directory where the idxbuild command was issued. If the file does not exist, then you will want to create a new <dbname>.xb file. This file must contain an exact listing of all tables/indexes that were being rebuilt prior to the failure. Failure to adhere to this recommendation could be potentially dangerous and result in index corruption. Especially if a user made a mistake in the <dbname>.xb, for example, put wrong tables/indexes or missed some tables/indexes in <dbname>.xb file. If this happens, idxbuild will finish without reporting errors. However, the database can be damaged and the user won't realize it until the database makes use of the damaged indexes.
Format of the dbname.xb file:
The file must use the <dbname>.xb as its file name. Example: sports2000.xb This file must exist in the 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 must follow the format of:
tableowner
tablename
indexname
This format needs to be repeated for all tables and indexes associated with the failed idxbuild. The file also needs to have an exclamation point (!) for its final entry. Example <dbname>.xb file below:
PUB
customer
name
PUB
customer
custnum
PUB
order
custorder
PUB
order
orderdate
!
When the idxbuild utility is rerun, it will locate the <dbname>.xb file and display all the tables and indexes which need to be rebuilt. It will also prompt for your approval prior to starting the idxbuild. It will then ask for information regarding sort space, then proceed with the index rebuild.
Upon successful completion of the idxbuild operation, the <dbname>.xb file is deleted.