Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

FP Index

Posted on 2008-06-18
3
Medium Priority
?
1,100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
By default Outlook 2016 displays only one time zone in the Calendar. The following article explains how to display two time zones in one calendar view.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

722 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