?
Solved

FP Index

Posted on 2008-06-18
3
Medium Priority
?
1,116 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 1000 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Google webmaster tool or Google search console is an imperative tool for solving different website relevant issues. The amazing features of this tool greatly help to check the indexing and improve the visibility of the site.
If you need to complete a Physical to Virtual (P2V), Virtual to Virtual (V2V) conversion to a VMware product (VMware Workstation, Player or VMware vSphere (ESXi) ) for FREE, then there is some good news...
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

621 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