The Load Data Warehouse job in the incremental format fails while loading data into a lookup table. A sample error message displayed in the bg-dwh.log is as below.
MERGE INTO DWH_LKP_EXAMPLE TGT
USING (
SELECT LAST_UPDATED_DATE AS CLARITY_UPDATED_DATE
,PRNAME AS RESOURCE_ROLE
,PRNAME AS RESOURCE_ROLE_KEY
,to_date('3022/05/16 48:27:42', 'yyyy/mm/dd HH24:MI:SS') AS dw_updated_date
FROM [email protected]
WHERE 1 = 1
AND LAST_UPDATED_DATE >= to_date('3022/05/16 48:27:42', 'yyyy/MM/dd HH24:mi:ss')
) SRC
ON (SRC.RESOURCE_ROLE_KEY = TGT.RESOURCE_ROLE_KEY)
WHEN MATCHED
THEN
UPDATE
SET TGT.CLARITY_UPDATED_DATE = SRC.CLARITY_UPDATED_DATE
,TGT.RESOURCE_ROLE = SRC.RESOURCE_ROLE
,TGT.dw_updated_date = SRC.dw_updated_date
WHEN NOT MATCHED
THEN
INSERT (
CLARITY_UPDATED_DATE
,RESOURCE_ROLE
,RESOURCE_KEY
,dw_updated_date
)
VALUES (
SRC.CLARITY_UPDATED_DATE
,SRC.RESOURCE_ROLE
,SRC.RESOURCE_ROLE_KEY
,SRC.dw_updated_date
);
...
...
[CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION IN DWH_LOOKUP_LOAD (DWH_LKP_RESOURCE_ROLE). SQLERRM : ORA-30926: unable to get a stable set of rows in the source tables
ORA-06512: at line 22
Notice that both RESOURCE_ROLE and RESOURCE_ROLE_KEY are mapped to PRNAME. It is expected that RESOURCE_ROLE_KEY is mapped to ID instead.
Incorrect configuration that would have lead to wrong mappings of the meta data in PPM_META_COLUMNS table.
Version: 15.9.3