Unanswered: Corrupt Index causing Constraint Errors:
Setting Up the Question
ASE : 5.5
I have a legacy application written in Powerbuilder that has been choking recently on a few client machines. I chased down the cause, which was a Unique constraint being violated during an insert.
This was actually a fairly difficult problem to identify in that probing the database for the ID yielded no pre-existing record that would clash, yet when trying insert a record with that ID, I got the error.
On a hunch, I changed my query from "
select * from T where ID = x
select * from T
and found the ID in the list! After doing some research, I found an article explaining that this behavior can be caused by a damaged table index. So I ran dbValid - c on the database and found my table in the following error list:
Adaptive Server Anywhere Validation Utility Version 126.96.36.19901
Run time SQL error -- Primary key for "Event_Description" has missing index entries
How do I rebuild the index on this table to "unhide" these records? What I've done so far is create a script to read all the rows from the database, generate INSERT Statements and write them to file, then I delete * records from the table and reinsert the records from the file. All the records seem to be accessible now, but I'm wondering if this left holes anywhere.
This version of the database doesn't seem to have any commandline utilities that can rebuild the indexes. Can anyone recommend a good way to do this without having to introduce coded-solutions.
Also, does anyone have an iSQL script that can copy the contents of any given table into a temp table, then delete the contents of said table then repopulate from the temp table in one batch file or stored procedure?