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
Release : 15.9
Component : CLARITY SAAS DATA WAREHOUSE
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.
See also: Load DWH job frequently reported issues