How to delete unused ForecastingShell registrations from the Admin Interface status

Delft-FEWS 2018.02

Apply to all Master-Controller databases in the MC pool.

Oracle

SELECT * FROM ForecastingShells WHERE SYSTIMESTAMP AT TIME ZONE 'GMT' - modificationtime > NUMTODSINTERVAL(60,'MINUTE') AND globalRowId NOT IN (SELECT fssRowId FROM FssStatus fst WHERE SYSTIMESTAMP AT TIME ZONE 'GMT' - modificationtime <NUMTODSINTERVAL(60,'MINUTE'));
DELETE FROM ForecastingShells WHERE SYSTIMESTAMP AT TIME ZONE 'GMT' - modificationtime > NUMTODSINTERVAL(60,'MINUTE') AND globalRowId NOT IN (SELECT fssRowId FROM FssStatus fst WHERE SYSTIMESTAMP AT TIME ZONE 'GMT' - modificationtime <NUMTODSINTERVAL(60,'MINUTE'));
SELECT * FROM FssGroups WHERE globalRowId IN (SELECT t1.globalRowId FROM (SELECT mcId, globalRowId, entryId FROM FssGroups) t1 LEFT JOIN (SELECT fssGroupEntryid, mcId FROM ForecastingShells) t2  ON t1.mcId=t2.mcId AND t2.fssGroupEntryid=t1.entryId WHERE t2.mcId IS NULL);
DELETE FROM FssGroups WHERE globalRowId IN (SELECT t1.globalRowId FROM (SELECT mcId, globalRowId, entryId FROM FssGroups) t1 LEFT JOIN (SELECT fssGroupEntryid, mcId FROM ForecastingShells) t2  ON t1.mcId=t2.mcId AND t2.fssGroupEntryid=t1.entryId WHERE t2.mcId IS NULL);

Postgres

SELECT * FROM ForecastingShells WHERE modificationTime < current_timestamp AT TIME ZONE 'GMT' - interval '60 minutes' AND globalRowId NOT IN (SELECT fssRowId FROM FssStatus fst WHERE fst.modificationTime > current_timestamp AT TIME ZONE 'GMT' - interval '60 minutes');
DELETE   FROM ForecastingShells WHERE modificationTime < current_timestamp AT TIME ZONE 'GMT' - interval '60 minutes' AND globalRowId NOT IN (SELECT fssRowId FROM FssStatus fst WHERE fst.modificationTime > current_timestamp AT TIME ZONE 'GMT' - interval '60 minutes');
SELECT * FROM FssGroups WHERE globalRowId IN (SELECT t1.globalRowId FROM (SELECT mcId, globalRowId, entryId FROM FssGroups) t1 LEFT JOIN (SELECT fssGroupEntryid, mcId FROM ForecastingShells) t2  ON t1.mcId=t2.mcId AND t2.fssGroupEntryid=t1.entryId WHERE t2.mcId IS NULL);
DELETE FROM FssGroups WHERE globalRowId IN (SELECT t1.globalRowId FROM (SELECT mcId, globalRowId, entryId FROM FssGroups) t1 LEFT JOIN (SELECT fssGroupEntryid, mcId FROM ForecastingShells) t2  ON t1.mcId=t2.mcId AND t2.fssGroupEntryid=t1.entryId WHERE t2.mcId IS NULL);

MS SQL Server

SELECT * FROM ForecastingShells WHERE DATEDIFF(minute, modificationTime, GETUTCDATE()) > 60 AND globalRowId NOT IN (SELECT fssRowId FROM FssStatus fst WHERE DATEDIFF(minute, modificationTime, GETUTCDATE()) < 60);
DELETE   FROM ForecastingShells WHERE DATEDIFF(minute, modificationTime, GETUTCDATE()) > 60 AND globalRowId NOT IN (SELECT fssRowId FROM FssStatus fst WHERE DATEDIFF(minute, modificationTime, GETUTCDATE()) < 60);
SELECT * FROM FssGroups WHERE globalRowId IN (SELECT t1.globalRowId FROM (SELECT mcId, globalRowId, entryId FROM FssGroups) t1 LEFT JOIN (SELECT fssGroupEntryid, mcId FROM ForecastingShells) t2  ON t1.mcId=t2.mcId AND t2.fssGroupEntryid=t1.entryId WHERE t2.mcId IS NULL);
DELETE FROM FssGroups WHERE globalRowId IN (SELECT t1.globalRowId FROM (SELECT mcId, globalRowId, entryId FROM FssGroups) t1 LEFT JOIN (SELECT fssGroupEntryid, mcId FROM ForecastingShells) t2  ON t1.mcId=t2.mcId AND t2.fssGroupEntryid=t1.entryId WHERE t2.mcId IS NULL);

Delft-FEWS 2017.02

Steps (fill in the correct <mcId> and <fssId> and apply to all Master-Controller databases in the MC pool)

Select the Forecasting Shell registrations to remove, by running the following sql statement: SELECT * FROM ForecastingShells WHERE mcId='<mcId>' AND fssId='<fssId>';
DELETE FROM ForecastingShells WHERE mcId='<mcId>' AND fssId='<fssId>';
SELECT * FROM LogCollectorServices
DELETE FROM LogCollectorServices WHERE mcId='<mcId>' AND id='<fssId>' AND type='MCP';

Forecasting Shells in Delft-FEWS 2017.02 are registered in the ForecastingShells table. In the 2017.02 Admin Interface there is no longer functionality to unregister old and unused ForecastingShells, since this would cause synchronization or primary key violation ERRORS. In order to delete ForecastingShells registrations that are no longer used, it is only possibly via SQL. In a multi Master-Controller environment, it is only legal to remove records if the deletion is applied to all Master-Controller databases. When deleting unused ForecastingShells on a multi Master-Controller environment it is important to temporary stop the MC-MC synchronisation on all connected Master Controllers.

Page tree