Load DWH and Access Rights jobs fail post refresh from Prod on PG GCP

Products

• Clarity PPM SaaS

Issue/Introduction

After refresh on Test and Dev, the Load Data Warehouse (DWH) - Full and Load Data Warehouse (DWH) Access Rights are failing continuously in lower environments only. (post refresh from Production)

Errors:

ERROR 2021-01-05 07:27:48,773 [dwh_etl_master_pg UUID: e96aaa01-2c73-423d-a095-0568b6eb05e6] dwh.event postgres? - An error occurred executing this job entry :
Couldn't execute SQL: CALL DWH_CFG_POP_HISTORY_SP();TRUNCATE TABLE DWH_CMN_ERROR_MESSAGE;CALL DWH_CFG_PRE_CONFIG_SP('N');CALL DWH_CFG_FOREIGN_TABLES_SP();
ERROR: schema "XXXXXX" does not exist
Where: importing foreign table "crv_sql_curves"SQL statement "IMPORT FOREIGN SCHEMA XXXXXXX EXCEPT (dual, cmn_db_history, dwh_cfg_object_type, prcounter, user_index_columns) FROM SERVER PPMDBLINK INTO XXXXXXXX"PL/pgSQL function dwh_cfg_foreign_tables_sp() line 29 at EXECUTE

OR

ERROR: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_SEC_RIGHT. relation "cmn_sec_right" does not exist
Where: PL/pgSQL function dwh_sec_right_load() line 39 at RAISE

Environment

Release : 15.9

Component : CLARITY SAAS DATA WAREHOUSE

Resolution

GCP customers: Please contact Broadcom support for assistance with the below.

Re-create the DB link using below command from clarity App server:

admin db create-db-link
admin db create-db-link –Dtype=app –Dsysusername=XXXX –Dsyspassword=XX

If the above is not run, the necessary types required to import the foreign schema are not created and causes the error.

Additional Information

See also: Load DWH job frequently reported issues