CA WA DE MOVESHISTORYDATA is not moving data to H_ tables

Products

• DSERIES- SERVER
• CA Workload Automation DE - System Agent (dSeries)

Issue/Introduction

When MOVEHISTORYDATA command is issued, the H_ tables (archive) rows don't change.  No new data gets moved from ESP_ to H_ tables.

Environment

CA Workload Automation DE
Database: Any
OS: Any

Cause

The MOVEHISTORYDATA data command will move data from history (ESP_APPLICATION, ESP_GENERIC_JOB etc.) to archive tables (H_).
If a duplicate record is found when moving data the move will fail.  Duplicate records cannot be inserted in H_ tables.  All the records in H_ tables must be unique.  
Duplicate records may occur if there was some kind of application generation reset or failure of data move at the database side.  The tracelog will show the following error when duplicate entries are found.

2018xxxx 20:47:33.955 [essential] [INFO] asyncMoveHistoryDataToStageTable: [2018-xx-xx_20:47:33.955] move data olrder than Sun xxx xx xx:47:33 EDT 2018 start.
20180918 20:47:33.963 [essential] [ERROR] asyncMoveHistoryDataToStageTable: [2018-xx-xx_20:47:33.963] move data olrder than Sun xxx xx xx:47:33 EDT 2018 fail.
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'. 
The duplicate key value is (SOME_JOB_1234~~, MY_APPLICATION_1234, 1000).
	at com.ca.wa.core.engine.maintenance.ApplicationDataMover.moveData(ApplicationDataMover.java:145)
	at com.ca.wa.core.engine.maintenance.MoveHistoryDataUtil$1.run(MoveHistoryDataUtil.java:78)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Cannot insert duplicate key row in object 'dbo.H_APPLICATION' 
with unique index 'XAK1H_APPLICATION'. 
The duplicate key value is (SOME_JOB_1234~~, MY_APPLICATION_1234, 1000).
	at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:217)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1655)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:440)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:385)
	at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7505)
	at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2445)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:191)
	at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:166)
	at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:328)
	at com.ca.wa.core.engine.rdbms.PreparedStatementWrapper.executeUpdate(PreparedStatementWrapper.java:299)
	at com.ca.wa.core.engine.maintenance.ApplicationDataMover.moveApplicationById(ApplicationDataMover.java:176)
	at com.ca.wa.core.engine.maintenance.ApplicationDataMover.moveData(ApplicationDataMover.java:123)
	... 2 more

 

Resolution

The duplicate entries must be removed in the H_ tales for the move to complete.  The following SQL query will show the number of duplicate entries.
 

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;

If the above shows count of more than zero, use the following DELETE statement to remove the duplicate entries.
 

DELETE FROM  H_APPLICATION h1 WHERE h1.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);

 

Additional Information

Note: Always backup the database before deleting any data.  Also, it is recommended to shutdown DE when running the DELETE statement.