Load Data Warehouse job - Full Load fails with FK constraint when scheduled at a certain time of the day only (Oracle)Load Data Warehouse Failing

Document ID : KB000103730
Last Modified Date : 28/06/2018
Show Technical Document Details
Issue:
When running Load Data Warehouse 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.
 
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.