How to find & fix corrupted Index

Hello Informix Team, I am facing some index corruption on an Informix IDS database. SELECT * FROM dpc_ent WHERE dent_numcli = 15018 ;This query returns 10086 Records, but not only the requested records 442 record with the column value higher than 15018 79 records with a NULL value 243 records with a value lower than 15018 9322 records with the right value (15018) If I run the following SQL with only one field, I am getting a different result. SELECT dent_numcli FROM dpc_ent WHERE dent_numcli = 15018 ;13251 Records with value = 15018 A SET Explain output for both requests. Maybe it can help. QUERY: (OPTIMIZATION TIMESTAMP: 01-15-2020 09:27:35) SELECT * FROM dpc_ent WHERE dent_numcli = 15018 ;Estimated Cost: 12965 Estimated # of Rows Returned: 13239 1) regix.dpc_ent: INDEX PATH (SKIP SCAN) (1) Index Name: regix.i_dent2 Index Keys: dent_numcli (Serial, fragments: ALL) Lower Index Filter: regix.dpc_ent.dent_numcli = 15018 Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 dpc_ent type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 10086 13239 10086 00:00.11 12966 QUERY: (OPTIMIZATION TIMESTAMP: 01-15-2020 09:27:48) SELECT dent_numcli FROM dpc_ent WHERE dent_numcli = 15018 ;Estimated Cost: 450 Estimated # of Rows Returned: 13239 1) regix.dpc_ent: INDEX PATH (1) Index Name: regix.i_dent2 Index Keys: dent_numcli (Key-Only) (Serial, fragments: ALL) Lower Index Filter: regix.dpc_ent.dent_numcli = 15018 Query statistics: ----------------- Table map : ------------------------------ Internal name Table name ------------------------------ t1 dpc_ent type table rows_prod est_rows rows_scan time est_cost ------------------------------ scan t1 13251 13239 13251 00:00.00 450 I tried to run the oncheck command with different options, but I never got any issue on this tables. oncheck -cr ... no issue oncheck -cc ... no issue oncheck -cDI magix_prod24:dpc_ent Validating indexes for magix_prod24:regix.dpc_ent...Index i_dent2 Index fragment partition datadbs in DBspace datadbs Index i_dent Index fragment partition datadbs in DBspace datadbs Index i_dent_wentid Index fragment partition datadbs in DBspace datadbs TBLspace data check for magix_prod24:regix.dpc_ent The index was dropped, and recreated. But the issue is still there. Any help will be very useful to fix this issue, and to find out if other tables have the same problem. Kind Regards, ------------------------------ Philippe Fornaciari ------------------------------ #Informix

Philippe: This is not an index corruption issue. It looks like a bug pure and simple (or not simple). Please reach out to IBM and open a support case! Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.

What is the column datatype (the column with the WHERE condition)? What is the isolation level that you are using? You should run the "wrong" query with a directive to avoid INDEX SKIP SCAN... I would have to look it up... I see 3 possible issues conditioning this, but sounds like a bug...: 1- If the column is CHAR/VARCHAR, the fact that you're using an INTEGER may cause strange (but correct) effects (but I have big doubts that it would explain the effects you're seeing) 2- If you're using DIRTY READ, the time between gathering the rowids from the index and obtaining the data from the data pages (this is an effect of SKIP SCAN) could also (I suppose) cause some weird effects if there is a lot of activity on the table 3- There is a bug in the SKIP SCAN code... Regards ------------------------------ FERNANDO NUNES ------------------------------

Can we have Informix version information? ------------------------------ Andreas Legner ------------------------------

Hello all, Thanks for your answers. Some more information for this issue. Informix Version : IBM Informix Dynamic Server Version 12.10.FC10 Software Serial OS Version : Linux : CentOS Linux release 7.6.1810 The datatype for this column is INTEGER, We did the test with Dirty Read connexion, but there was no other connection on the Database to explain this behavior. We have this result at any time running this query. Best Regards, ------------------------------ Philippe Fornaciari ------------------------------