Urgent from Sybase: Possible data integrity issues caused to FP indexes on Sybase IQ 12.6 ESD# 5 & ESD# 6 after performing update operations
Summary: This document describes an issue that can affect the integrity of FP indexes after performing an UPDATE operation on char, varchar or varbinary columns. In order to be affected by this issue the columns must have a defined width greater than 255 bytes or be clob/blob columns whose data widths are less than 32768 bytes. Only versions of Sybase IQ 12.6 ESD# 5 and ESD# 6 are affected by this issue. The issue is extremely data pattern dependent and is much more likely to occur when updating more than 10,000 rows in a single update command.
This document contains the following sections:
- Customer Alert
Under certain circumstances UPDATE commands performed on char, varchar or varbinary columns with a defined width greater than 255 bytes, or clob/blob columns whose data widths are less than 32768 bytes, may cause FP index inconsistencies. The inconsistencies can cause non-fatal s_blockmap errors.
The issue is extremely data pattern dependent and is much more likely to occur when updating more than 10,000 rows in a single update command.
The issue does NOT occur if any of the following are true:
- All rows in the column are updated in a single update command
- All rows being updated were originally all NULL or zero-length strings
- All rows being updated are set to NULL or zero-length strings
- It is a single table update (no joins involved) whose where clause specifies single column predicates against constants
To determine if your database has IQ indexes on column types that could potentially be affected run the following query while connected to the database:
select column_name, table_name, width, t.table_id, domain_id
from sysiqcolumn a, syscolumn b, systable t
where a.column_id = b.column_id
and a.table_id = b.table_id
and a.table_id = t.table_id
and server_type = 'IQ' and width > 255
If no rows are returned from the above query the database does not contain FP indexes that could be affected by this issue. If however rows are returned this does not mean the indexes have been affected but it does mean they are at risk.
This issue is being tracked under Sybase CR# 425124. This CR is resolved in the following EBFs, or ones that supersede them.
|Platform||EBF# (ESD# 6.2)|
|IBM AIX 64-bit||13517|
|HP-UX PA-RISC 64-bit||13518|
|HP-UX Itanium 64-bit||13519|
|Linux x86 32-bit||13520|
|Linux x86 64-bit||13521|
|Linux on POWER 64-bit||13522|
|Sun Solaris x64 64-bit||13523|
|Sun Solaris SPARC 64-bit||13524|
|Windows x86 32-bit||13525|
Note: Once one of the above EBFs has been applied you must execute the steps described below in order to detect and correct any issue that may have already arisen.
The first step is to execute the sp_iqcheckdb command to determine if any indexes have been affected.
sp_iqcheckdb 'check database indextype FP'
If no errors are reported, then your database was unaffected by the issue and no further action is required.
If inconsistent FP indexes are reported run the following command for each inconsistent index:
sp_iqcheckdb 'check index <index_name>'
A list of affected rowids will be printed in the iqmsg file preceded by the following line, where # is the number of rowids:
DBCC owner.table.indexname: # inconsistent ROWIDS
To resolve the inconsistencies the following steps must be repeated for each inconsistent FP index reported. Use the sp_iqindex procedure to determine the column name that corresponds to the FP index name.
- Add a new column to the table with the same definition as the original column with the inconsistent FP index, e.g.
- Update the new column with the data from the original column excluding all rows reported in the iqmsg file under "inconsistent ROWIDS", e.g.
- Match each rowid reported in the "inconsistent ROWIDS" list to the table's primary key or if no primary key exists, use data values from other columns that in combination uniquely identify the row. Use this information to determine the correct column value from the original data source used to populate the column. Update the new column based on this information, e.g.
- After step 3 is complete for each inconsistent rowid reported, drop the original column and rename the new column to the original name, e.g.
- If the original column had WD or CMP indexes, recreate them.
- After steps 1 to 5 have been completed for each inconsistent FP index reported run sp_iqcheckdb 'check database indextype FP' a final time to confirm no errors remain.
ALTER TABLE <table_name> ADD <new_colname> <type> (<width>) NULL
UPDATE <table_name> SET <new_colname> = <original_colname>
WHERE ROWID(<table_name>) NOT IN (<list_of_inconsistent_rowids>)
UPDATE <table_name> SET <new_colname> = <value>
WHERE ROWID(<table_name>) = <rowid_value>
ALTER TABLE <table_name> DROP <original_colname>;
ALTER TABLE <table_name> RENAME <new_colname> TO <original_colname>
If the original column was defined as NOT NULL, alter the column to add the not null specification, e.g.
ALTER TABLE <table_name> MODIFY <original_colname> NOT NULL
Sybase strongly recommends that if you have, or intend to use, datatypes that could be affected you apply the appropriate EBF and complete the follow-up actions.
The EBFs can be obtained from the Sybase EBFs and Maintenance site.
Follow the instructions in the EBF cover letter to install the EBF.
If you require further assistance please contact your local support center. The contact numbers can be found in the About Support section under Support & Services at the www.sybase.com website.
Copyright © 2006 Sybase, Inc. All rights reserved.