Load Data Warehouse - Incremental fails intermittently with ORA-02298: cannot validate (PPM_DWH.DWH_RDM_ITEM_POSITION_FK2) - parent keys not found
Article ID: 132594
Clarity PPM SaaSClarity PPM On Premise
On a large database of customer who heavily uses roadmaps, schedule the Load Data Warehouse - Incremental to run every hour
Expected results: The job to run fine Actual Results: Once in a while the job will fail with error: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_RDM_ITEM_POSITION_FK2) - parent keys not found at org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:251) at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60) at java.base/java.lang.Thread.run(Thread.java:834) Caused by: org.pentaho.di.core.exception.KettleDatabaseException: Couldn't execute SQL: ALTER TABLE DWH_RDM_ITEM_POSITION ENABLE VALIDATE CONSTRAINT DWH_RDM_ITEM_POSITION_FK2
The issue is with the order the tables are loaded. If an item gets updated between DWH_RDM_ROADMAP_ITEM and DWH_RDM_ITEM_POSITION tables loading, it would cause this issue.
Release: All Clarity releases with DWH Component: PPMDWH
Part of this issue was addressed in 15.5.1 as fix for DE43623 It may still occur on 15.5.1 and require substantial design change - logged as DE49361/DE58448, Fixed in 15.9.2