AnsweredAssumed Answered

Load Data Warehouse Job fails

Question asked by msnizek on Sep 23, 2015
Latest reply on Jan 4, 2017 by Suman Pramanik

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

Outcomes