Load Data Warehouse Job Fails in PPM with Error ORA-00942: table or view does not exist

Document ID : KB000113051
Last Modified Date : 05/01/2019
Show Technical Document Details
Issue:
The Load Data Warehouse (DWH) job is failing with an error referencing: ORA-00942: table or view does not exist, EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE' in CA Project & Portfolio Management (PPM) (formerly known as Clarity).

This issue may be see with the Load DWH job when it's run in Full or Incremental mode. 

Example of error message below:

2018/08/24 11:13:53 - ClarityDB - isOracle? - Couldn't execute SQL: BEGIN 
2018/08/24 11:13:53 - ClarityDB - isOracle? - EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE'; 
2018/08/24 11:13:53 - ClarityDB - isOracle? - DWH_CFG_PRE_CONFIG_SP( 
2018/08/24 11:13:53 - ClarityDB - isOracle? - P_DBLINK => 'ZZZZZZDWH_ZZZZZZ', 
2018/08/24 11:13:53 - ClarityDB - isOracle? - P_FULL_RELOAD => 'Y'); 
2018/08/24 11:13:53 - ClarityDB - isOracle? - END; 
2018/08/24 11:13:53 - ClarityDB - isOracle? - 
2018/08/24 11:13:53 - ClarityDB - isOracle? - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist 
2018/08/24 11:13:53 - ClarityDB - isOracle? - ORA-06512: at "ZZZZZZDDWH.DWH_CFG_PRE_CONFIG_SP", line 43 
2018/08/24 11:13:53 - ClarityDB - isOracle? - ORA-06512: at line 3 
Cause:
See KB000004413 for known causes 
Resolution:
Rebuild the DWH using the steps found at: KB000004413, then run the below listed jobs. 
  • SaaS Customers: Please open a case with CA Support to rebuild the DWH.
  • Once the DWH is rebuilt, the below jobs need to be run and completed in the following order to repopulate the DWH database tables.
  1. Create and Update Jaspersoft Users
  2. Load DWH Access Rights
  3. Update Report Tables
  4. Load DWH (full)