At one point a stored procedure was working correctly and then we decided to insert rows into a table to do further testing. Not sure what got messed up after the insertion but after that sybase started giving an error message-
Fatal Error 644 occurred at Apr 25 2011 2:05PM. Please note the error and time, and contact a user with System Administrator (SA) authorization.
00:00000:00074:2011/04/26 14:05:39.42 server Index row entry for data row id (23693, 6) is missing from index page 41953 of index id 2 of table 'employee_table' in database 'sybasedb_user'. Xactid is (51051,11). Drop and re-create the index.
We tried dropping the table and recreating all indexes, but the problem remained.
After doing quite some analysis, we found that the query plan being used for one of the sql in the stored procedure was using a merge_join. There is supposedly a bug in sybase ver15.0 that causes the above mentioned error. We had to include a statement "set merge_join off" in the stored procedure to get rid of this error and the stored procedure started working fine again.
My question is, why did the sp start to fail only after the inserts were done to the table and not before - when it was working just fine - and when we did not even need the statement "set merge_join off" . What during the simple insert statement would have caused Sybase to give the fatal errror.