AnsweredAssumed Answered

LOAD DATAWAREHOUSE JOB FAILS

Question asked by elkuani on Oct 14, 2017
Latest reply on Oct 17, 2017 by Suman Pramanik

Hi, LOAD DATAWAREHOUSE JOB fails with the following error on the logs:

 

2017/10/13 23:01:19 - User Defined Java Class.0 - *****************************************************************
2017/10/13 23:01:19 - User Defined Java Class.0 -  
2017/10/13 23:01:19 - User Defined Java Class.0 -  STARTING EXECUTION FOR THE DIMENSION TABLE - DWH_CMN_PERIOD
2017/10/13 23:01:19 - User Defined Java Class.0 -  
2017/10/13 23:01:19 - User Defined Java Class.0 - *****************************************************************
2017/10/13 23:01:22 - User Defined Java Class.0 - DWH_CMN_PERIOD - Job Log: 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [MSSQL?]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [Get Error Variables]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Get Error Variables - Loading transformation from XML file [file:///G:/CA/PPM/META-INF/datamart/etl_scripts/dwh_cmn_etl_jobs/dwh_mssql_error_occurred.ktr]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_occurred - Dispatching started for transformation [dwh_mssql_error_occurred]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count.0 - Finished reading query, closing connection.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Setting environment variables...
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Set variable ERROR_COUNT to value [0]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message.0 - Finished reading query, closing connection.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Value.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message Variables.0 - We didn't receive a row while setting the default values.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message Variables.0 - Set variable ERROR_NUMBER to value [0]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Message Variables.0 - Finished after 0 rows.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Finished after 1 rows.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - Error Count Variable.0 - Finished processing (I=0, O=0, R=1, W=1, U=0, E=0)
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [No Errors?]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Starting entry [Success 2]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [Success 2] (result=[true])
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [No Errors?] (result=[true])
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [Get Error Variables] (result=[true])
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:19 - dwh_mssql_error_handling - Finished job entry [MSSQL?] (result=[true])
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - dwh_mssql_error_handling - Starting entry [MSSQL?]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - dwh_mssql_error_handling - Starting entry [Get Error Variables]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Get Error Variables - Loading transformation from XML file [file:///G:/CA/PPM/META-INF/datamart/etl_scripts/dwh_cmn_etl_jobs/dwh_mssql_error_occurred.ktr]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - dwh_mssql_error_occurred - Dispatching started for transformation [dwh_mssql_error_occurred]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count.0 - Finished reading query, closing connection.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count Variable.0 - Setting environment variables...
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message.0 - Finished reading query, closing connection.
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count Variable.0 - Set variable ERROR_COUNT to value [1]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Count.0 - Finished processing (I=1, O=0, R=0, W=1, U=0, E=0)
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message Variables.0 - Setting environment variables...
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message Variables.0 - Set variable TABLE_NAME to value [DWH_CMN_PERIOD]
2017/10/13 23:01:22 - User Defined Java Class.0 - 2017/10/13 23:01:22 - Error Message Variables.0 - Set variable ERROR_MESSAGE to value [Violation of PRIMARY KEY constraint 'DWH_CMN_PERIOD_PK'. Cannot insert duplicate key in object 'ppm_dwh.DWH_CMN_PERIOD'. The duplicate key value is (1006210).]

 

I've been working with CA Support (Argentina) on this case for over a week, but their lack of an accurate and timely response is becoming a problem.

 

Any ideas on how can I fix this? I've been working on my timeslices configuration since there was a problem related to that on the health report.

The weird thing is that I tried a new timeslices configuration on our development environment (this is a production db from september 25) and the job works fine, but when I applied the same configuration in our production environment it still fails with the error attached.

 

Any input will be highly appreciated.

 

Regarads,

Juan.

Outcomes