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.
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.