IO27061: STORE REQUEST FAILS WITH ORA-00001 UNIQUE CONSTRAINT ON DUPLICATE OF ITEMID WHILE USING ORACLE RAC

APAR status

• Closed as program error.

Error description

• Datacap store request fails with ORA-00001 DUP. Library trace
  shows:
  <ORA-00001: unique constraint (ICMADMIN.SYS_C0024302) violated.
  Constraint is on primary key on ICMSTITEMS001001 Itemid
  apparently during the creation of the itemid for the part
  ICMUT00300001. Excerpt from trace:
  ICMPLSC2 ICMCREATEDOCPART 00879 05/10 15:47:00.456 GMT
  ;10154647599959 c4:16aa19a3c00:X7dd5 DATACAP_REL
  szDB2CurrentTimeStamp1
  
  ICMPLSC2 TX_insertItemInProgress0 02843 05/10 15:47:00.488 GMT
  ;10154647599959 c4:16aa19a3c00:X7dd5 DATACAP_REL SQL ERROR:
  <ORA-00001: unique constraint (ICMADMIN.SYS_C0024302) violated
  

Local fix

• Two options:
  1/ Alter sequence <schema>.CM_SEQ_ID NOCACHE;
  
  This will turn off the cache, as a result, all usage of the
  sequence will be serialized for both nodes. This will ensure a
  unique number generated. However, there may be a performance
  implication because of the serialization. However, this is a
  simple way to fix this problem.
  
  2/ Alter sequence <schema>.CM_SEQ_ID NOCACHE;
  
  Alter SEQUENCE <schema>.CM_SEQ_ID INCREMENT BY 1 MINVALUE 1
  MAXVALUE
  100000 CYCLE CACHE <n>;
  
  Where n is calculated as 100000 / number of nodes.
  
  So 2 nodes: n = 50000;
  
  3 nodes: 3333
  
  4 nodes: 2500
  
  In an instance where you have 2 nodes, we need to make the
  following change:
  
  Alter sequence <schema>.CM_SEQ_ID NOCACHE;
  
  ALTER SEQUENCE <schema>.CM_SEQ_ID INCREMENT BY 1 MINVALUE 1
  MAXVALUE   100000 CYCLE CACHE 50000;
  
  This method sets the cache and max value in such a way that
  each node will generate the values in a different range, hence
  ensuring uniqueness.
  Oracle recommends using the CACHE setting to enhance
  performance if you are using sequences in an Oracle Real
  Application Clusters environment.  As such, the preference is
  option 2.
  

Problem summary

• the problem has been fixed/shipped in a previous out of support
  release.
  

Problem conclusion

• na
  

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

  LIBRARY SERVER

• Fixed component ID

  5724B1907

Applicable component levels