Solved

FP Index

Posted on 2008-06-18
3
1,062 Views
Last Modified: 2009-11-14
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
Comment
Question by:tranicus
  • 2
3 Comments
 
LVL 19

Expert Comment

by:grant300
ID: 21816045
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
 

Author Comment

by:tranicus
ID: 21816241
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
 
LVL 19

Accepted Solution

by:
grant300 earned 250 total points
ID: 21817091
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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: This is the second blog post in a series on email clearinghouses (https://www.xmatters.com/alert-management/blog-email-has-failed-us?utm_campaign=70138000000ydLoAAI&utm_source=exex&utm_medium=article&utm_content=blog-post).   Every month t…
An analysis of the phishing scam that has been affecting Google users, along with steps to take for protection, as well as what to do if you receive one of the emails.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now