CA WA DE MOVESHISTORYDATA is not moving data to H_ tables

Document ID : KB000115381
Last Modified Date : 19/09/2018
Show Technical Document Details
Issue:
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 ANE 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 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);



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