As there a lot of topics with DWH load job failures, I was not able to find anything related to my error.
I really don't understand how in OOTB built query is used attribute which is not presented in the table:
ERROR 2018-09-20 10:26:21,110 [dwh_get_settings_variables - dwh_cfg_settings_query] dwh.event dwh_cfg_settings_query - Unexpected errorERROR 2018-09-20 10:26:21,117 [dwh_get_settings_variables - dwh_cfg_settings_query] dwh.event dwh_cfg_settings_query - org.pentaho.di.core.exception.KettleDatabaseException:An error occurred executing SQL:SELECT NVL(CAST(ENTITY_KEY AS VARCHAR2(60)),'0') ENTITY_KEY, NVL(WEEK_START_DAY,-1) WEEK_START_DAY, LANGUAGE_CODE, LANGUAGE_CODE_KEY, NVL(CURRENCY_PRECISION,0) CURRENCY_PRECISION, NVL(CURRENT_FISCAL_PERIOD_KEY,0) CURRENT_FISCAL_PERIOD_KEY, NVL(WEEKLY_SLICES,0) WEEKLY_SLICES, NVL(MONTHLY_SLICES,0) MONTHLY_SLICES, NVL(FISCAL_SLICES,0) FISCAL_SLICES, NVL(ONE_SET_OF_FISCAL_PERIODS,0) ONE_SET_OF_FISCAL_PERIODS, NVL(SINGLE_CURRENCY,0) SINGLE_CURRENCY, NVL(TO_CHAR(DAILY_ROLLOVER_DATE,'YYYY/MM/DD HH24:MI:SS'),'1900/01/01 00:00:00') DAILY_ROLLOVER_DATE, NVL(TO_CHAR(WEEKLY_ROLLOVER_DATE,'YYYY/MM/DD HH24:MI:SS'),'1900/01/01 00:00:00') WEEKLY_ROLLOVER_DATE, NVL(TO_CHAR(MONTHLY_ROLLOVER_DATE,'YYYY/MM/DD HH24:MI:SS'),'1900/01/01 00:00:00') MONTHLY_ROLLOVER_DATE, NVL(TO_CHAR(FISCAL_ROLLOVER_DATE,'YYYY/MM/DD HH24:MI:SS'),'1900/01/01 00:00:00') FISCAL_ROLLOVER_DATE, NVL((SELECT TO_CHAR(DW_UPDATED_DATE,'YYYY-MM-DD HH24:MI:SS') FROM DWH_CFG_AUDIT WHERE TABLE_NAME = 'DWH_CMN_PERIOD'),'1900-01-01 00:00:00') LAST_RUN_DATE, NVL(TO_CHAR(DWH_DIM_START_DATE,'YYYY/MM/DD HH24:MI:SS'),'1900/01/01 00:00:00') LAST_START_DATE, NVL(USE_DATAPUMP,0) USE_DATAPUMPFROM DWH_CFG_SETTINGSCaused by: java.sql.SQLSyntaxErrorException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "USE_DATAPUMP": invalid identifier
This "USE_DATAPUMP" attribute is really not in the DWH_CFG_SETTINGS table:
Of course when I comment this attribute, query works fine.
Can somebody explain me that to be able to run DWH load (full load)?
I'm attaching full log for your refference.
Looks like the attribute is somehow missing, can you run the command
ALTER TABLE DWH_CFG_SETTINGS ADD USE_DATAPUMP NUMBER
and then run the DWH job please
Thanks, it worked then. But why the one attribute was missing....
However another error appeared:
APPEND_COL_SQL - org.pentaho.di.core.exception.KettleStepException: Error while running this step!Couldn't execute SQL: ALTER TABLE DWH_INV_STATUS_REPORT ADD SR_REPORT_STATUS_KEY VARCHAR2(255)[CA Clarity][Oracle JDBC Driver][Oracle]ORA-00942: table or view does not exist
It's really strange for me...We run DWH load jobs in the past (2 months) ago when it worked, and now it's not...
This seems to be some mismatch with PPM and DWH schema. Now the entire status report is missing. May be you should raise a case so that we can get it fixed.
Hmm,do you think restore of the DWH initial dump would help? As we don't have any important data there....
In the meantime - case created 01196483.
Just the DWH might not help, lets work out via case