DWH Full Load and Incremental Fail with Ora - 30926 at META_LOAD_COLS_ORCL
search cancel

DWH Full Load and Incremental Fail with Ora - 30926 at META_LOAD_COLS_ORCL

book

Article ID: 233440

calendar_today

Updated On:

Products

Clarity PPM On Premise

Issue/Introduction

Error messaged in DWH log, after DWH job has failed: 

 

 

META_LOAD_COLS_ORCL - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : An error occurred executing this job entry : 
META_LOAD_COLS_ORCL - Couldn't execute SQL: MERGE INTO DWH_META_COLUMNS A USING
META_LOAD_COLS_ORCL - (
META_LOAD_COLS_ORCL - SELECT
META_LOAD_COLS_ORCL -   UPPER(SRC_TABLE_NAME) SRC_TABLE_NAME
META_LOAD_COLS_ORCL - , UPPER(SRC_COLUMN_NAME) SRC_COLUMN_NAME
META_LOAD_COLS_ORCL - , UPPER(DWH_TABLE_NAME) DWH_TABLE_NAME
META_LOAD_COLS_ORCL - , UPPER(DWH_COLUMN_NAME) DWH_COLUMN_NAME
META_LOAD_COLS_ORCL - , UPPER(ATTR_TYPE) ATTR_TYPE
META_LOAD_COLS_ORCL - , CASE 
META_LOAD_COLS_ORCL -  WHEN (UPPER(ATTR_DATA_TYPE)='DATE' OR UPPER(ATTR_DATA_TYPE)='INTEGER' OR UPPER(ATTR_DATA_TYPE)='TIMESTAMP') THEN UPPER(ATTR_DATA_TYPE)
META_LOAD_COLS_ORCL -  WHEN (UPPER(ATTR_TYPE)='CLOB' AND UPPER(ATTR_DATA_TYPE) NOT LIKE '%VARCHAR%') THEN UPPER(ATTR_DATA_TYPE)
META_LOAD_COLS_ORCL -    ELSE UPPER(ATTR_DATA_TYPE)||'('||NVL(ATTR_DATA_SIZE,'')||')'
META_LOAD_COLS_ORCL -   END ATTR_DATA_TYPE_SRC
META_LOAD_COLS_ORCL - , IS_LOOKUP_ATTR
META_LOAD_COLS_ORCL - , IS_MULTIVALUED
META_LOAD_COLS_ORCL - , LOOKUP_TYPE
META_LOAD_COLS_ORCL - , UPPER(LKP_DIM_TABLE_NAME) LKP_DIM_TABLE_NAME
META_LOAD_COLS_ORCL - , UPPER(DWH_LOOKUP_KEY) DWH_LOOKUP_KEY
META_LOAD_COLS_ORCL - , IS_CUSTOM
META_LOAD_COLS_ORCL - , IS_KEY
META_LOAD_COLS_ORCL - , IS_DELETED DROP_FLAG
META_LOAD_COLS_ORCL - , 0 COL_MOD
META_LOAD_COLS_ORCL - ,UPPER(DIM_KEY) DIM_KEY
META_LOAD_COLS_ORCL - ,UPPER(DIM_DISPLAY) DIM_DISPLAY
META_LOAD_COLS_ORCL - ,UPPER(OBJECT_CODE) OBJECT_CODE
META_LOAD_COLS_ORCL - ,UPPER(ATTRIBUTE_CODE) ATTRIBUTE_CODE
META_LOAD_COLS_ORCL - ,UPPER(EXTENDED_TYPE) EXTENDED_TYPE
META_LOAD_COLS_ORCL - FROM 
META_LOAD_COLS_ORCL -  DWH_META_COLUMNS@PPMDBLINK
META_LOAD_COLS_ORCL - ) B
META_LOAD_COLS_ORCL - ON
META_LOAD_COLS_ORCL -  ( A.DWH_TABLE = B.DWH_TABLE_NAME AND A.DWH_COLUMN = B.DWH_COLUMN_NAME AND A.SRC_TABLE = B.SRC_TABLE_NAME AND A.SRC_COLUMN = B.SRC_COLUMN_NAME )
META_LOAD_COLS_ORCL - WHEN MATCHED THEN UPDATE SET 
META_LOAD_COLS_ORCL -  A.ATTRIBUTE_TYPE = B.ATTR_TYPE,A.ATTRIBUTE_DATA_TYPE = B.ATTR_DATA_TYPE_SRC,A.LOOKUP_TABLE = B.LKP_DIM_TABLE_NAME,A.DWH_RELATED_LOOKUP_KEY = B.DWH_LOOKUP_KEY,
META_LOAD_COLS_ORCL -  A.DROP_FLAG = B.DROP_FLAG,A.IS_MULTIVALUED = B.IS_MULTIVALUED,A.DIM_KEY = B.DIM_KEY,A.DIM_DISPLAY = B.DIM_DISPLAY,A.IS_KEY = B.IS_KEY,A.OBJECT_CODE = B.OBJECT_CODE,
META_LOAD_COLS_ORCL -   A.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE, A.EXTENDED_TYPE = B.EXTENDED_TYPE
META_LOAD_COLS_ORCL - WHEN NOT MATCHED THEN INSERT 
META_LOAD_COLS_ORCL -  (A.DWH_TABLE,A.DWH_COLUMN,A.SRC_TABLE,A.SRC_COLUMN,A.ATTRIBUTE_TYPE,A.ATTRIBUTE_DATA_TYPE,A.DWH_RELATED_LOOKUP_KEY,A.LOOKUP_TABLE,A.DROP_FLAG,A.COL_MODIFIED,A.IS_MULTIVALUED
META_LOAD_COLS_ORCL -  ,A.DIM_KEY,A.DIM_DISPLAY,A.IS_KEY,A.OBJECT_CODE,A.ATTRIBUTE_CODE,A.EXTENDED_TYPE) 
META_LOAD_COLS_ORCL - VALUES 
META_LOAD_COLS_ORCL -  (B.DWH_TABLE_NAME,B.DWH_COLUMN_NAME,B.SRC_TABLE_NAME,B.SRC_COLUMN_NAME,B.ATTR_TYPE,B.ATTR_DATA_TYPE_SRC,B.DWH_LOOKUP_KEY,B.LKP_DIM_TABLE_NAME,B.DROP_FLAG,B.COL_MOD,B.IS_MULTIVALUED
META_LOAD_COLS_ORCL -  B.DIM_KEY,B.DIM_DISPLAY,B.IS_KEY,B.OBJECT_CODE,B.ATTRIBUTE_CODE,B.EXTENDED_TYPE)
META_LOAD_COLS_ORCL - 
META_LOAD_COLS_ORCL - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in the source tables
META_LOAD_COLS_ORCL - ERROR (version 9.1.0.0-324, build 9.1.0.0-324 from 2020-09-07 05.09.05 by buildguy) : An error occurred executing this job entry : 
META_LOAD_COLS_ORCL - Couldn't execute SQL: MERGE INTO DWH_META_COLUMNS A USING
META_LOAD_COLS_ORCL - (
META_LOAD_COLS_ORCL - SELECT
META_LOAD_COLS_ORCL -   UPPER(SRC_TABLE_NAME) SRC_TABLE_NAME
META_LOAD_COLS_ORCL - , UPPER(SRC_COLUMN_NAME) SRC_COLUMN_NAME
META_LOAD_COLS_ORCL - , UPPER(DWH_TABLE_NAME) DWH_TABLE_NAME
META_LOAD_COLS_ORCL - , UPPER(DWH_COLUMN_NAME) DWH_COLUMN_NAME
META_LOAD_COLS_ORCL - , UPPER(ATTR_TYPE) ATTR_TYPE
META_LOAD_COLS_ORCL - , CASE 
META_LOAD_COLS_ORCL -  WHEN (UPPER(ATTR_DATA_TYPE)='DATE' OR UPPER(ATTR_DATA_TYPE)='INTEGER' OR UPPER(ATTR_DATA_TYPE)='TIMESTAMP') THEN UPPER(ATTR_DATA_TYPE)
META_LOAD_COLS_ORCL -  WHEN (UPPER(ATTR_TYPE)='CLOB' AND UPPER(ATTR_DATA_TYPE) NOT LIKE '%VARCHAR%') THEN UPPER(ATTR_DATA_TYPE)
META_LOAD_COLS_ORCL -    ELSE UPPER(ATTR_DATA_TYPE)||'('||NVL(ATTR_DATA_SIZE,'')||')'
META_LOAD_COLS_ORCL -   END ATTR_DATA_TYPE_SRC
META_LOAD_COLS_ORCL - , IS_LOOKUP_ATTR
META_LOAD_COLS_ORCL - , IS_MULTIVALUED
META_LOAD_COLS_ORCL - , LOOKUP_TYPE
META_LOAD_COLS_ORCL - , UPPER(LKP_DIM_TABLE_NAME) LKP_DIM_TABLE_NAME
META_LOAD_COLS_ORCL - , UPPER(DWH_LOOKUP_KEY) DWH_LOOKUP_KEY
META_LOAD_COLS_ORCL - , IS_CUSTOM
META_LOAD_COLS_ORCL - , IS_KEY
META_LOAD_COLS_ORCL - , IS_DELETED DROP_FLAG
META_LOAD_COLS_ORCL - , 0 COL_MOD
META_LOAD_COLS_ORCL - ,UPPER(DIM_KEY) DIM_KEY
META_LOAD_COLS_ORCL - ,UPPER(DIM_DISPLAY) DIM_DISPLAY
META_LOAD_COLS_ORCL - ,UPPER(OBJECT_CODE) OBJECT_CODE
META_LOAD_COLS_ORCL - ,UPPER(ATTRIBUTE_CODE) ATTRIBUTE_CODE
META_LOAD_COLS_ORCL - ,UPPER(EXTENDED_TYPE) EXTENDED_TYPE
META_LOAD_COLS_ORCL - FROM 
META_LOAD_COLS_ORCL -  DWH_META_COLUMNS@PPMDBLINK
META_LOAD_COLS_ORCL - ) B
META_LOAD_COLS_ORCL - ON
META_LOAD_COLS_ORCL -  ( A.DWH_TABLE = B.DWH_TABLE_NAME AND A.DWH_COLUMN = B.DWH_COLUMN_NAME AND A.SRC_TABLE = B.SRC_TABLE_NAME AND A.SRC_COLUMN = B.SRC_COLUMN_NAME )
META_LOAD_COLS_ORCL - WHEN MATCHED THEN UPDATE SET 
META_LOAD_COLS_ORCL -  A.ATTRIBUTE_TYPE = B.ATTR_TYPE,A.ATTRIBUTE_DATA_TYPE = B.ATTR_DATA_TYPE_SRC,A.LOOKUP_TABLE = B.LKP_DIM_TABLE_NAME,A.DWH_RELATED_LOOKUP_KEY = B.DWH_LOOKUP_KEY,
META_LOAD_COLS_ORCL -  A.DROP_FLAG = B.DROP_FLAG,A.IS_MULTIVALUED = B.IS_MULTIVALUED,A.DIM_KEY = B.DIM_KEY,A.DIM_DISPLAY = B.DIM_DISPLAY,A.IS_KEY = B.IS_KEY,A.OBJECT_CODE = B.OBJECT_CODE,
META_LOAD_COLS_ORCL -   A.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE, A.EXTENDED_TYPE = B.EXTENDED_TYPE
META_LOAD_COLS_ORCL - WHEN NOT MATCHED THEN INSERT 
META_LOAD_COLS_ORCL -  (A.DWH_TABLE,A.DWH_COLUMN,A.SRC_TABLE,A.SRC_COLUMN,A.ATTRIBUTE_TYPE,A.ATTRIBUTE_DATA_TYPE,A.DWH_RELATED_LOOKUP_KEY,A.LOOKUP_TABLE,A.DROP_FLAG,A.COL_MODIFIED,A.IS_MULTIVALUED
META_LOAD_COLS_ORCL -  ,A.DIM_KEY,A.DIM_DISPLAY,A.IS_KEY,A.OBJECT_CODE,A.ATTRIBUTE_CODE,A.EXTENDED_TYPE) 
META_LOAD_COLS_ORCL - VALUES 
META_LOAD_COLS_ORCL -  (B.DWH_TABLE_NAME,B.DWH_COLUMN_NAME,B.SRC_TABLE_NAME,B.SRC_COLUMN_NAME,B.ATTR_TYPE,B.ATTR_DATA_TYPE_SRC,B.DWH_LOOKUP_KEY,B.LKP_DIM_TABLE_NAME,B.DROP_FLAG,B.COL_MOD,B.IS_MULTIVALUED
META_LOAD_COLS_ORCL -  B.DIM_KEY,B.DIM_DISPLAY,B.IS_KEY,B.OBJECT_CODE,B.ATTRIBUTE_CODE,B.EXTENDED_TYPE)
META_LOAD_COLS_ORCL - 
META_LOAD_COLS_ORCL - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in the source tables

Environment

Release : 16.0

Component : PPMDWH

Cause

This is caused by duplicates on DWH_META_COLUMNS of Clarity Schema.

Resolution

  1. Find the duplicated column in Clarity schema using below query.
    select src_table_name,SRC_COLUMN_NAME,DWH_TABLE_NAME,DWH_COLUMN_NAME,count(*) from DWH_META_COLUMNS
    group by src_table_name,SRC_COLUMN_NAME,DWH_TABLE_NAME,DWH_COLUMN_NAME having count(*) > 1
  2. If there are duplicates remove one of the entry which is newly created. 
  3. Re-run the Full load. 
Powered by Wolken Software