LOAD DATAWAREHOUSE JOB FAILS

[Juan A. Segovia]

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.

[Suman Pramanik]

Hi Juan,

 

Will it be possible to BG-DWH all logs, we can take a look and suggest.

 

Regards

Suman Pramanik 

[Juan A. Segovia]

Hi Suman, thanks for your interest and response. I'm inluding those logs so you can check the out along with app and bg logs.

 

Thanks,

 

Juan.

[Nika Hadzhikidi]

Hi Juan

 

Looks like you have a duplicate period in DWH_CMN_PERIOD, most likely copied over from PPM. Please try the following solution:

 

1. Backup and truncate the table RPT_CALENDAR on PPM database
2. Run the job Update Report Tables until completion.
3. Run Load Data Warehouse - Full Load

 

See if you still have the issue.

 

Hope this helps -Nika

[Juan A. Segovia]

Nika, thanks so much, this seemed to be the case. Finally my LDW job is working!

 

Thanks again.

 

Juan.

[Suman Pramanik]

Thank you Juan and Nika