Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  How to find & fix corrupted Index

    Posted Fri January 17, 2020 11:29 AM
    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


  • 2.  RE: How to find & fix corrupted Index

    Posted Fri January 17, 2020 12:09 PM
    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


    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.






    Original Message


  • 3.  RE: How to find & fix corrupted Index

    Posted Fri January 17, 2020 01:52 PM

    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
    ------------------------------

    Original Message


  • 4.  RE: How to find & fix corrupted Index

    Posted Fri January 17, 2020 05:12 PM
    Can we have Informix version information?

    ------------------------------
    Andreas Legner
    ------------------------------

    Original Message


  • 5.  RE: How to find & fix corrupted Index

    Posted Mon January 20, 2020 03:19 AM
    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
    ------------------------------

    Original Message


Related Content