CA PPM Advanced Reporting scheduled report fails while Load DWH job is running
Article ID: 108928
CLARITY PPM FOR ITGCLARITY PPM FEDERALClarity PPM SaaS - ApplicationClarity PPM On Premise
I set up a report to run on auto-schedule and received a failure notification for it last night. It appears that its running at the same time the Load DWH job is scheduled to run which seems to be causing the problem.
The assumption is correct - this is caused by the DWH load job putting a deadlock on an object which a report is attempting to access.
Release: Component: PPMDWH
In the log we see the following: Caused by: java.sql.SQLException: ORA-04020: deadlock detected while trying to lock object PPM_DWH.DWH_INV_INVESTMENT
This shows us that there is a deadlock on the object in DWH at the same time that the report was running. The best way to prevent this from occurring is to have a known rule across your organization that there is a "blackout period" for scheduling reports such that lets say between 12am and 1am (or however long your DWH job typically takes - leave a little bit of extra room there as it can fluctuate a bit) no reports should be scheduled.