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

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

book

Article ID: 206098

calendar_today

Updated On:

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