Reporter: LAUNCH_WAIT lines for some executions remain in UNI_EVT_RELAUNCHES

Products

• CA Automic Dollar Universe

Issue/Introduction

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.

Cause

When retrieving Launch Wait record corresponding to Terminated jobs, Reporter should consider that relaunch number '0000000' and '9999999' are the same.

Environment

Release : 6.10

Component : DOLLAR UNIVERSE

Subcomponent: Reporter Server

Resolution

Workaround:

Delete duplicated lines (two lines with same launch number for the same node) from UNI_EVT_RELAUNCHES table via SQL statements.

Solution:

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

 

Additional Information

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'