Randomly, for some Jobs that have been extracted in status Launch Wait a while ago, the Launch Wait line remains in Reporter Table UNI_EVT_RELAUNCHES which is not normal for jobs that did complete with a status Completed / Aborted.
We can find two lines with the same LAUNCH_NUM and DATASOURCE_ID on Reporter Database for the same execution, one with status Launch Wait and one with a final Status like Completed/Aborted and RELAUNCH_NUM=9999999.
When retrieving Launch Wait record corresponding to Terminated jobs, Reporter should consider that relaunch number '0000000' and '9999999' are the same.
Release : 6.10
Component : DOLLAR UNIVERSE
Subcomponent: Reporter Server
Delete duplicated lines (two lines with same launch number for the same node) from UNI_EVT_RELAUNCHES table via SQL statements.
Update to a fix version listed below or a newer version if available.
Fix version(s):
Component: Reporter.Server
Dollar Universe 6.10.51 - Released 28th October 2020
Useful queries for MS SQL Server:
1. SQL request to see only duplicated launches that are not "normal" : those are not linked with Overrun or Manual Intervention
SELECT r3.LAUNCH_NUM, r3.DATASOURCE_ID, r3.RELAUNCH_NUM, r3.FINAL_STATUS, r3.FINAL_EXECUTION_NUM, r3.PROCESSING_DATE, r3.*
from UNI_EVT_RELAUNCHES r3
JOIN (
SELECT r1.LAUNCH_NUM as num_launch, r1.DATASOURCE_ID as resssource_id from UNI_EVT_RELAUNCHES r1
INNER JOIN
(
SELECT COUNT(*) AS nbr_doublon, LAUNCH_NUM as num, DATASOURCE_ID, PROCESSING_DATE
FROM UNI_EVT_RELAUNCHES
WHERE FINAL_STATUS != 'I' and FINAL_STATUS != 'O'
---and PROCESSING_DATE > '20200902'
GROUP BY LAUNCH_NUM, DATASOURCE_ID, PROCESSING_DATE
HAVING COUNT(*) > 1
) r2 ON (r1.LAUNCH_NUM = r2.num and r1.DATASOURCE_ID = r2.DATASOURCE_ID)
where r1.FINAL_STATUS = 'T' and (r1.RELAUNCH_NUM = 9999999 or r1.RELAUNCH_NUM = 0)
) r4 ON (r3.LAUNCH_NUM = num_launch and r3.DATASOURCE_ID = resssource_id)
WHERE r3.FINAL_STATUS = 'L'
ORDER BY r3.PROCESSING_DATE, r3.LAUNCH_NUM, r3.DATASOURCE_ID
2. Query to delete those duplicated elements from the Database (always test it first)
Unset comment on --and PROCESSING_DATE > '20200902' with correct date
DELETE r3
from UNI_EVT_RELAUNCHES r3
JOIN (
SELECT r1.LAUNCH_NUM as num_launch, r1.DATASOURCE_ID as resssource_id from UNI_EVT_RELAUNCHES r1
INNER JOIN
(
SELECT COUNT(*) AS nbr_doublon, LAUNCH_NUM as num, DATASOURCE_ID, PROCESSING_DATE
FROM UNI_EVT_RELAUNCHES
WHERE FINAL_STATUS != 'I' and FINAL_STATUS != 'O'
and PROCESSING_DATE > '20200830'
GROUP BY LAUNCH_NUM, DATASOURCE_ID, PROCESSING_DATE
HAVING COUNT(*) > 1
) r2 ON (r1.LAUNCH_NUM = r2.num and r1.DATASOURCE_ID = r2.DATASOURCE_ID)
where r1.FINAL_STATUS = 'T' and (r1.RELAUNCH_NUM = 9999999 or r1.RELAUNCH_NUM = 0)
) r4 ON (r3.LAUNCH_NUM = num_launch and r3.DATASOURCE_ID = resssource_id)
WHERE r3.FINAL_STATUS = 'L'