Check index returns Index out of sequence.......why?

Sri Sri

Check index returns Index out of sequence.......why?
Dear List,

One of our daily production jobs that has been running happily for many
years suddenly gave a sqlcode -532(referential constraint prevents delete)
on a delete of the parent record, even though there was a check immediately
before that to make sure that all children rows had been deleted. A check
index on the child table's indexes gave a "Index out of sequence" message.
After rebuild was done, the program ran happily again.

The weird thing is that there was no database changes or load,
reorg,recover,runstats etc. done on the parent or child. Only basic sql
operations such as insert/delete by programs are done daily on the tables.
One special characteristic of the tables are that they get inserted with
data upto 100,000 rows or more and then go back to 0 rows every day.

Can anyone enlighten me why a perfectly good index should go out of
sequence suddenly? Thanks,

Sri



Sanjeev (CTS) S

Re: Check index returns Index out of sequence.......why?
(in response to Sri Sri)
Hi Sri,

I am sure you must be having the RID which was causing the problem. Now i
think everything is perfect but the particular row in problem could have
been tracked for the reason. The complete SQLCA message could also have
helped. I have not seen or heard of data inconsistency caused by SQLs.

Let's see if someone else could share some experience of data
inconsistency thru' SQLs.

Regards,
Sanjeev

> -----Original Message-----
> From: Sri Sri [SMTP:[login to unmask email]
> Sent: Monday, January 15, 2001 6:39 AM
> To: [login to unmask email]
> Subject: Check index returns Index out of sequence.......why?
>
> Dear List,
>
> One of our daily production jobs that has been running happily for many
> years suddenly gave a sqlcode -532(referential constraint prevents delete)
> on a delete of the parent record, even though there was a check
> immediately
> before that to make sure that all children rows had been deleted. A check
> index on the child table's indexes gave a "Index out of sequence" message.
> After rebuild was done, the program ran happily again.
>
> The weird thing is that there was no database changes or load,
> reorg,recover,runstats etc. done on the parent or child. Only basic sql
> operations such as insert/delete by programs are done daily on the tables.
> One special characteristic of the tables are that they get inserted with
> data upto 100,000 rows or more and then go back to 0 rows every day.
>
> Can anyone enlighten me why a perfectly good index should go out of
> sequence suddenly? Thanks,
>
> Sri
>
>
>
>
>
-----------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------
This e-mail and any files transmitted with it are for the sole use
of the intended recipient(s) and may contain confidential and privileged information.
If you are not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message. Any unauthorised review, use, disclosure,
dissemination, forwarding, printing or copying of this email or any action taken in
reliance on this e-mail is strictly prohibited and may be unlawful.

Visit us at http://www.cognizant.com
----------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------



Sri Sri

Re: Check index returns Index out of sequence.......why?
(in response to Sanjeev (CTS) S)
Hi Sanjeev,

I have RID , check index returned me only one index key is out of sync.
This program runs every day, and it got problem on this saturday.

It is me the DBA, did not do any thing for this table from couple of
months, one month back we did reorg. During reorg, i did not get any
problem.

This program ran atleast 30 times in production after reorg.
During the day records will be inserted and night this program write
to the file and deletes the records.

Thanks & Regards,
Sri.