IT27508: DBSCHEMA AND "SELECT * FROM SYSINDEXES" FAIL WITH -201 ERROR AFTER MIGRATION AND REVERSION 11.50->12.10->11.50

APAR status

• Closed as program error.

Error description

• If you initialize an instance of 11.50.FC9W3 (for example),
  create the stores_demo database, then migrate that in-place to
  12.10.FC10 and then revert back to 11.50.FC9W3, dbschema of the
  entire stores_demo database or any single table will fail with
  -201.
  
  Setting a trap for -201 shows that dbschema is failing while
  trying to run this SQL:
  
    select c.coltype, c.collength  from informix.syscolumns c,
      informix.sysindexes i  where i.tabid = c.tabid and i.tabid =
  100 and
      (c.colno = ABS(i.part1) or c.colno = ABS(i.part2) or c.colno
  =
      ABS(i.part3) or c.colno = ABS(i.part4) or c.colno =
  ABS(i.part5) or
      c.colno = ABS(i.part6) or c.colno = ABS(i.part7) or c.colno
  = ABS(i.part8)
      or c.colno = ABS(i.part9) or c.colno = ABS(i.part10) or
  c.colno =
      ABS(i.part11) or c.colno = ABS(i.part12) or c.colno =
  ABS(i.part13) or
      c.colno = ABS(i.part14) or c.colno = ABS(i.part15) or
  c.colno =
      ABS(i.part16));
  
  A simple "select * from sysindexes" also returns -201.
  
  I noticed that the last record in sysviews for sysindexes is
  this:
  
  ...
  
  tabid     71
  seqno     14
  viewtext  ".sysindices x0 ;
  
  tabid     71
  seqno     15
  viewtext
  
  Looking at those two final rows, I see this:
  
  slot  18:
      0:  0  0  0 47  0  e 22 2e 73 79 73 69 6e 64 69 63
  ...G..".sysindic
     16: 65 73 20 78 30 20 3b 20 20 20 20 20 20 20 20 20   es x0 ;
  
     32: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
  
     48: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
  
     64: 20 20 20 20 20 20
  ..........
  slot  19:
      0:  0  0  0 47  0  f  0  0  0  0  0  0  0  0  0  0
  ...G............
     16:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
  ................
     32:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
  ................
     48:  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0  0
  ................
     64:  0  0  0  0  0  0
  ................
  
  If I remove that last row (seqno=15), the -201 error goes away.
  
  The problem does not reproduce for the sysmaster and sysutils
  databases likely because they are rebuilt on 11.50 after
  reversion.  But the problem does reproduce with the sysuser
  database which is not rebuilt after reversion.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of IDS 12.10.xC10 and older versions.                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * Dbschema and "select * from sysindexes" fail with -201 error *
  * after migration and reversion 11.50->12.10->11.50.           *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  

Problem conclusion

• Fixed in IDS 12.10.xC11.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  INFORMIX SERVER

• Fixed component ID

  5725A3900

Applicable component levels