OE 10.2B08, Windows
As part of a dump and reload I was requested to add a unique index on a table. All well and good except unbeknown to me there are duplicate values in the index. So the rebuild index reported a large number of errors. It's a simple fix in theory because the unique field is now sequence bound. I thought I'd just parse the logfile and find the records that failed in the index rebuild using the recid reported, and update them to the next value of the sequence.
Except I get the error
SYSTEM ERROR: Index trx-no in af for recid 1544 could not be deleted. (1422)
Is there an easy way I can get around this?
It is trying to remove the old index entry, but it does not exist, so it fails. Deactivate the index and fix the errors. Then rebuild the index again afterwards.
It is trying to remove the old index entry, but it does not exist, so it fails. Deactivate the index and fix the errors. Then rebuild the index again afterwards.
Thanks Simon. I was hoping it wouldn't come to that. It's a massive table.
James, you can dump a record (or make a buffer copy), delete it using idxfix/6, load it back except the sequence field and update the sequence - all steps in one transaction. Do the same for all records reported in the errors.
Hey George, how does one go around and include idxfix in the same transaction? Or did you mean all steps except idxfix?
Thanks George - unfortunately there's 22k records so I need to automate whatever I do.
> how does one go around and include idxfix in the same transaction?
do transaction:
find MyTable no-lock where recid(MyTable) eq MyRecid.
buffer-copy MyTable to MyTempTable.
os-command("idxfix ...")
create MyTable.
buffer-copy MyTempTable to MyTable.
end.
> unfortunately there's 22k records so I need to automate whatever I do.
In Dan Foreman's books there are the examples of programs that parse db logs exactly for 1422's.
The program that will use the recids from db log is almost ready (see above). ;-)
Thanks George. I've already written code to parse the logs for the errors :) In terms of the os-command, I presume I need to build a text file with the list of commands needed for idxfix? Ie, I can't just do something along the lines of "proutil db -C idxfix recid 183394"?
Yes, you need to create an input file (with recid and area number) for idxfix.
Perfect George. Thank you.
Very interesting one here. I've got some word indexes on the table and when I do the idxfix command as part of a batch file I get the following errors:
(12001) SYSTEM ERROR: proixgen failed for word indexing of array, index after-values of table af.
If I do the idxfix command from a proenv session I don't get the error.
Any ideas? I've tried rebuilding the word indexes but that's not helped.
What is the db codepage? Unicode?
[2017/09/07@17:46:48.509+0100] P-94460 T-94464 I BROKER 0: (4264) Character Set (-cpinternal): ISO8859-1.
find first DICTDB._Db no-lock.
display
dbcodepage("DICTDB") format "x(15)" label "Db codepage"
_db-xl-name format "x(15)" label "Db codepage"
_db-coll-name format "x(15)" label "Db collation".