ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

SQL INDEX corrupt or not!

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL INDEX corrupt or not!

    We have an issue with our indexes in that they are reporting as being ok but they clearly aren't

    We recently moved over to a mirrored Iseries which was mirrored using QuickEdd
    When we started to run on the mirrored machine we noticed that some of the indexes didn't seem to work anymore
    There seems to be no obvious error reported and when I use EDTRBAP I see nothing needing a rebuild

    One such example is the index below


    SQL Index create statement . . . . . . . . :
    CREATE INDEX MYFILEL3 ON DATLIB.MYFILEPF ( RFBK ASC , RFSRC ASC , R
    FSTMP ASC ) WHERE ( RFATHF = + 1 AND RFATHU <> ' ' ) OR RFATHF = + 0
    RCDFMT MYFILE1

    When a program CHAINS to this index the CHAIN fails, now it should work fine (trust me it should get a hit!)

    I wrote another few programs to do some checks and played with a new index and this is what I found...

    1. I wrote a CHAIN program just to reproduce the live issue - it did reproduce the chain got no hit
    2. A Setll, Read loop will work fine - it will use the above index and find all 4 entries
    3. A setll ReadE loop will not work fine if you use the full key but if you drop the final part of the key (which is a timestampe) it is fine
    4. I crreated a new index identical to the other one called MYFILELA and created a duplicate of the program at point 1 this time using the new index - and the CHAIN worked fine
    5. I dropped the original index and recreated it and everything worked fine


    All of the above proves that the index simply isnt working for some reason ? but why!

    BUT I have many indexes and I have no way of knowing which ones are corrupt ! hence I am seeking the help of the gurus to give me some pointers (many of which I will probably have tried already by the way, as I have been working on Iseries for a long time)


    If I do a line by line comparison of the MYFILEL3 and MYFILELA indexes using DSPFD they appear identical !

    Thanks

  • #2
    I'd suggest to open a PMR at IBM.
    We only can guess. The only thing I can say is, I never had any problems with using SQL Indexes in composition with native I/O (and I haven't created any logical file since at least 15 years - and even I prefer embedded SQL there are multiple situations where I prefer native I/O).
    I also had never heard of any of our customers having comparable problems than yours.

    Birgitta

    Comment


    • #3
      Because you did step 5 (deleted/recreated), a PMR with IBM may be tricky. You should report this, though, and be prepared to test until another index demonstrates the problem. If you're lucky, another customer might have reported a similar issue and a fix might be known.
      Tom

      There are only two hard things in Computer Science: cache invalidation, naming things and off-by-one errors.

      Why is it that all of the instruments seeking intelligent life in the universe are pointed away from Earth?

      Comment

      Working...
      X