Hi Nika,
Thanks for your Answer. I believe this would help. But when I unchecked the Datawarehouse from Custom object. I ran the job and got the following error.
Do I need to delete the DB link/delete any tables related to DWH before I run the job? Please advise.
INFO 2016-05-23 10:53:43,894 [Thread-868] dwh.event dwh_etl_master - Job execution finished
ERROR 2016-05-23 10:53:44,011 [Dispatch pool-5-thread-7 : (tenant=clarity)] dwh.event ETL Job Failed. Log details below:
2016/05/23 10:53:43 - dwh_db_check_ - ERROR (version 5.0.2, build 1 from 2013-12-04_15-52-25 by buildguy) : An error occurred executing this job entry :
2016/05/23 10:53:43 - dwh_db_check_ - Couldn't execute SQL: MERGE INTO DWH_META_COLUMNS A USING
2016/05/23 10:53:43 - dwh_db_check_ - (
2016/05/23 10:53:43 - dwh_db_check_ - SELECT
2016/05/23 10:53:43 - dwh_db_check_ - UPPER(SRC_TABLE_NAME) SRC_TABLE_NAME
2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(SRC_COLUMN_NAME) SRC_COLUMN_NAME
2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(DWH_TABLE_NAME) DWH_TABLE_NAME
2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(DWH_COLUMN_NAME) DWH_COLUMN_NAME
2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(ATTR_TYPE) ATTR_TYPE
2016/05/23 10:53:43 - dwh_db_check_ - , CASE
2016/05/23 10:53:43 - dwh_db_check_ - WHEN (UPPER(ATTR_DATA_TYPE)='DATE' OR UPPER(ATTR_DATA_TYPE)='INTEGER' OR UPPER(ATTR_DATA_TYPE)='TIMESTAMP') THEN UPPER(ATTR_DATA_TYPE)
2016/05/23 10:53:43 - dwh_db_check_ - WHEN (UPPER(ATTR_TYPE)='CLOB' AND UPPER(ATTR_DATA_TYPE) NOT LIKE '%VARCHAR%') THEN UPPER(ATTR_DATA_TYPE)
2016/05/23 10:53:43 - dwh_db_check_ - ELSE UPPER(ATTR_DATA_TYPE)||'('||NVL(ATTR_DATA_SIZE,'')||')'
2016/05/23 10:53:43 - dwh_db_check_ - END ATTR_DATA_TYPE_SRC
2016/05/23 10:53:43 - dwh_db_check_ - , IS_LOOKUP_ATTR
2016/05/23 10:53:43 - dwh_db_check_ - , IS_MULTIVALUED
2016/05/23 10:53:43 - dwh_db_check_ - , LOOKUP_TYPE
2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(LKP_DIM_TABLE_NAME) LKP_DIM_TABLE_NAME
2016/05/23 10:53:43 - dwh_db_check_ - , UPPER(DWH_LOOKUP_KEY) DWH_LOOKUP_KEY
2016/05/23 10:53:43 - dwh_db_check_ - , IS_CUSTOM
2016/05/23 10:53:43 - dwh_db_check_ - , IS_KEY
2016/05/23 10:53:43 - dwh_db_check_ - , IS_DELETED DROP_FLAG
2016/05/23 10:53:43 - dwh_db_check_ - , 0 COL_MOD
2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(DIM_KEY) DIM_KEY
2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(DIM_DISPLAY) DIM_DISPLAY
2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(OBJECT_CODE) OBJECT_CODE
2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(ATTRIBUTE_CODE) ATTRIBUTE_CODE
2016/05/23 10:53:43 - dwh_db_check_ - ,UPPER(EXTENDED_TYPE) EXTENDED_TYPE
2016/05/23 10:53:43 - dwh_db_check_ - FROM
2016/05/23 10:53:43 - dwh_db_check_ - DWH_META_COLUMNS@PPMDBLINK
2016/05/23 10:53:43 - dwh_db_check_ - ) B
2016/05/23 10:53:43 - dwh_db_check_ - ON
2016/05/23 10:53:43 - dwh_db_check_ - ( 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 )
2016/05/23 10:53:43 - dwh_db_check_ - WHEN MATCHED THEN UPDATE SET
2016/05/23 10:53:43 - dwh_db_check_ - 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,
2016/05/23 10:53:43 - dwh_db_check_ - 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,
2016/05/23 10:53:43 - dwh_db_check_ - A.ATTRIBUTE_CODE = B.ATTRIBUTE_CODE, A.EXTENDED_TYPE = B.EXTENDED_TYPE
2016/05/23 10:53:43 - dwh_db_check_ - WHEN NOT MATCHED THEN INSERT
2016/05/23 10:53:43 - dwh_db_check_ - (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
2016/05/23 10:53:43 - dwh_db_check_ - ,A.DIM_KEY,A.DIM_DISPLAY,A.IS_KEY,A.OBJECT_CODE,A.ATTRIBUTE_CODE,A.EXTENDED_TYPE)
2016/05/23 10:53:43 - dwh_db_check_ - VALUES
2016/05/23 10:53:43 - dwh_db_check_ - (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,
2016/05/23 10:53:43 - dwh_db_check_ - B.DIM_KEY,B.DIM_DISPLAY,B.IS_KEY,B.OBJECT_CODE,B.ATTRIBUTE_CODE,B.EXTENDED_TYPE)
2016/05/23 10:53:43 - dwh_db_check_ -
2016/05/23 10:53:43 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-30926: unable to get a stable set of rows in the source tables
2016/05/23 10:53:42 - audit table name - & DWH_CFG_AUDIT
2016/05/23 10:53:42 - audit table name - & DWH_CFG_AUDIT