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.
The issue is due to some error or crash that may have happened at DE or at database. This can sometimes cause inconsistent data.
CA Workload Automation DE 11.3/R12.1/R12.2
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
Note: The deletestagingdata command will delete the data from H_ tables. It is recommended to backup the database before running the command.