Load Data Warehouse Job fails

[Martin Snizek]

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

[Suman Pramanik]

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

[Martin Snizek]

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

[Suman Pramanik]

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

 

Regards

Suman Pramanik

[Martin Snizek]

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

[Anon Anon]

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

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

[Martin Snizek]

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

[Anon Anon]

Hi Martin,

 

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

 

 

Regards

Nitin

[Nika Hadzhikidi]

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

[Anon Anon]

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?

[System]

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

[Anon Anon]

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

[Anon Anon]

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

[Suman Pramanik]

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