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
Issue:
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.
 
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.
 
Additional Information:
Reference KB000035711 - Load Data Warehouse job fails with _FK errors when the load DWH fails or is cancelled