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

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.

 

Environment

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

Database: Any

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.

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.