IBM Support

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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 number

    IT33346

  • Reported component name

    STR B2B INTEGRA

  • Reported component ID

    5725D0600

  • Reported release

    526

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-06-26

  • Closed date

    2021-04-08

  • Last modified date

    2021-05-21

  • 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

[{"Line of Business":{"code":"LOB02","label":"AI Applications"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS3JSW","label":"IBM Sterling B2B Integrator"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"5.2.6"}]

Document Information

Modified date:
22 May 2021