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

Document ID : KB000113051
Last Modified Date : 04/09/2018
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)

Example of full 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.

SaaS Customers: Please open a case with CA Support to perform steps a, b and c under Solution 2. Once those steps are complete, the following 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)