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.