ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Load DWH job fails with error "ORA-02437" on primary key

book

Article ID: 108991

calendar_today

Updated On:

Products

Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

The Load Data Warehouse (DWH) job fails with the following error:
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02437: cannot validate (PPM_DWH.DWH_INV_TEAM_PK) - primary key violated
ORA-06512: at line 7

Cause

Caused by a duplicate team record on a project.

Environment

Release: Any
Component: Data Warehouse

Resolution

The duplicate record cannot be deleted via the UI, and must be done via the database. You must ensure that the record that is being left in place (the one NOT being deleted) must point to the proper team_id.

  1. Run the following query against the Clarity database:

    select odf_pk, count(odf_pk) from dwh_team_v 
    group by odf_pk 
    having count(odf_pk) > 1

    This will show you the ones that have duplicates.
  2. Take the "odf_pk" values from the results and put them into this query inside the parentheses, and run this against the DWH database:

    select * from dwh_team_v where odf_pk in (PUT_LIST_OF_ODF_PK_VALUES_HERE )

    This will show you the specific projects that have duplicate teams.
  3. You can then delete the duplicate records from both the prassignment and odf_ca_assignment tables.
    IMPORTANT:  Ensure the records you are leaving are the ones pointed to the proper value!! As always, we recommend testing this in a test environment before attempting to do this in production.  Additionally, ALWAYS backup the database prior to deleting any records.

Additional Information

Reference also: