Movehistorydata job not moving records to Stage tables

Products

• DSERIES- SERVER
• ESP dSeries Workload Automation
• ESP dSeries Workload Automation - Scheduler (dSeries)

Issue/Introduction

The DE Server is experiencing slow response after the Movehistorydata job stopped working and no records were moved to stage tables.

Environment

Workload Automation DE

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.