Clarity

Expand all | Collapse all

Load Datawarehouse Job Fails - Full Load

Jump to Best Answer
  • 1.  Load Datawarehouse Job Fails - Full Load

    Posted 06-28-2017 06:35 AM

    We are in 14.2. When we try to run the Load Datawarehouse Job as a full load, it fails within few seconds with the following error. It looks like there is no table name passed as a first parameter.

     

    An error occurred executing this job entry :
    Couldn't execute SQL: BEGIN
    CMN_DISABLE_CONSTRAINTS_SP(NULL,'PK');
    END;

     

    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-01735: invalid ALTER TABLE option
    ORA-06512: at "PPM_DWH.CMN_DISABLE_CONSTRAINTS_SP", line 25
    ORA-06512: at line 2

     

    Can somebody please help?

    @NIKA HADZHIKIDI

     

    Regards

    Augustin#



  • 2.  Re: Load Datawarehouse Job Fails - Full Load
    Best Answer

    Posted 06-28-2017 04:05 PM

    Hi Augustin,

     

     

    I see, it does try to run it for NULL.

     

    Please connect as PPM_DWH and check your user_constraints:

    select * from user_constraints

     

    Make sure you don't have any weird constraint that is not associated to any table (column TABLE_NAME ). If you have any:

     

    1. drop them with the help of your DBA
    2. Run Load Data Warehouse - Full

     

    If you don't or that doesn't help, please do the following:

    1. Reload your DWH database from a template (situated in $cappm/database/backups
    2. Recreate the db link
    3. If you have any patches on 14.2 then run the command:
      admin db dwh-upgrade -Dupgrade.phase=maintenance
    4. Run Load Data Warehouse - Full

     

    Hope this helps -Nika



  • 3.  Re: Load Datawarehouse Job Fails - Full Load

    Posted 06-29-2017 10:14 AM

    Hi Nika

     

    Thank you so much for your reply. Your response really helped!

     

    Yes, it was having weird constraint names and I removed them with the help of our DBA.

    Then I tried running a full load; it did not fail with the same error but with a different error now.

     

    ERROR 2017-06-29 07:17:29,603 [dwh_etl_interface_dim - User Defined Java Class] dwh.event User Defined Java Class - java.lang.RuntimeException: ERROR: THERE WERE ERRORS DURING DIMENSION JOB EXECUTION FOR THE TABLE - DWH_INV_STATUS_REPORT

    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00904: "SR_COST_EFFORT_STATUS_KEY": invalid identifier

     

    I think this is somethinng related to a system field - Cost and Effort Status in the system sub object - Status Report but I could not further analyse or able to fix this issue.

     

    Can you please advise?



  • 4.  Re: Load Datawarehouse Job Fails - Full Load

    Posted 06-30-2017 11:05 AM

    Hi Augustin

     

    Great to hear this issue was resolved!

     

    Please do the following to resolve the new error on Status Report:

     

    1. Go to Objects - Status Report - uncheck the entire object from Included in Data Warehouse
    2. Run Load Data Warehouse job - Full (it's okay if it fails)
    3. Now go back and include the Object back for Data Warehouse.
    4. Go to Attributes tab and check in all the attributes apart from cop_phase
    5. Run Load Data Warehouse job - Full

     

     

    See if that helps. If it doesn't, uncheck and check the field Cost and Effort Status for Data Warehouse and try running the Full Load again.

     

    Hope this helps -Nika



  • 5.  Re: Load Datawarehouse Job Fails - Full Load

    Posted 07-03-2017 07:44 AM

    Hi Nika

     

    You are really rocking!

    Issue fixed. I followed those 5 steps and that cleared all the issues.

     

    Thanks a ton!