Load DWH Full fails with ORA-02298 - FK constraint when scheduled at a certain time of the day only (Oracle)
book
Article ID: 103730
calendar_today
Updated On:
Products
Clarity PPM On Premise
Issue/Introduction
When running Load Data Warehouse (DHW) job - Full Load (could also be Incremental) at a certain time of the day only (usually scheduled) getting a FK constraint error, such as:
Execute SQL script - org.pentaho.di.core.exception.KettleStepException: Error while running this step! Couldn't execute SQL: ALTER TABLE DWH_INV_APPLICATION ENABLE VALIDATE CONSTRAINT DWH_INV_APPLICATION_FK1 [CA Clarity][Oracle JDBC Driver][Oracle]ORA-02298: cannot validate (PPM_DWH.DWH_INV_APPLICATION_FK1) - parent keys not found
Please note the error could be on other tables, not only DWH_INV_APPLICATION.
When the job is ran at another time it completes with no problem.
Cause
This happens when there is another custom job or process which is accessing the records and updating them whilst the Load Data Warehouse job is running. This updates the LAST_UPDATED_DATE on the objects, and creates mismatches with other records that were not updated.
Environment
Component: PPMDWH
Resolution
We recommend that any custom jobs and processes which may be updating the LAST_UPDATED_DATE field for the object instances should be run at times in which they do not conflict with Load Data Warehouse job.
Set any possible custom jobs updating this field that are added to UI as Incompatible with Load data Warehouse and schedule them with some time in between.
Similarly for processes that update this field, if they use the Execute process job, set it as Incompatible in UI - Administration - Reports and jobs
Adjusting the job schedule should resolve the problem and avoid the constraint issues.