Clarity

Expand all | Collapse all

Load Data Warehouse Job fails

  • 1.  Load Data Warehouse Job fails

    Posted 09-23-2015 07:09 AM

    Hello,

     

    we have got a problem with executing/running Load Data Warehouse job. It always fails. In bg-dwh.log we found following error:

    INFO  2015-09-23 13:40:08,741 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event dwh_db_check_ - Starting job entry
    INFO  2015-09-23 13:40:08,741 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event DWH_SRC_CLARITY_CONN - New database connection defined
    INFO  2015-09-23 13:40:08,775 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event DWH_SRC_CLARITY_CONN - Connected to database.
    INFO  2015-09-23 13:40:08,775 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event dwh_db_check_ - Running SQL :BEGIN
    RPT_CALENDAR_SP();
    RPT_INV_HIERARCHY_SP();
    END;
    
    
    ERROR 2015-09-23 13:40:08,790 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event dwh_db_check_ - An error occurred executing this job entry : 
    Couldn't execute SQL: BEGIN
    RPT_CALENDAR_SP();
    RPT_INV_HIERARCHY_SP();
    END;
    
    
    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1341
    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1959
    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 2010
    ORA-06512: at line 1
    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 611
    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1045
    ORA-06512: at "PPM.CRV_CALENDAR_FCT", line 7
    ORA-06512: at line 1
    ORA-06512: at "PPM.RPT_CALENDAR_SP", line 85
    ORA-06512: at line 2
    
    
    
    
    INFO  2015-09-23 13:40:08,799 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event DWH_SRC_CLARITY_CONN - Connection to database closed!
    INFO  2015-09-23 13:40:08,801 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event dwh_prerequisite - Finished job entry [src_calendar_sp_orcl] (result=[false])
    INFO  2015-09-23 13:40:08,802 [dwh_prerequisite UUID: a08e5f5f-b9ac-46d4-ab9a-740883949aa3] dwh.event dwh_prerequisite - Finished job entry [src_db_check] (result=[false])
    INFO  2015-09-23 13:40:08,803 [Thread-12] dwh.event dwh_etl_master - Finished job entry [Prerequisite_check_job] (result=[false])
    INFO  2015-09-23 13:40:08,804 [Thread-12] dwh.event dwh_etl_master - Finished job entry [Pre_execution_config_step_orcl] (result=[false])
    INFO  2015-09-23 13:40:08,804 [Thread-12] dwh.event dwh_etl_master - Finished job entry [dwh_db_check_] (result=[false])
    INFO  2015-09-23 13:40:08,804 [Thread-12] dwh.event dwh_etl_master - Job execution finished
    INFO  2015-09-23 13:40:08,804 [Thread-12] dwh.event DWH_TGT_DM_CONN - New database connection defined
    INFO  2015-09-23 13:40:08,834 [Thread-12] dwh.event DWH_TGT_DM_CONN - Connected to database.
    INFO  2015-09-23 13:40:08,834 [Thread-12] dwh.event DWH_TGT_DM_CONN - Auto commit off
    DEBUG 2015-09-23 13:40:08,848 [Thread-12] dwh.event DWH_TGT_DM_CONN - Commit on database connection [DWH_TGT_DM_CONN]
    DEBUG 2015-09-23 13:40:08,849 [Thread-12] dwh.event DWH_TGT_DM_CONN - Commit on database connection [DWH_TGT_DM_CONN]
    INFO  2015-09-23 13:40:08,851 [Thread-12] dwh.event DWH_TGT_DM_CONN - Connection to database closed!
    ERROR 2015-09-23 13:40:08,857 [Dispatch pool-5-thread-4 : bg@cappm02 (tenant=clarity)] dwh.event ETL Job Failed. Log details below: 
    2015/09/23 13:40:08 - Prerequisite_check_job - Starting job entry
    2015/09/23 13:40:08 - Prerequisite_check_job - Loading job from XML file : [file:///opt/ppm/META-INF/datamart/etl_scripts\dwh_cmn_etl_jobs\dwh_jb_prerequisite.kjb]
    2015/09/23 13:40:08 - dwh_prerequisite - exec(4, 0, START.0)
    2015/09/23 13:40:08 - START - Starting job entry
    2015/09/23 13:40:08 - dwh_prerequisite - Starting entry [src_db_check]
    2015/09/23 13:40:08 - dwh_prerequisite - exec(5, 0, src_db_check.0)
    2015/09/23 13:40:08 - dwh_prerequisite - Starting entry [src_calendar_sp_orcl]
    2015/09/23 13:40:08 - dwh_prerequisite - exec(6, 0, src_calendar_sp_orcl.0)
    2015/09/23 13:40:08 - dwh_prerequisite - Finished job entry [src_calendar_sp_orcl] (result=[false])
    2015/09/23 13:40:08 - dwh_prerequisite - Finished job entry [src_db_check] (result=[false])
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Value to evaluate is oracle
    2015/09/23 13:40:08 - dwh_db_check_ - Comparing incoming value [oracle] with value [oracle]...
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - New database connection defined
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - Connected to database.
    2015/09/23 13:40:08 - dwh_db_check_ - Running SQL :-- This step is provided in case of any cofig steps that need to be carried out in the future w.r.t datawarehouse.
    2015/09/23 13:40:08 - dwh_db_check_ - 
    2015/09/23 13:40:08 - dwh_db_check_ - BEGIN
    2015/09/23 13:40:08 - dwh_db_check_ - DWH_CFG_PRE_CONFIG_SP(
    2015/09/23 13:40:08 - dwh_db_check_ -     P_DBLINK => 'PPMDB_LINK'
    2015/09/23 13:40:08 - dwh_db_check_ - );
    2015/09/23 13:40:08 - dwh_db_check_ - END;
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - Connection to database closed!
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Value to evaluate is oracle
    2015/09/23 13:40:08 - dwh_db_check_ - Comparing incoming value [oracle] with value [oracle]...
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - DWH_SRC_CLARITY_CONN - New database connection defined
    2015/09/23 13:40:08 - DWH_SRC_CLARITY_CONN - Connected to database.
    2015/09/23 13:40:08 - dwh_db_check_ - Running SQL :BEGIN
    2015/09/23 13:40:08 - dwh_db_check_ - RPT_CALENDAR_SP();
    2015/09/23 13:40:08 - dwh_db_check_ - RPT_INV_HIERARCHY_SP();
    2015/09/23 13:40:08 - dwh_db_check_ - END;
    2015/09/23 13:40:08 - 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 : 
    2015/09/23 13:40:08 - dwh_db_check_ - Couldn't execute SQL: BEGIN
    2015/09/23 13:40:08 - dwh_db_check_ - RPT_CALENDAR_SP();
    2015/09/23 13:40:08 - dwh_db_check_ - RPT_INV_HIERARCHY_SP();
    2015/09/23 13:40:08 - dwh_db_check_ - END;
    2015/09/23 13:40:08 - dwh_db_check_ - 
    2015/09/23 13:40:08 - dwh_db_check_ - [CA Clarity][Oracle JDBC Driver][Oracle]ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1341
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1959
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 2010
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at line 1
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 611
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1045
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.CRV_CALENDAR_FCT", line 7
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at line 1
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at "PPM.RPT_CALENDAR_SP", line 85
    2015/09/23 13:40:08 - dwh_db_check_ - ORA-06512: at line 2
    2015/09/23 13:40:08 - DWH_SRC_CLARITY_CONN - Connection to database closed!
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Value to evaluate is oracle
    2015/09/23 13:40:08 - dwh_db_check_ - Comparing incoming value [oracle] with value [oracle]...
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - New database connection defined
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - Connected to database.
    2015/09/23 13:40:08 - dwh_db_check_ - Running SQL :-- This step is provided in case of any cofig steps that need to be carried out in the future w.r.t datawarehouse.
    2015/09/23 13:40:08 - dwh_db_check_ - 
    2015/09/23 13:40:08 - dwh_db_check_ - BEGIN
    2015/09/23 13:40:08 - dwh_db_check_ - DWH_CFG_PRE_CONFIG_SP(
    2015/09/23 13:40:08 - dwh_db_check_ -     P_DBLINK => 'PPMDB_LINK'
    2015/09/23 13:40:08 - dwh_db_check_ - );
    2015/09/23 13:40:08 - dwh_db_check_ - END;
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - Connection to database closed!
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Value to evaluate is oracle
    2015/09/23 13:40:08 - dwh_db_check_ - Comparing incoming value [oracle] with value [oracle]...
    2015/09/23 13:40:08 - START - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Value to evaluate is oracle
    2015/09/23 13:40:08 - dwh_db_check_ - Comparing incoming value [oracle] with value [oracle]...
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - New database connection defined
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - Connected to database.
    2015/09/23 13:40:08 - dwh_db_check_ - Running SQL :-- This step is provided in case of any cofig steps that need to be carried out in the future w.r.t datawarehouse.
    2015/09/23 13:40:08 - dwh_db_check_ - 
    2015/09/23 13:40:08 - dwh_db_check_ - BEGIN
    2015/09/23 13:40:08 - dwh_db_check_ - DWH_CFG_PRE_CONFIG_SP(
    2015/09/23 13:40:08 - dwh_db_check_ -     P_DBLINK => 'PPMDB_LINK'
    2015/09/23 13:40:08 - dwh_db_check_ - );
    2015/09/23 13:40:08 - dwh_db_check_ - END;
    2015/09/23 13:40:08 - DWH_TGT_DM_CONN - Connection to database closed!
    2015/09/23 13:40:08 - dwh_db_check_ - Starting job entry
    2015/09/23 13:40:08 - dwh_db_check_ - Value to evaluate is oracle
    2015/09/23 13:40:08 - dwh_db_check_ - Comparing incoming value [oracle] with value [oracle]...
    2015/09/23 13:40:08 - START - Starting job entry
    

     

    Please have you got any idea what can cause this error or how to solve this issue?

     

    Thanks in advance.

     

    Best Regards,

    Martin



  • 2.  Re: Load Data Warehouse Job fails

    Posted 09-23-2015 07:16 AM

    Can you run the Update Business Object Tables job and then run the Load Data Ware house to see if it helps



  • 3.  Re: Load Data Warehouse Job fails

    Posted 09-23-2015 08:03 AM

    Hello Suman,

     

    thank you for your response. I tried to run Update Business Object Tables and when I did not check any checkbox it finished successfully (after this I tried to run Load Data Warehouse, but it's still failing), but after I checked update Reporting Calendar then it failed and in bg-ca.log is following error:

    ERROR 2015-09-23 14:58:42,680 [Dispatch pool-5-thread-4 : bg@cappm02 (tenant=clarity)] niku.njs (clarity:admin:5695240__D7128589-24A1-4934-BB7E-AA1B24EE4E2C:Update Business Objects Report Tables) Error executing job: 5027007 error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in RPT_SETUP_SP - ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1341

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1959

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 2010

    ORA-06512: at line 1

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 611

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1045

    ORA-06512: at "PPM.CRV_CALENDAR_FCT", line 7

    ORA-06512: at line 1

    ORA-06512: at "PPM.RPT_SETUP_SP", line 35

    ORA-06512: at line 1

     

    Which is same as in my previous post. Please have you got any idea about this?

     

    Best Regards,

    Martin



  • 4.  Re: Load Data Warehouse Job fails

    Posted 09-23-2015 08:15 AM

    Can you check all the parameters and run the job and see if the error changes.

     

    Regards

    Suman Pramanik



  • 5.  Re: Load Data Warehouse Job fails

    Posted 09-23-2015 08:30 AM

    Tried with all parameters, but the error is still the same:

    ERROR 2015-09-23 15:27:43,907 [Dispatch pool-5-thread-8 : bg@cappm02 (tenant=clarity)] niku.njs (clarity:admin:5697051__965B615A-E316-413E-9A8C-605A91374691:Update Business Objects Report Tables) Error executing job: 5028003 error java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20000: Error in RPT_SETUP_SP - ORA-06502: PL/SQL: numeric or value error: character to number conversion error

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1341

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1959

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 2010

    ORA-06512: at line 1

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 611

    ORA-06512: at "PPM.CRV_SQL_CURVE_PKG", line 1045

    ORA-06512: at "PPM.CRV_CALENDAR_FCT", line 7

    ORA-06512: at line 1

    ORA-06512: at "PPM.RPT_SETUP_SP", line 35

    ORA-06512: at line 1

     

    Best Regards,

    Martin



  • 6.  Re: Load Data Warehouse Job fails

    Posted 09-23-2015 09:14 AM

    Hi Martin,

     

    This could be related to problematic timeslices also.

    RPT_SETUP_SP would execute the below mentioned procedures.

     

        RPT_CALENDAR_SP;

        RPT_PROGRAM_HIERARCHY_SP;

        RPT_INV_HIERARCHY_SP;

        RPT_WBSINDEX_SP;

        RPT_RES_SKILLS_INDEX_SP;

        NBI_POPULATE_OBS_DIM_SP;

     

    You might want to check which of them is failing.

    Before that could you please try the below mentioned and see if it helps.

     

    When setting up the DWH time slices from the
    Administration > Data Adminstration > Time Slices page, please ensure that the start day of the slice ID matches that of the
    Administration > Project Management > Settings > General > First Day of Work Week

    first day of work week.JPG

    Use the following queries to quickly find out what the days are set to:
    --a
    select sr.id, sr.request_name, TO_CHAR(SR.FROM_DATE, 'MM-DD-YYYY HH24:MI:SS AM Dy')
    from PRJ_BLB_SLICEREQUESTS SR
    where SR.IS_DWH_REQUEST = 1
    and SR.PERIOD = 1 --weekly
       order by SR.id

     

    --b
    select prweekstart from PRSITE
    0 - Sunday
    1 - Monday
    2 - Tuesday
    3 - Wednesday
    4 - Thursday
    5 - Friday
    6 - Saturday

     

    If the starting day does not match,
    reslice the DWH request to start on a day consistent with the first workday of the week.

    Run the Time Slicing job.
    Run the Load Data Warehouse (LDW) job.

     

     

    Regards

    Nitin



  • 7.  Re: Load Data Warehouse Job fails

    Posted 09-23-2015 11:17 AM

    Hi Nitin,

     

    I run both queries and the starting days mathes for all time slices and the first day of week.

     

    The time slices job runs every minute and it does not fail.

     

    Regards,

    Martin



  • 8.  Re: Load Data Warehouse Job fails

    Posted 09-28-2015 01:38 AM

    Hi Martin,

     

    May be you would want to troubleshoot the individual procedures mentioned above to se which one is failing.

     

     

    Regards

    Nitin



  • 9.  Re: Load Data Warehouse Job fails

    Posted 09-28-2015 10:03 AM

    Hi Martin,

     

     

    Please raise a Support case so we can troubleshoot this with you. It would be best also if you can provide a copy of your database for us to debug together. I looked into your package but it seems like additional debug may be needed.

    Feel free to let me know the issue number once you raise it.

     

    Thank you

     

    Kind Regards

     

    Nika Hadzhikidi

    CA Technologies

    Principal Support Engineer



  • 10.  Re: Load Data Warehouse Job fails

    Posted 10-09-2015 03:09 PM

    Just to verify do have your Language and Entity configured in System Options?  Did the Job run previously and now it fails all of a sudden or was that the first time you had tried to run it?



  • 11.  Re: Load Data Warehouse Job fails

    Posted 10-09-2015 06:28 PM

    Hi Martin - Were you able to resolve this issue? If so please update with what the solution was. Thanks! Chris



  • 12.  Re: Load Data Warehouse Job fails

    Posted 12-09-2016 03:51 AM

    Hi Marin,

     

    We are facing exactly the same issue as the one you described so if you have a solution for this, we appreciate if you could share.

     

    Thanks in Advance,

    Eero Gröndahl



  • 13.  Re: Load Data Warehouse Job fails

    Posted 01-04-2017 03:22 AM

    We got our issue solved. It was due to invalid data in PRCALENDAR table. Values for daily hours were separated with the same character that was used as a decimal separator. sqlcurve was was 7,25,7,25,7,25... when it should have been 7.25,7.25,7.25,7.25...

     

    Recreating the calendar in Clarity fixed the issue.

     

    Hope this helps someone!

     

    Regards,

    Eero Gröndahl



  • 14.  Re: Load Data Warehouse Job fails

    Posted 01-04-2017 03:53 AM

    The values are comma separated due to  database parameters and not corruption, if you follow the correct parameter mentioned in the install guide you shouldn't face the issue 

     

    Regards

    Suman Pramanik