Clarity

 View Only
  • 1.  Load DWH job failed: "USE_DATAPUMP": invalid identifier

    Posted Sep 20, 2018 04:50 AM
      |   view attached

    Hi All,

     

    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 error
    ERROR 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_DATAPUMP
    FROM   DWH_CFG_SETTINGS

    Caused 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.

    Thanks,

     

    Matej

    Attachment(s)

    zip
    bg-dwh.log.zip   3 KB 1 version


  • 2.  Re: Load DWH job failed: "USE_DATAPUMP": invalid identifier

    Broadcom Employee
    Posted Sep 20, 2018 05:17 AM

    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 



  • 3.  Re: Load DWH job failed: "USE_DATAPUMP": invalid identifier

    Posted Sep 20, 2018 05:30 AM

    Hi Suman,

     

    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...

     

    Thanks,

    Matej



  • 4.  Re: Load DWH job failed: "USE_DATAPUMP": invalid identifier

    Broadcom Employee
    Posted Sep 20, 2018 05:36 AM

    Hi Matej

     

    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.

     

    Regards
    Suman Pramanik 



  • 5.  Re: Load DWH job failed: "USE_DATAPUMP": invalid identifier

    Posted Sep 20, 2018 05:45 AM

    Hi Suman,

     

    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.

     

    Matej



  • 6.  Re: Load DWH job failed: "USE_DATAPUMP": invalid identifier

    Broadcom Employee
    Posted Sep 20, 2018 07:23 AM

    Just the DWH might not help, lets work out via case