Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1129
  • Last Modified:

FP Index

Looking for some advices on how to fix a corrupt index (index type: FP) in Sybase IQ.

The text standard: sp_iqcheckdb 'repair index' does not work.  Getting error msg 'failed to create FP indexname'
0
tranicus
Asked:
tranicus
  • 2
1 Solution
 
grant300Commented:
I presume you mean IQ 12.6, not 12.06, correct?

How did you discover the index is corrupt?  Do you have any idea how long it has been that way?

Is the column accessible at all?  If it is, you have a couple of choices.
 - You can dump all the data from the table, drop the column, recreate it, and do a load table
 - You can copy just the bad column and rowid to a new table, drop the column (and the FP index with it of course), add the column, and do either an update or dump the column to a file and do a partial width load table.

If you cannot get the data out of the column, you can either drop and recreate the column with no data in it or you will have to restore from the last valid backup.

Do you have any idea what caused the corruption?  There is not much point in trying to fix it if you have a drive or a controller going bad underneath it all.

Regards,
Bill
0
 
tranicusAuthor Commented:
Hello Grant300,

I believed the corruption came from bad drive but Admin can't find the problem thru their tools... sighs.  the table is pretty big, 144 mil records so your second suggestion sounds great!

"You can copy just the bad column and rowid to a new table, drop the column (and the FP index with it of course), add the column, and do either an update or dump the column to a file and do a partial width load table."

But to clarify about copy bad column...  Are you refer to 'select column into #temp'?  
0
 
grant300Commented:
Oh boy, don't use a #temp table.  If anything happens in the middle, you lose the data!!!

No, you want to create a permanent table, probably using SELECT/INTO

SELECT ROWID(SourceTable), BadColumn
    INTO BadColumnTable
   FROM SourceTable

NOTE:  The system must be quiescent while you are doing this or you'll never knit it all back together properly.  Hopefully, you have an adequate maintenance window to work with.

Make sure you have enough temp cache space to make this work.  If the column is narrow, it probably won't be an issue but if it is say a varchar(255), you might want to check it.

BTW, what platform are you running on?  If it is Windows you, the admin can look at the Event Viewer at System events to see if there is any indication what happened.  If it is Linux/Unix, they can take a look at the syslog.  Usually you will see something that indicates a bad controller, or repeated sector read errors, etc.  There is a lot of stuff, you just have to know where to look.

In any event, I would be backing this sucker up very regularly, at least until they found and replaced the offending hardware.

Best of luck,
Bill
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now