When running Load Data Warehouse (DWH) job - Full after upgrade of CA Application Portfolio Management (APM) and PMO, it's persistently failing with error:
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
Nothing else is running at the time of the execution.
Release : All Clarity releases with DWH
Component : CLARITY DATA WAREHOUSE
Try the following:
delete from DWH_INV_APPLICATION where INVESTMENT_KEY not in
(select INVESTMENT_KEY from DWH_INV_INVESTMENT)
3. Commit
4. Now run a Load Data Warehouse - Full Load
If the above does not help, this issue can be due to orphan investment.
If the investment is corrupted we recommend marking it for deletion and running Delete Investments job.
Reference: Load DWH job frequently reported issues