Load Data Warehouse job fails due to DWH_RDM_ITEM_POSITION_FK3

book

Article ID: 142302

calendar_today

Updated On:

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

The Load Data Warehouse job fails with the following error in bg-dwh.log:

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (DWH_RDM_ITEM_POSITION_FK3) - parent keys not found
at org.pentaho.di.core.database.Database.execStatement(Database.java:1432) 
at org.pentaho.di.core.database.Database.execStatements(Database.java:1538)
at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:225)
 ... 2 more
Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (DWH_RDM_ITEM_POSITION_FK3) - parent keys not found

Cause

DWH_RDM_ITEM_POSITION.PICKLIST_KEY has a foreign key constraint on DWH_RDM_PICKLIST.PICKLIST_KEY, that is defined in DWH_RDM_ITEM_POSITION_FK3.

The following query can be used in the DWH DB to determine any such orphan records in DWH_RDM_ITEM_POSITION table.

SELECT
    *
FROM
    dwh_rdm_item_position
WHERE
    picklist_key NOT IN (
        SELECT DISTINCT
            picklist_key
        FROM
            dwh_rdm_picklist
    );

Environment

Versions: 15.6.1, 15.7.0, 15.7.1, 15.8.0, 15.8.1, 15.9.0, 15.9.1

Resolution

1. Run the following query in the PPM DB to find the picklist_key that are causing the issue:

SELECT
    rr.name,
    rr.code,
    ip.*
FROM
    dwh_rdm_item_position_v ip
    JOIN rdm_roadmaps rr ON rr.id = ip.roadmap_key
WHERE
    ip.picklist_key NOT IN (
        SELECT
            picklist_key
        FROM
            dwh_rdm_picklist_v
    );

2. The name and code of the Roadmap that has the issue is shown by the above query in the first two columns. Use that information to navigate to the relevant Roadmap

3. Check if there are any custom picklists defined in that Roadmap. Also, navigate to the Board view and change "Columns" and "Color By" options under "View Options"

4. Run the query in step 1 again, and verify if any rows are returned or not. If no rows are returned, then try running a full load and check if the job fails or not.