Load DWH Full fails with ORA-02298 - FK constraint when scheduled at a certain time of the day only (Oracle)

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.