Informix

 View Only
Expand all | Collapse all

Odd Index Issue

Gaurav Kumar

Gaurav KumarWed August 26, 2020 12:46 AM

  • 1.  Odd Index Issue

    IBM Champion
    Posted Fri August 14, 2020 12:21 PM
    Edited by System Fri January 20, 2023 04:31 PM

    I have an existing table in 12.10.FC14, about 1.8 million records, that has three indexes on it:

    - A unique index on a serial key
    - A composite, six-column unique index
    - A three-column composite index that allows dups

    This table participates in ER in a send-only/receive-only relationship. When doing a cdr check, I noticed that the tables weren't syncing correctly.

    After a bunch of investigation, I found that the main production table, on the send-only node, has duplicate records in it that violate the composite unique index. Yet an oncheck -CDI on the table shows no errors.

    I was able to find, unload and delete the duplicates, but when I load them back up, they load right in without throwing a duplicate index violation error. Yes, I've triple-checked, and the index is enabled. Indeed, I need to force a sequential scan to find the dups, because if I query normally, it uses the index and finds only one.

    As an extra sanity check, I grabbed the schema for the table to a file, using the -ss option, changed the table and index names in the new file and left everything else identical, built the new table, and populated it with the data from the original table, and in the new table, the unique index works exactly as expected.

    An oncheck -cc against the database also returns clean.

    I've never seen anything like this. I would expect the oncheck to produce an error, and I'd certainly expect that when I delete and re-add the duplicate rows, they should be rejected on the reload. It's as if it's just ignoring the word unique.

    I have a ticket open, but I'm wondering if anyone here has seen something like this or has other suggestions.

    Best,

    - TJG



    ------------------------------
    TOM GIRSCH
    ------------------------------
    #Informix


  • 2.  RE: Odd Index Issue

    Posted Mon August 17, 2020 06:28 AM
    Hi Tom,

    May i know if you still have old table in place. If yes then, can you please share the dbschema -ss and some sample data which you are saying are duplicate.

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 3.  RE: Odd Index Issue

    IBM Champion
    Posted Mon August 17, 2020 10:25 AM
    Edited by System Fri January 20, 2023 04:21 PM

    If you have TS system access, look at TS004060227 for details.

    [Corrected the ticket number]
    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 4.  RE: Odd Index Issue

    Posted Mon August 17, 2020 10:46 PM
    Hi Tom,

    Can you please upload onunload of the table that has duplicate values to the case...


    Regards,
    Gaurav

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 5.  RE: Odd Index Issue

    IBM Champion
    Posted Tue August 18, 2020 11:16 AM
    Unfortunately, I can't get onunload to work. It keeps telling me to mount a tape and complaining when I hit enter. It's not recognizing the -FILE option listed in the documentation, and no matter what I specify for TAPEDEV, it fails with either errno=2 or errno=13.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 6.  RE: Odd Index Issue

    IBM Champion
    Posted Tue August 18, 2020 11:18 AM
    Did you pre-create (touch) the unload device?

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



  • 7.  RE: Odd Index Issue

    IBM Champion
    Posted Tue August 18, 2020 11:41 AM
    Tom:

    Just to get the "dumb stuff" out of the way, errno 2 is no such file and errno 13 is no permissions. So, does the TAPEDEV file exist and are the privileges correct?

    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.








  • 8.  RE: Odd Index Issue

    IBM Champion
    Posted Tue August 18, 2020 11:43 AM
    Oh, and the -FILE=<filepath> option is for a file containing environment variables like INFORMIXDIR and INFORMIXSERVER so they don't have to be in the actual environment. That was added to most of the on* tools in v14.10. It doesn't specify the output file for onunload.

    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.








  • 9.  RE: Odd Index Issue

    IBM Champion
    Posted Tue August 18, 2020 12:02 PM
    Figured it out. I had to touch the file AND provide a full path to that touched file. TAPEDEV points to a directory, not a file, and apparently onunload doesn't like that, even though ontape handles that just fine.

    I'd whine that there should be STDIO and/or direct-to-file options, but since this is literally the first time I've ever had need of the utility, probably not worth the effort. ;)

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 10.  RE: Odd Index Issue

    Posted Fri August 21, 2020 01:49 AM
    Hi Tom,

    Can you help me the page size of the dbspaces in which that table and it's index is created....

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 11.  RE: Odd Index Issue

    IBM Champion
    Posted Mon August 24, 2020 08:37 AM

    Gaurav:

    The table is in a space called chaos_globdbs1, which has 2K pages.
    It has three indices in three different spaces: chaos_globidx4, chaos_globidx5 and chaos_globidx1; all of these have 16K pages.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 12.  RE: Odd Index Issue

    Posted Tue August 25, 2020 12:36 AM
    Hi Tom,

    Thanks!!!
    Now i am able to successfully load  your binary dump ((1855548 rows)). But as i tried to load the dups.unl, it gives me error 239/100.

    informix@COMP-1000-1:/data2/gaurav/1210FC14 $ echo "load from dups.unl insert into g_ophours"| dbaccess aeglobal

    Database selected.

    239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:ix_g_ophours_16). 100: ISAM error: duplicate value for a record with unique key.
    847: Error in load file row 1.
    Error in line 2
    Near character position 0

    Database closed.

    Can you please give me another onunload on table having duplicate data? Because with the shared dump, I can not find anything.

    Regards,
    Gaurav




    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 13.  RE: Odd Index Issue

    IBM Champion
    Posted Tue August 25, 2020 08:59 AM
    I have uploaded a new onunload with the ticket; this time it's the table with the duplicates already in it. Based on your results above, I'm guessing the onload is going to fail.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 14.  RE: Odd Index Issue

    Posted Wed August 26, 2020 12:46 AM
    Edited by System Fri January 20, 2023 04:48 PM


  • 15.  RE: Odd Index Issue

    Posted Wed August 26, 2020 04:50 AM
    Edited by System Fri January 20, 2023 04:26 PM
    Hi Tom,

    Can you please dump the partition page of the unique index from both the tables (table having duplicate values and table with unique values)

    How to find partition page of an index
    oncheck pt aeglobal:g_ophours
    and based on the Physical Address (first line) you can dump the partition page and share the data

    for example: when you run the oncheck -pt , you will get partition number of indexes. So you need to dump that partition page

    Index ix_g_ophours_16 fragment partition chaos_globidx4 in DBspace chaos_globidx4

    Physical Address 4:40

    using oncheck -pP 4 40

    On a side note, I would like to know the history of this instance. Like previous corruption, Anytime you skipped logical recovery. And when did you upgrade to 12.10.FC14. What was your previous versions? and when did you created this table?







    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 16.  RE: Odd Index Issue

    IBM Champion
    Posted Wed August 26, 2020 09:24 AM

    I'll try to grab the partition page. But here's the query you need to run to "see" the duplicates:

    SELECT {+FULL(g_ophours)} ophrserial,ophrrtype,ophrmofrom,ophrmothru,ophrdyfrom,ophrdythru,COUNT(*) AS nrows
    FROM g_ophours
    GROUP BY ophrserial,ophrrtype,ophrmofrom,ophrmothru,ophrdyfrom,ophrdythru
    HAVING COUNT(*) > 1



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 17.  RE: Odd Index Issue

    IBM Champion
    Posted Wed August 26, 2020 09:26 AM
    $ oncheck -pP 23 2336 addr stamp chksum nslots flag type frptr frcnt next prev 23:2336 -1930885629 83dd 5 2 PARTN 340 16020 0 0 slot ptr len flg 1 24 136 0 2 160 44 0 3 204 0 0 4 204 116 0 5 320 20 0 slot 1: 0: 61 0 70 1 2 8 0 0 1c 0 0 0 0 0 1 0 a.p............. 16: 1 0 0 40 95 f9 90 58 1 0 0 0 70 56 0 0 ...@.y.X....pV.. 32: 48 11 0 0 72 13 0 0 c0 11 0 0 0 0 0 0 H...r...@....... 48: ff ff ff ff 41 0 f0 0 1 0 0 0 0 0 0 0 ....A.p......... 64: ba b 13 0 0 0 0 0 0 0 0 0 0 0 0 0 :............... 80: 83 25 b 0 0 0 0 0 0 0 0 0 1 0 0 0 .%.............. 96: 0 0 0 0 1 0 0 0 1 0 0 0 0 0 0 0 ................ 112: 1 0 0 0 a 0 0 0 50 f0 a5 b 0 0 0 0 ........Pp%..... 128: 0 0 0 0 9b 61 46 5f .....aF_........ slot 2: 0: 61 65 67 6c 6f 62 61 6c 0 69 6e 66 6f 72 6d 69 aeglobal.informi 16: 78 0 69 78 5f 67 5f 6f 70 68 6f 75 72 73 5f 31 x.ix_g_ophours_1 32: 36 0 65 6e 5f 55 53 2e 38 31 39 0 6.en_US.819..... slot 3: slot 4: 0: 98 2 0 0 6 0 12 0 0 0 0 0 41 0 f0 0 ............A.p. 16: 1 0 0 0 0 0 4 0 2 0 0 0 0 0 0 0 ................ 32: 0 0 0 0 4 0 2 0 0 0 0 0 0 0 0 0 ................ 48: 0 0 0 0 6 0 4 0 2 0 0 0 0 0 0 0 ................ 64: 0 0 0 0 a 0 4 0 2 0 0 0 0 0 0 0 ................ 80: 0 0 0 0 e 0 2 0 1 0 0 0 0 0 0 0 ................ 96: 0 0 0 0 10 0 2 0 1 0 0 0 0 0 0 0 ................ 112: 0 0 0 0 ................ slot 5: 0: 0 0 0 0 0 17 0 b f6 60 0 0 13 72 0 0 ........v`...r.. 16: 0 0 0 0 ................ ​

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 18.  RE: Odd Index Issue

    IBM Champion
    Posted Wed August 26, 2020 09:30 AM
    As to the history, the table has been around for years. We upgraded from FC12X1 to FC13 to FC14 to FC14X3 earlier this year in increments. The FC14 upgrade happened on May 24. It's possible that the problem has existed for a long time and went unnoticed. I only found it when tracking down a cdr check issue.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 19.  RE: Odd Index Issue

    Posted Wed August 26, 2020 09:59 AM

    Ok thanks Tom for the info.

     Right now my shift was over and i'll look into your oncheck output in the morning and will let you know.and i hope you have shared partition page of indexes from both the tables (table which contain duplicates and table which doesnt have duplicates)

    and one more thing. As you siad you were there on 12.10 FC12.   So does that mean you have started using informix directly from FC12 or that table was created when you were on FC12 



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 20.  RE: Odd Index Issue

    IBM Champion
    Posted Wed August 26, 2020 09:46 AM

    To make it easier for support to see the problem, I've restored the full instance to a test server, scaled back everything except the table in question and then done a Level-0 archive of that scaled back instance. The query I've already posted clearly shows the duplicates that shouldn't be there.

    I also uploaded the onconfig and an onstat -d to the case to make restoring the instance on your test servers easier.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 21.  RE: Odd Index Issue

    Posted Thu August 27, 2020 07:32 AM
    Hi Tom,

    Just an FYI..i am able to restore your L0 backup..
    So far, I am able to insert duplicate data, but couldn't find any root cause... Partition page of that index looks good to me..

    So ..still investigating.....Will update you in a day or two




    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 22.  RE: Odd Index Issue

    Posted Thu August 27, 2020 10:31 AM
    Edited by System Fri January 20, 2023 04:17 PM
    Hi Tom,

    just on a side note, i would like to know did anyone from IBM/HCL did tbzero on your system to bypass the recovery in the past??? Or anyother activity did by Advance Support 


    Regards
    Gaurav

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 23.  RE: Odd Index Issue

    IBM Champion
    Posted Thu August 27, 2020 11:42 AM
    Advanced support has never connected to this system.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 24.  RE: Odd Index Issue

    Posted Fri August 28, 2020 07:08 AM
    Edited by System Fri January 20, 2023 04:40 PM
    Hi Tom,

    I think I have find the problem.
    Seems like an issue with the flag on your index partition page of slot 4. I patched that in my system (with the value when I re-create a unique index on 6 columns after deleting duplicate values) and then it doesn't allow to insert more duplicate values.

    And If I revert my patch, again I am able to insert duplicates.

    Now I can not tell you how that flag got changed in your system. 

    So is that enough for you..or you need more details to it, knowing you have fixed the duplicates I hope by creating new table or new index?




    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 25.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 08:10 AM
    My next question is, why isn't oncheck finding this? There has to be a bug there somewhere.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 26.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:09 AM

    Because it wasn't a bad index. So once informix think it was bad then oncheck should have thrown error .

    And that's what i told you earlier that i can not tell you how it got changed.  That's probably be a question for you to tell me what happned to your engine and this table in the past. 


    i am pretty sure something must have happened to this table in the past that's why unique index takes duplicate values.  

    Anyways now the best solution for you is to delete duplicated records and re-create this unique index. 



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 27.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 09:24 AM
    I'm not sure I follow you, unless we're using different definitions of "bad." To me, a unique index that does not enforce uniqueness is bad by definition. If the problem is with the index, oncheck -cI should find that. If it is elsewhere, then where else is it?

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 28.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 09:38 AM
    I agree with Tom. Oncheck should check for inconsistent flags like this one that if this flag is set ON in a UNIQUE index that is a problem! Also someone should be searching the code for any place where that flag might be set, figure out why it might have been set incorrectly on this one index on Tom's server, and fix that code! 

    Alternatively someone should figure out why the flag being set permits dups to be inserted into a table under the control of a UNIQUE index and fix that if it is not a legitimate reason. The only thing I can think of from the outside is that this flag was used to debug something and was never removed from the code, but that's no more than a guess.

    I will grant that there may be a legitimate reason for this flag and its effects, but it is not clear what that might be.

    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.








  • 29.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 09:45 AM
    This iteration of the table was created on January 31, 2017 and there have been no advanced support operations against it, nor any kind of system catalog shenanigans (against this or any other table). I'd love to know how this got corrupted (or whatever is wrong with it). Now I feel like I need to validate every unique index in the system.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 30.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 09:49 AM

    Gaurav:

    I'd also ask that you compare notes with the support engineer (dsybes06) who's working TS004050227.

    Thanks,

    - TJG



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 31.  RE: Odd Index Issue

    Posted Fri August 28, 2020 10:07 AM

    Hi Tom,

    seems like you thought that i have concluded. But thats not the case yet!!! Or may be i can not put that in clear word. I am sorry for the confusion. 

     I worked on your case throughout the day and in the end i got  to know it was flag which was wrong.  Having said that it doesn't mean i have conclude. That's my findings from today's test and thought to tell you.  


    Allow me some more time.  I will update you next week now.



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 32.  RE: Odd Index Issue

    Posted Tue September 01, 2020 01:22 AM
    Hi Tom,

    Again I am coming back to the history of that table.

    1) You have told me version history from 12.10 FC12X1. So I would also be interested in knowing prior versions before XC12?
    2) You also told me, table was created in Jan 2017, So what was your IDS version at that time?
    3) When did you define cdr on this table? What was the IDS version at that time?
    4) Can you please upload online.log from Jan 2017 onwards to see its history....
    5) L0 Backup you have shared is from the Source database or target database?



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 33.  RE: Odd Index Issue

    IBM Champion
    Posted Tue September 01, 2020 04:14 PM

    Best guess based on ticket history is that we were on 12.10.FC8W1 in January of 2017. I seriously doubt I have online.log going that far back.

    cdr replication dates back to 11.10 (prior to my arrival at this company), but the replication has been completely dismantled and rebuild several times in the interim. Most recently just a few months ago.

    The L0 I shared is from the send-only database. However, to create something you could easily work with, I restored the full thing to a test server, then dropped all of the other databases and tables leaving just the relevant table. Then I took an L0 backup of that and sent that. If I sent you the whole thing, it would have been about a TB.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 34.  RE: Odd Index Issue

    IBM Champion
    Posted Tue September 01, 2020 04:27 PM
    I'm not sure how relevant this is, but these databases all initially existed on AIX. Around 2015, we did a migration to Linux, and that was accomplished by building empty databases on the Linux side and then establishing ER (cdr) so that we could sync up all the data and run in parallel for a while. If my memory is correct, we were running 11.70 on AIX and 12.10 on Linux. I don't believe we ever ran a version older than 12.10.FC6 on our current platform. So for the tables we're working with today, that would be "ground zero."

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 35.  RE: Odd Index Issue

    Posted Wed September 02, 2020 06:50 AM
    Hi Tom,

    Thanks for the history. Looking at the history if seems that the table is quite old and has seen many changes :-).

    Having said this i looked into our bug database and found the below APAR's that existed in 11.7 and older versions of 12.10.
    See the details below.

    APAR-IC95692-Duplicate rows in table with unique index when rows result from insert triggers on ER-replicated tables.
    Found in IDS-11.70.xC7
    Fixed in IDS-11.70.xC8,IDS-12.10.xC3

    APAR-IT08258-Duplicate rows can be inserted into a table with unique index defined.
    found in IDS-11.70.xC8
    fixed in IDS-11.70.xC9,IDS-12.10.xC6

    There is one more defect for which i don't have APAR and unfortunately i can not share defect number here.
    ER apply ignoring uniqueness imposed only by a unique index
    Found in IDS-11.70.xC5
    Fixed in IDS-11.70.xC8

    I wonder if these could be the cause of the duplicates landing in your table. Over the years you upgraded the engine version but that did not affect the data inside the table. This could be a probable explanation of the table containing duplicate data.

    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 36.  RE: Odd Index Issue

    IBM Champion
    Posted Wed September 02, 2020 09:47 AM

    Gaurav:

    The structure of the table itself has rarely been touched, however.

    Also, do any of those APARs involve a scenario where it continues to be possible to add duplicate records despite the presence of the unique index? Because that's what we're seeing here.

    It seems to me we may be dealing with a new bug here. At a minimum, oncheck should be able to detect the bad flag in the index header.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 37.  RE: Odd Index Issue

    Posted Thu September 03, 2020 05:13 AM
    Hi Tom,

    The situation of 'continuity' seems to be valid for your system only. The other day while trying few things i accidentally patched the flags  in slot 4 of your partition page on your system (restored from L0) and found that i can control the inserting of duplicates by swapping them.

    The reason i say this happens in your system only is that when i create similar table in fresh instance with the same flags as your's it does not allow me to insert duplicates.
    For us to log in a defect we should have a reproducible test case. Problem reproduced with help of patching does not qualify as a test case. For this we need to know what actions or combination/seqence of actions (if i may say) happened on the table which resulted in the current state. We will try and see if we can reproduce the steps but in the meantime the solution seems to be to delete the duplicate records and then recreate the unique index.

    Having said that, it doesn't mean we are done. We am still investigating and see why oncheck wouldn't report such things if we have duplicate records in any scenario.

    My Next update on this will be next week!!!


    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 38.  RE: Odd Index Issue

    Posted Tue September 15, 2020 04:37 AM
    Hi Tom,

    As an update we are in the last phase of our testing and heading towards the defect against oncheck... I wil update you in a day or two...



    ------------------------------
    Gaurav Kumar
    ------------------------------



  • 39.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 09:54 AM

    How can a unique index that allows duplicates being anything but 'bad' ?

     

    Cheers
    Paul

     






  • 40.  RE: Odd Index Issue

    Posted Fri August 28, 2020 09:57 AM

    That was my suspicion all along. 

     

    The question now becomes how did that flag get changed in the first place ?

     

    Advanced support  tools are built into the engine (they use these tool for fixing things like this) . You can check under $INFORMIXDIR for  a sub-directory  called astools (or something similar) . If there was any patching ever done there would  be a history file with those changes in it.

     






  • 41.  RE: Odd Index Issue

    IBM Champion
    Posted Fri August 28, 2020 10:15 AM
    I've checked the entire system and no "astools" directory. I have every INFORMIXDIR dating back to 12.10.FC12, which gets us as far back as March of 2019. The table dates to January of 2017, so it's at least possible that something was done on a prior version of IDS whose INFORMIXDIR was later removed, but I have no recollection of any such event, and there's nothing I can find in my case history to suggest it.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 42.  RE: Odd Index Issue

    Posted Fri August 28, 2020 10:44 AM

    I hope the support engineer gets a handle on this for your sake as well as the rest of us.

     

    keep us in the loop.

     






  • 43.  RE: Odd Index Issue

    IBM Champion
    Posted Mon August 17, 2020 11:48 AM

    Technical support's answer thus far has been: Unload the table, drop it, recreate it and reload it.

    The first problem with this is that it's a frequently-accessed production table, and doing so would require a full production outage.
    The second problem is that doing so doesn't tell us what went wrong in the first place or whether there's a more serious bug we need to be aware of.
    The third problem is that this table is part of a four-server, 54-table ER template, so rebuilding the table necessarily means dismantling, rebuilding and resyncing all of THAT.

    So you can understand why I'm hesitant to go through all that.

    If I can get a small amount of outage time, I might try simply dropping and rebuilding the offending index, which would at least alleviate most of the work above.



    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 44.  RE: Odd Index Issue

    Posted Mon August 17, 2020 12:42 PM

     

    I have a suspicion that the tables index partition page  (internal 'meta data'  page ) which describes the tables indexes is corrupted in some manner where oncheck is not detecting the corruption.  TS does have the tools

    necessary to view this information  and it would be good to know if that is the root cause. You can always call back and ask  to escalate.

     

     

     

     






  • 45.  RE: Odd Index Issue

    IBM Champion
    Posted Mon August 17, 2020 01:41 PM
    That's good to know, Mark, thanks. My Rube Goldberg-y path forward may end up being to break an RSS off from the cluster, drop everything except the problem table, downsize everything else, and then take a Level 0 to send to support.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 46.  RE: Odd Index Issue

    Posted Mon August 17, 2020 02:58 PM

    good luck ...

     






  • 47.  RE: Odd Index Issue

    IBM Champion
    Posted Fri September 04, 2020 09:06 AM
    As I've noted in the tech support case, I'm less concerned with how this situation came to be in the first place and more concerned with why oncheck doesn't detect this problem. What I'm pushing for is a defect to be filed against oncheck, and oncheck to be fixed so that it detects a problem like this: a table where records exist that violate an active unique index.

    I also admit to being a bit mystified at the pushback I've been getting. It seems to me that now that I've sent in a Level-0 of a small instance that illustrates the problem, it wouldn't be all that difficult to fire up a debugging version of the engine, insert an invalid record and step through the process to see where it's breaking down.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 48.  RE: Odd Index Issue

    Posted Mon September 07, 2020 11:28 AM
    Hi Tom.

    Very many years ago, under the first release of 4GL and Standard Engine, I had created a table with a unique index and loaded it from some source (the SQL load command I think) the had some duplicate values in the index fields.  It loaded the data with the duplicate rows and I only found out when we did a a singleton SELECT which returned a "has returned no only one row" error.  I somehow doubt that could still be happening.

    Recalling my days on the help desk, I would first ask you to run an "dbschema -ss" command on the table to see if that 6-column index really was created UNIQUE, though I presume you have done that by now.  When I reboot my other PC to Linux I will look for a one-use script I wrote more recently (like 4 years ago) to delete duplicate rows that resulted from a restarted load before the unique index was created.  If I find it I will share it and you can modify it to locate duplicates and decide on your own what to about it.

    As to why onchecks are failing to notice:  Sounds like a bug but the authors plainly assume it could not happen so no checking for it.  I'd call it a design flaw.

    Good luck.

    -- Jacob S.





  • 49.  RE: Odd Index Issue

    IBM Champion
    Posted Tue September 15, 2020 05:56 AM

    Hi,

    Previously I was told that oncheck is designed to check for physical issues i.e. page linkage,overlapping rows on a page,incorrect free space count on  a page,index linkage to data rows rather than logical issues.

    For example oncheck does not detect invalid bit pattern values in decimal fields.

    What do people think, should oncheck has an additional mode which it checks values on the rows?

    Sounds like Feature Request to me!

    Regards,
    David.

    ------------------------------
    David Williams
    ------------------------------



  • 50.  RE: Odd Index Issue

    IBM Champion
    Posted Tue June 22, 2021 04:50 PM

    By golly, they've found it!

    IT37368 UNIQUE INDEX POTENTIALLY ALLOWING DUPLICATES INTO TABLE

    For an explicitly named unique index serving a unique constraint, it is possible to survive dropping of the constraint with a specific 'constraint' characteristic still on. In this state, the unique index would no longer block duplicates from going into the table.

    This would happen if the "drop constraint" gets applied to the table through any form of logical log recovery.

    T37370 INDEX CHECK ON UNIQUE INDEX NOT FINDING/REPORTING CERTAIN DUPLICATES

    oncheck -ci|-cI index checking would not detect duplicates in a table if they share the same key entry (two entries with same values would be found and reported, though.)



    ------------------------------
    TOM GIRSCH
    ------------------------------