SQL Queries - CAST Measure Base - Checking and removing corruptions - Snapshot containing quality rules with zero total check

 This query lists the snapshots containing Quality rules with a total check equal to zero.

Get the snapshot ID and count of QR

select s.SNAPSHOT_ID as Snapshot , count(1) as count
  from DSS_SNAPSHOTS s 
 where exists ( 
  select dmr.SNAPSHOT_ID from DSS_METRIC_RESULTS dmr 
    where dmr.SNAPSHOT_ID = s.SNAPSHOT_ID 
    and dmr.METRIC_VALUE_INDEX = 2 
    and dmr.METRIC_NUM_VALUE = 0 
    and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
    and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
      ) 
  and s.CONSOLIDATION_MODE = -1
group by s.SNAPSHOT_ID 

--------   ------
1          2
2          2

Correct the inconsistencies

Execute the below queries in the same order for each snapshot ID that is inconsistent in an increasing order. This means if you have snapshots 1 , 2 and 3 that are inconsistent, then you have to execute Query 1 , Query 2 and Query 3 for snapshot ID 1 followed by 2 and then 3.

1. Query 1

   select DSS_PROPAGATE_RESULTS_TO_APP(<snapshot_ID>);

2. Query 2

   delete
   from DSS_METRIC_RESULTS
   where (SNAPSHOT_ID, OBJECT_ID, METRIC_ID) in (select dmr.SNAPSHOT_ID, dmr.object_id, dmr.metric_id
         from DSS_METRIC_RESULTS dmr 
         where dmr.METRIC_VALUE_INDEX = 2 
           and dmr.METRIC_NUM_VALUE = 0 
           and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
           and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
           )

3. Query 3

   select DSS_CLEANUP_COMPUTE_METRIC( <snapshot_ID> );

Get the snapshot ID and count of QR

select s.SNAPSHOT_ID as Snapshot , count(1) as count
  from DSS_SNAPSHOTS s 
 where exists ( 
  select dmr.SNAPSHOT_ID from DSS_METRIC_RESULTS dmr 
    where dmr.SNAPSHOT_ID = s.SNAPSHOT_ID 
    and dmr.METRIC_VALUE_INDEX = 2 
    and dmr.METRIC_NUM_VALUE = 0 
    and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
    and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
      ) 
  and s.CONSOLIDATION_MODE = -1
group by s.SNAPSHOT_ID 

--------   ------
1          2
2          2

Correct the inconsistencies

Execute the below queries in the same order for each snapshot ID that is inconsistent in an increasing order. This means if you have snapshots 1 , 2 and 3 that are inconsistent, then you have to execute Query1 , Query2 and Query3 for snapshotID 1 followed by 2 and then 3.

1. Query 1

   select DSS_PROPAGATE_RESULTS_TO_APP(<snapshot_ID>);

2. Query 2

   delete
   from DSS_METRIC_RESULTS
   where (SNAPSHOT_ID, OBJECT_ID, METRIC_ID) in (select dmr.SNAPSHOT_ID, dmr.object_id, dmr.metric_id
         from DSS_METRIC_RESULTS dmr 
         where dmr.METRIC_VALUE_INDEX = 2 
           and dmr.METRIC_NUM_VALUE = 0 
           and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
           and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
           )

3. Query 3

   select DSS_CLEANUP_COMPUTE_METRIC( <snapshot_ID> );

Get the snapshot ID and count of QR

select s.SNAPSHOT_ID as Snapshot , count(1) as count
  from DSS_SNAPSHOTS s 
 where exists ( 
  select dmr.SNAPSHOT_ID from DSS_METRIC_RESULTS dmr 
    where dmr.SNAPSHOT_ID = s.SNAPSHOT_ID 
    and dmr.METRIC_VALUE_INDEX = 2 
    and dmr.METRIC_NUM_VALUE = 0 
    and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
    and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
      ) 
  and s.CONSOLIDATION_MODE = -1
group by s.SNAPSHOT_ID 

--------   ------
1          2
2          2

Correct the inconsistencies

Execute the below queries in the same order for each snapshot ID that is inconsistent in an increasing order. This means if you have snapshots 1 , 2 and 3 that are inconsistent, then you have to execute Query 1 , Query 2 and Query 3 for snapshot ID 1 followed by 2 and then 3.

1. Query 1

   select DSS_PROPAGATE_RESULTS_TO_APP(<snapshot_ID>);

2. Query 2

   delete
   from DSS_METRIC_RESULTS
   where (SNAPSHOT_ID, OBJECT_ID, METRIC_ID) in (select dmr.SNAPSHOT_ID, dmr.object_id, dmr.metric_id
         from DSS_METRIC_RESULTS dmr 
         where dmr.METRIC_VALUE_INDEX = 2 
           and dmr.METRIC_NUM_VALUE = 0 
           and dmr.METRIC_ID in (select dmt.METRIC_ID from DSS_METRIC_TYPES dmt where dmt.METRIC_GROUP = 1) 
           and dmr.OBJECT_ID in (select dos.OBJECT_ID from DSS_OBJECTS dos where dos.OBJECT_TYPE_ID = -102) 
           )

3. Query 3

   select DSS_CLEANUP_COMPUTE_METRIC( <snapshot_ID> );