Movehistorydata job not moving records to Stage tables

Document ID : KB000103634
Last Modified Date : 28/06/2018
Show Technical Document Details
Issue:
The DE Server is experiencing slow response after the Movehistorydata job stopped working and no records were moved to stage tables.
Environment:
CA Workload Automation DE 11.3 SP3, r12.0.00, r12.0.01, r12.0.02
Cause:
The Movehistorydata job was trying to move record from ESP_APPPLICATION table to H_APPLICATION stage table, having a combination of the same JOB_NAME, APPL_NAME and APPL_GEN_NO in both table as this constraint has been defined to have a unique key with the combination of these three fields. The job failed with SQL Server Exception 'Cannot insert duplicate key row in object';

com.ca.wa.core.engine.rdbms.DatabaseException: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.H_APPLICATION' with unique index 'XAK1H_APPLICATION'.
Resolution:
Follow the procedure below;

1. Query both the ESP_APPLICATION and H_APPLICATION tables for the duplicate key;

select count(*) from esp_application e, h_application h where e.APPL_NAME = h.APPL_NAME and e.JOB_NAME=h.JOB_NAME and e.APPL_GEN_NO = h.APPL_GEN_NO;

2. If the query returns a value more than 0, then we need to execute the below SQL statement to remove the troubled records from the H_APPLICATION table;

delete from H_APPLICATION h where h.APPL_ID in (select h.APPL_ID from esp_application e, h_application h where e.APPL_NAME = h.APPL_NAME and e.JOB_NAME = h.JOB_NAME and e.APPL_GEN_NO = h.APPL_GEN_NO);

Notes - Backup the database before running the delete statement.