Load DWH job errors "ORA-00942" and "EXECUTE IMMEDIATE 'TRUNCATE TABLE DWH_CMN_ERROR

Products

Clarity PPM On Premise Clarity PPM SaaS

Issue/Introduction

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 Clarity

This issue may be seen 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

Cause 1: This may be noticed post refresh

You sometimes have a mismatch of tables during refresh if the database backup was taken when the Load Data Warehouse job was running on the source environment. We suggest you make sure you don't run the Load Data Warehouse at the times when the backups are taken for a refresh.

Cause 2: A specific change was made that requires the Load DWH Full job to be run

If the full load wasn't run first prior to running the Incremental job for any of the changes listed below, this can cause issues. Make sure to run the full load DWH job prior to incremental when any of the changes below occur (See more details at: Load DWH Full - When to run to prevent failures)

Upgrade to a new release or patch for Clarity
Deleting an attribute or unselecting the Include in the Data Warehouse option for the attribute in Studio. You cannot re-enable the attribute for inclusion in the data warehouse unless at least this job has completed one run.
Changing the timeslice dates to include a larger timeframe.
Changing the First Day of Work Week.
Changing any of the settings in the System Options, Data Warehouse Option (including language or entity for fiscal periods)

Important! This error is generic. To know the exact root cause and exact table that causes the issue, debugging with the database copy is required.

Environment

Release: All supported Releases
Component: Clarity Data Warehouse (DWH)

Resolution

Resolution 1: Run a Load DWH- Full Load (Recommended)

This can address this issue in a majority of instances. If this does not help proceed to the steps below.

Resolution 2: Rebuild the DWH

If the above does not help: Re-build the DWH (reset the database to blank) using the steps found at: When to rebuild the Data Warehouse (DWH) database, then run the below-listed jobs.

Impact of rebuilding DWH: Make sure DWH Trend tables are backed up if you are using DWH trends as the trend data will be lost if the Data Warehouse schema is recreated. Once the DWH is rebuilt, replace
the backed up trend tabled in the newly imported schema

SaaS Customers: Please open a case with Clarity 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.

Create and Update Jaspersoft Users
Load DWH Access Rights
Update Report Tables
Load DWH (full)

Resolution 3: Check for DDL_STATEMENT under DWH_INTERNAL_MD table on DWH schema

If there are entries they might be from a backup/restore operation done when the DWH job is running on the source environment.

Copy all entries from this table
Run the statement: TRUNCATE TABLE DWH_INTERNAL_MD
Using the entries you copied, recreate all indexes on the DWH database (replace old schema name with new schema name accordingly)
Now re-run the Load Data warehouse Full Load job to resolve the issue.

Additional Information

If the above doesn't help, see: