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

Document ID : KB000103730
Last Modified Date : 04/09/2018
Show Technical Document Details
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!
[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.
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.
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.
Additional Information:
Reference KB000035711 - Load Data Warehouse job fails with _FK errors when the load DWH fails or is cancelled