IT33346: SQLINTEGRITYCONSTRAINTVIOLATIONEXCEPTION: ORA-00001: UNIQUE CONSTRAINT (STLPRD.ARCHIVE_INFO_PK)

APAR status

• Closed as program error.

Error description

• When the dmivisibility visibility_coverage property defines dmiv
  isibility.visibility_coverage=AdminAudit,Authentication,Authoriz
  ation in customer_overrides.properties, the visibility.log
  reports Oracle exception: "ORA-00001: unique constraint
  (STLPRD.ARCHIVE_INFO_PK)" while attempting to store an
  Authentication or Authorization DMI Event in the ARCHIVE_INFO
  table.
  
  [2020-03-22 13:00:55.683] ERROR
  DmiVisEventWarehouseQueue.Worker.executeStatementsAsExecutor
  had a SQL exception from pstmnt: PreparedStatementHelper: SQL:
  INSERT INTO ARCHIVE_INFO (WF_ID, GROUP_ID, ARCHIVE_FLAG,
  ARCHIVE_DATE) VALUES (?, ?, ?, ?) Values: [Killed545, 3, 1,
  2020-04-21 13:00:54.796]
  [2020-03-22 13:00:55.683] ERROR
  [1584882055683] ORA-00001: unique constraint
  (STLPRD.ARCHIVE_INFO_PK)
  viopfgadm@reuxeuls390[PRODUCTION]$
  
  [2020-03-22 13:00:55.683]
  ERRORDTL [1584882055683]java.sql.SQLIntegrityConstraintViolation
  Exception: ORA-00001: unique constraint
  (STLPRD.ARCHIVE_INFO_PK) violated
  
  at
  oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
  at
  oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
  at
  oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
  at
  oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
  at
  oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
  at
  oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
  at oracle.
  jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.ja
  va:207)
  at oracle.jdbc.driver.T4CPreparedStatement.executeForRow
  s(T4CPreparedStatement.java:1044)
  at oracle.jdbc.driver.OracleSt
  atement.doExecuteWithTimeout(OracleStatement.java:1329)
  at oracl
  e.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePrep
  aredStatement.java:3584)
  at oracle.jdbc.driver.OraclePreparedSta
  tement.execute(OraclePreparedStatement.java:3685)
  at oracle.jdbc
  .driver.OraclePreparedStatementWrapper.execute(OraclePreparedSta
  tementWrapper.java:1376)
  at
  sun.reflect.GeneratedMethodAccessor148.invoke(Unknown
  Source)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(Deleg
  atingMethodAccessorImpl.java:56)
  

Local fix

• STRRTC - B2BISFG-52886
  

Problem summary

• Users Affected:
  
  ALL
  
  
  
  Problem Description:
  
  The "ORA-00001: unique constraint (REDACTED.ARCHIVE_INFO_PK)
  violated" exception is thrown when the visibility_coverage
  property (see visibility.properties) is set to
  "AdminAudit,Authentication,Authorization"
  
  
  
  [2020-03-22 13:00:55.683] ERROR
  DmiVisEventWarehouseQueue.Worker.executeStatementsAsExecutor had
  a SQL exception from pstmnt: PreparedStatementHelper: SQL:
  INSERT INTO ARCHIVE_INFO (WF_ID, GROUP_ID,ARCHIVE_FLAG,
  ARCHIVE_DATE) VALUES (?, ?, ?, ?) Values: [Killed545, 3, 1,
  2020-04-21 13:00:54.796]
  
  [2020-03-22 13:00:55.683] ERROR [1584882055683] ORA-00001:
  unique constraint (STLPRD.ARCHIVE_INFO_PK)
  viopfgadm@reuxeuls390[PRODUCTION]$
  
  [2020-03-22 13:00:55.683] ERRORDTL
  [1584882055683]java.sql.SQLIntegrityConstraintViolationException
  : ORA-00001: unique constraint (STLPRD.ARCHIVE_INFO_PK) violated
  
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445)
  
  at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
  
  at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879)
  
  at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450)
  
  at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192)
  
  at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531)
  
  at
  oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedState
  ment.java:207)
  
  at
  oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPrepar
  edStatement.java:1044)
  
  at
  oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleSt
  atement.java:1329)
  
  at
  oracle.jdbc.driver.OraclePreparedStatement.executeInternal(Oracl
  ePreparedStatement.java:3584)
  
  at
  oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePrepare
  dStatement.java:3685)
  
  at
  oracle.jdbc.driver.OraclePreparedStatementWrapper.execute(Oracle
  PreparedStatementWrapper.java:1376)
  
  at sun.reflect.GeneratedMethodAccessor148.invoke(Unknown Source)
  
  at
  sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethod
  AccessorImpl.java:56)
  
  
  
  Platforms Affected:
  
  ALL
  

Problem conclusion

• Resolution Summary:
  A code fix is provided.
  Corrected the restrictions to ensure only 1 INSERT attempt
  would be made should Connection, Authenticate and/or
  Authorization events get posted. Since some protocol sessions
  post more than one authentication event (e.g. CDSA), duplicate
  insertion is still possible. In these specific cases, the
  exception will be ignored. However, the "Duplicate Key"
  exception will be reported in the visibility log if the logging
  level is set to DEBUG.
  
  
  
  Delivered in:
  
  5020603_16
  5020605_4
  

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

  STR B2B INTEGRA

• Fixed component ID

  5725D0600

Applicable component levels