IT40848: UNIQUE INDEX BUILD CAN FAIL WITH -316 ERROR WHEN CREATE TABLE WITH ERKEY OR ALTER TABLE ADD ERKEY

APAR status

• Closed as program error.

Error description

• If the server is bounced after creating tables with erkey or
  altering tables to add erkey, then
  it is possible that subsequent create table with erkey or alter
  table add erkey operations will
  fail to add the associated unique index.
  
  The first significant problem here is that the create table or
  alter table statements will appear
  successful, but the if you look in the online.log, you will see
  an error.  For instance:
  
  In dbaccess:
  
      > create table tab10(c1 int, c2 int) with erkey in rootdbs;
  
      Table created.
  
      >
  
  But in the online.log:
  
      05/05/22 10:14:59  Error on create unique index ERKEY_1 on
  'informix'.tab10 (ifx_erkey_1, ifx_erkey_2, ifx_erkey_3 ) using
  btree
      05/05/22 10:14:59  SQLCODE:-316 ISAM:0
  
  The next significant problem here was the original ER symptoms
  that were reported at customer site.
  
  The customer was observing a large trg_send queue on their
  source and a large trg_receive queue on
  their target server.  They discovered the missing index for a
  particular replicate's target table and removed
  the replicate.  The trg_send queue on the source and trg_receive
  queue on the target immediately started
  shrinking and ER was able to process the queues and maintain any
  loads associated with the all other
  replicates.
  
  Rebuilding the target table for the problematic replicate with
  the erkey index now successfully created has
  resumed normal operation.
  
  There is problem with the server when it is bounced that the
  underlying key that is used to uniquely name
  the erkey index is reset and we generate non-unique value
  resulting in duplicate erkey index names.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users of Informix Server prior to 12.10.xC16 and 14.10.xC10. *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * See Error Description                                        *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to Informix Server 12.10.xC16 or 14.10.xC10.         *
  ****************************************************************
  

Problem conclusion

• Fixed in Informix Server 12.10.xC16 and 14.10.xC10.
  

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