CA WA DE : MOVEHISTORYDATA cli command -- unique key violation error

book

Article ID: 143241

calendar_today

Updated On:

Products

CA Workload Automation DE

Issue/Introduction

The data on completed jobs may not get archived due to "unique key constraint" error.  This will stop the MOVEHISTORYDATA command and will not move any further data in H_ tables.

2020XXXX XX:09:32.670 [essential] [ERROR] asyncMoveHistoryDataToStageTable: [2020-XX-XX_XX:09:32.670] Exception caught while inserting the application id: 200011 during moveApplicationById command because of :ERROR: duplicate key value violates unique constraint "xak1h_application"

  Detail: Key (job_name, appl_name, appl_gen_no)=(ZZZ~~, ZZZ, 1) already exists.

 

Cause

The issue is due to some error or crash that may have happened at DE or at database.  This can sometimes cause inconsistent data.

Environment

CA Workload Automation DE 11.3/R12.1/R12.2

Database: Any

Resolution

The following is a workaround for R12.2 and above only.

1. From the cli, run the command – deletestagingdata

Example: deletestagingdata olderthan("now less 2 months") batchsize(5000)

The command removes the to remove the records from the H_ tables for the time frame specified in the command.  As shown in example, the command will remove data for anything older than 2 months.  Specify the time period as per the user requirements.

2. Once staging data has been removed, run movehistorydata and check if the data is getting moved out from ESP_APPLICATION to H_APPLICATION

 

 

Additional Information

Note:  The deletestagingdata command will delete the data from H_ tables.  It is recommended to backup the database before running the command.