Clarity

CA PPM 14.4

JOB LOGS:
ETL Job Failed. Please see log bg-dwh.log for details.

User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_INVESTMENT_LN      at Processor.processRow(Processor.java:64)      at org.pentaho.di.trans.steps.userdefinedjavaclass.UserDefinedJavaClass.processRow(UserDefinedJavaClass.java:1181)      at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)      at java.lang.Thread.run(Thread.java:745)

Not sure why this job is failing. Any ideas??

Can you attach the DWH log please 

attached -thanks.

timestamp to look for in log - 

2018/02/16 05:53:14

This is the 1st error I am seeing 

Couldn't execute SQL: MERGE INTO DWH_INV_INVESTMENT_LN TGT
USING
(
select OBJ_ALIGN_FACTOR3_CAPTION, OBJ_ALIGN_FACTOR2_CAPTION, OBJ_ALIGN_FACTOR4_CAPTION, OBJ_ALIGN_FACTOR1_CAPTION, OBJ_ALIGN_FACTOR5_CAPTION, OBJ_ALIGN_FACTOR6_CAPTION, COST_TYPE_CAPTION, FORD_BUDGET_TYPE_CAPTION, FORD_FUNDING_SOURCE_CAPTION, FORD_INV_PF_ORG_CAPTION, FORD_INV_SCALE_BUS_CAPTION, FORD_INV_SCALE_EXEC_CAPTION, FORD_INV_SCALE_TECH_CAPTION, FORD_INV_SKILL_TEAM_CAPTION, FORD_MODE_CAPTION, FORD_PRIORITIZATION_CAPTION, FORD_RANK_CAPTION, GOAL_CODE_CAPTION, INVESTMENT_KEY, STATUS_CAPTION, DW_INVESTMENT_TYPE_CAPTION, LANGUAGE_CODE, LANGUAGE_ID, OBJ_PFL_CATEGORY1_CAPTION, OBJ_PFL_CATEGORY2_CAPTION, OBJ_PFL_CATEGORY3_CAPTION, OBJ_PFL_CATEGORY4_CAPTION, PROGRESS_CAPTION, STAGE_CODE_CAPTION, STATUS_INDICATOR_CAPTION, TRACKMODE_CAPTION, OBJ_WORK_STATUS_CAPTION, to_date('2018/02/16 07:14:37', 'yyyy/mm/dd HH24:MI:SS') as dw_updated_date from DWH_DW_INVESTMENT_V@ A where CLARITY_UPDATED_DATE >= to_date('1910/01/01 00:00:00', 'yyyy/MM/dd HH24:mi:ss') AND CLARITY_UPDATED_DATE <= (SELECT DISTINCT DWH_DIM_START_DATE FROM DWH_CFG_SETTINGS)
) SRC
ON
(
SRC.INVESTMENT_KEY = TGT.INVESTMENT_KEY
AND SRC.LANGUAGE_CODE = TGT.LANGUAGE_CODE
)
WHEN MATCHED THEN UPDATE SET TGT.ALIGN_ARCHITECTURAL = SRC.OBJ_ALIGN_FACTOR3_CAPTION, TGT.ALIGN_BUSINESS_UNIT = SRC.OBJ_ALIGN_FACTOR2_CAPTION, TGT.ALIGN_COMMERCIAL = SRC.OBJ_ALIGN_FACTOR4_CAPTION, TGT.ALIGN_CORPORATE = SRC.OBJ_ALIGN_FACTOR1_CAPTION, TGT.ALIGN_REGULATORY = SRC.OBJ_ALIGN_FACTOR5_CAPTION, TGT.ALIGN_TECHNOLOGY = SRC.OBJ_ALIGN_FACTOR6_CAPTION, TGT.COST_TYPE = SRC.COST_TYPE_CAPTION, TGT.FORD_BUDGET_TYPE = SRC.FORD_BUDGET_TYPE_CAPTION, TGT.FORD_FUNDING_SOURCE = SRC.FORD_FUNDING_SOURCE_CAPTION, TGT.FORD_INV_PF_ORG = SRC.FORD_INV_PF_ORG_CAPTION, TGT.FORD_INV_SCALE_BUS = SRC.FORD_INV_SCALE_BUS_CAPTION, TGT.FORD_INV_SCALE_EXEC = SRC.FORD_INV_SCALE_EXEC_CAPTION, TGT.FORD_INV_SCALE_TECH = SRC.FORD_INV_SCALE_TECH_CAPTION, TGT.FORD_INV_SKILL_TEAM = SRC.FORD_INV_SKILL_TEAM_CAPTION, TGT.FORD_MODE = SRC.FORD_MODE_CAPTION, TGT.FORD_PRIORITIZATION = SRC.FORD_PRIORITIZATION_CAPTION, TGT.FORD_RANK = SRC.FORD_RANK_CAPTION, TGT.GOAL = SRC.GOAL_CODE_CAPTION, TGT.INVESTMENT_STATUS = SRC.STATUS_CAPTION, TGT.INVESTMENT_TYPE = SRC.DW_INVESTMENT_TYPE_CAPTION, TGT.LANGUAGE_CODE_KEY = SRC.LANGUAGE_ID, TGT.PORTFOLIO_CAT1 = SRC.OBJ_PFL_CATEGORY1_CAPTION, TGT.PORTFOLIO_CAT2 = SRC.OBJ_PFL_CATEGORY2_CAPTION, TGT.PORTFOLIO_CAT3 = SRC.OBJ_PFL_CATEGORY3_CAPTION, TGT.PORTFOLIO_CAT4 = SRC.OBJ_PFL_CATEGORY4_CAPTION, TGT.PROGRESS = SRC.PROGRESS_CAPTION, TGT.STAGE = SRC.STAGE_CODE_CAPTION, TGT.STATUS_INDICATOR = SRC.STATUS_INDICATOR_CAPTION, TGT.TRACK_MODE = SRC.TRACKMODE_CAPTION, TGT.WORK_STATUS = SRC.OBJ_WORK_STATUS_CAPTION, TGT.dw_updated_date = SRC.dw_updated_date
WHEN NOT MATCHED THEN
INSERT (ALIGN_ARCHITECTURAL, ALIGN_BUSINESS_UNIT, ALIGN_COMMERCIAL, ALIGN_CORPORATE, ALIGN_REGULATORY, ALIGN_TECHNOLOGY, COST_TYPE, FORD_BUDGET_TYPE, FORD_FUNDING_SOURCE, FORD_INV_PF_ORG, FORD_INV_SCALE_BUS, FORD_INV_SCALE_EXEC, FORD_INV_SCALE_TECH, FORD_INV_SKILL_TEAM, FORD_MODE, FORD_PRIORITIZATION, FORD_RANK, GOAL, INVESTMENT_KEY, INVESTMENT_STATUS, INVESTMENT_TYPE, LANGUAGE_CODE, LANGUAGE_CODE_KEY, PORTFOLIO_CAT1, PORTFOLIO_CAT2, PORTFOLIO_CAT3, PORTFOLIO_CAT4, PROGRESS, STAGE, STATUS_INDICATOR, TRACK_MODE, WORK_STATUS, dw_updated_date)
VALUES (SRC.OBJ_ALIGN_FACTOR3_CAPTION, SRC.OBJ_ALIGN_FACTOR2_CAPTION, SRC.OBJ_ALIGN_FACTOR4_CAPTION, SRC.OBJ_ALIGN_FACTOR1_CAPTION, SRC.OBJ_ALIGN_FACTOR5_CAPTION, SRC.OBJ_ALIGN_FACTOR6_CAPTION, SRC.COST_TYPE_CAPTION, SRC.FORD_BUDGET_TYPE_CAPTION, SRC.FORD_FUNDING_SOURCE_CAPTION, SRC.FORD_INV_PF_ORG_CAPTION, SRC.FORD_INV_SCALE_BUS_CAPTION, SRC.FORD_INV_SCALE_EXEC_CAPTION, SRC.FORD_INV_SCALE_TECH_CAPTION, SRC.FORD_INV_SKILL_TEAM_CAPTION, SRC.FORD_MODE_CAPTION, SRC.FORD_PRIORITIZATION_CAPTION, SRC.FORD_RANK_CAPTION, SRC.GOAL_CODE_CAPTION, SRC.INVESTMENT_KEY, SRC.STATUS_CAPTION, SRC.DW_INVESTMENT_TYPE_CAPTION, SRC.LANGUAGE_CODE, SRC.LANGUAGE_ID, SRC.OBJ_PFL_CATEGORY1_CAPTION, SRC.OBJ_PFL_CATEGORY2_CAPTION, SRC.OBJ_PFL_CATEGORY3_CAPTION, SRC.OBJ_PFL_CATEGORY4_CAPTION, SRC.PROGRESS_CAPTION, SRC.STAGE_CODE_CAPTION, SRC.STATUS_INDICATOR_CAPTION, SRC.TRACKMODE_CAPTION, SRC.OBJ_WORK_STATUS_CAPTION, SRC.dw_updated_date)

[CA Clarity][Oracle JDBC Driver][Oracle]ORA-01722: invalid number

So look into any of the custom attribute you added in investment object and remove the same from DWH. You should be ok with it 

Regards

Suman Pramanik 

Hi Deepu,

Can you tell us if you have added any new lookups recently?  I have noted one case where a customer had added a new lookup and it was causing the job to fail.  They had removed that new lookup from dwh, and then ran the job and it worked, then they re-added the new lookup to dwh and ran the job again and it worked that time as well.   

Hope this helps a bit.

Let us know!

Jon I.