Incremental Load DWH Job Fails With Generic DWH_GATHER_TABLE_STATS_SP Error

Products

• Clarity PPM SaaS

Issue/Introduction

The Incremental Load DWH consistently fails with BG DWH log error:

Misc Updates PG - An error occurred executing this job entry : 
Couldn't execute SQL: DO $$
BEGIN
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_CMN_PERIOD');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_FIN_BENEFIT_PLAN');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_FIN_PLAN');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_ASSIGNMENT');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_INVESTMENT');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_TASK');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_INV_TEAM');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_RES_RESOURCE');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_SHEET');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_ENTRY');
    CALL DWH_GATHER_TABLE_STATS_SP( 'DWH_TME_PERIOD');

    UPDATE DWH_CFG_AUDIT SET DW_LOAD_START_DATE = (SELECT LOCALTIMESTAMP FROM DUAL) WHERE TABLE_NAME = 'DWH_INV_TASK_HIERARCHY';
    CALL DWH_INV_TASK_HIERARCHY_SP();
    UPDATE DWH_CFG_AUDIT SET DW_UPDATED_DATE = (SELECT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS),
                             
Error 6/30/22 11:02 AM 
ETL Job Failed. Please see log bg-dwh.log for details.
Job Completed 6/30/22 11:02 AM 
NJS-0401: Execution of job failed.

Environment

Release : 15.9.3+

 

Cause

Corrupted DWH schema

Resolution

Run the full Load DWH job to check if there is an issue with the synchronization. If it completes, run the incremental Load DWH.

If the Incremental Load Data Warehouse still fails, restore the OOTB DWH Schema and re-run the full load