CA PPM Advanced Reporting scheduled report fails while Load DWH job is running

Document ID : KB000108928
Last Modified Date : 01/08/2018
Show Technical Document Details
Issue:
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.
Cause:
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.
Resolution:
In the log we see the following:
Caused by: java.sql.SQLException: ORA-04020: deadlock detected while trying to lock object SJC1224PDWH.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.