Purpose of Query
To check if the objects technically Deleted has the technical status "Deleted". The following query can be run on the central repository.
Official documentation
- CAST AIP 8.3 : TCC - Enhancement node - Right hand panel
- CAST AIP 8.2 : TCC - Enhancement node - Right hand panel
- CAST AIP 8.1 : TCC - EFP Calibration node - Right hand panel
- CAST AIP 8.0 : TCC - EFP Calibration node - Right hand panel
Applicable CAST Version
Release | Yes/No |
---|---|
8.3.x | |
8.2.x | |
8.1.x | |
8.0.x |
Applicable RDBMS
RDBMS | Yes/No |
---|---|
Oracle Server | |
Microsoft SQL Server | |
CSS2 |
Query for CSS
-- Metric level SELECT a.snapshot_id, a.object_id, CASE a.metric_id WHEN 10351 THEN 'ADDED' --3751 ms. WHEN 10352 THEN 'DELETED' WHEN 10353 THEN 'UPDATED' WHEN 10354 THEN 'UNCHANGED' END AS STATUS FROM dss_metric_results a JOIN dss_objects o ON o.object_id = a.object_id AND o.object_type_id != -102 JOIN dss_snapshots s ON s.snapshot_id = a.snapshot_id WHERE a.metric_id IN ( 10351, 10352, 10353, 10354 ) AND s.snapshot_status = 2 AND s.enhancement_measure = 'AEP' AND a.metric_value_index = 1 AND a.snapshot_id = <current snapshot id> -- except functional AND NOT EXISTS (SELECT 1 FROM efp_tran_info t JOIN dss_link_info l ON l.previous_object_id = t.object_id WHERE ( ( a.metric_id = 10352 AND l.snapshot_id = Dss_get_previous_snapshot (a.snapshot_id) ) OR ( a.metric_id != 10352 AND l.snapshot_id = a.snapshot_id ) ) AND l.link_type_id BETWEEN 11002 AND 11006 AND t.snapshot_id = a.snapshot_id AND t.status IS NOT NULL AND a.object_id = l.next_object_id AND NOT EXISTS (SELECT 1 FROM efp_tran_exclusion E WHERE E.object_id = a.object_id)) AND a.object_id IN ( -- Pure Delta SELECT previous_run.object_id FROM (SELECT doi.snapshot_id, doi.object_id, dob.object_full_name FROM dss_objects dob JOIN dss_object_info doi ON dob.object_id = doi.object_id WHERE doi.snapshot_id = <current snapshot id> AND doi.object_checksum != 0) current_run RIGHT JOIN (SELECT doi.snapshot_id, doi.object_id, dob.object_full_name FROM dss_objects dob JOIN dss_object_info doi ON dob.object_id = doi.object_id WHERE doi.snapshot_id = <previous snapshot id> AND doi.object_checksum != 0) previous_run ON current_run.object_id = previous_run.object_id WHERE current_run.object_id IS NULL) ORDER BY status
Query result example
-- 5;2550;"DELETED"
Query result interpretation
The query returns the id of the snapshot from where the object is deleted, the object id and the object status
Query for Oracle
-- Metric level SELECT a.snapshot_id, a.object_id, CASE a.metric_id WHEN 10351 THEN 'ADDED' --3751 ms. WHEN 10352 THEN 'DELETED' WHEN 10353 THEN 'UPDATED' WHEN 10354 THEN 'UNCHANGED' END AS STATUS FROM dss_metric_results a JOIN dss_objects o ON o.object_id = a.object_id AND o.object_type_id != -102 JOIN dss_snapshots s ON s.snapshot_id = a.snapshot_id WHERE a.metric_id IN ( 10351, 10352, 10353, 10354 ) AND s.snapshot_status = 2 AND s.enhancement_measure = 'AEP' AND a.metric_value_index = 1 AND a.snapshot_id = <current snapshot id> -- except functional AND NOT EXISTS (SELECT 1 FROM efp_tran_info t JOIN dss_link_info l ON l.previous_object_id = t.object_id WHERE ( ( a.metric_id = 10352 AND l.snapshot_id = Dss_get_previous_snapshot (a.snapshot_id) ) OR ( a.metric_id != 10352 AND l.snapshot_id = a.snapshot_id ) ) AND l.link_type_id BETWEEN 11002 AND 11006 AND t.snapshot_id = a.snapshot_id AND t.status IS NOT NULL AND a.object_id = l.next_object_id AND NOT EXISTS (SELECT 1 FROM efp_tran_exclusion E WHERE E.object_id = a.object_id)) AND a.object_id IN ( -- Pure Delta SELECT previous_run.object_id FROM (SELECT doi.snapshot_id, doi.object_id, dob.object_full_name FROM dss_objects dob JOIN dss_object_info doi ON dob.object_id = doi.object_id WHERE doi.snapshot_id = <current snapshot id> AND doi.object_checksum != 0) current_run RIGHT JOIN (SELECT doi.snapshot_id, doi.object_id, dob.object_full_name FROM dss_objects dob JOIN dss_object_info doi ON dob.object_id = doi.object_id WHERE doi.snapshot_id = <previous snapshot id> AND doi.object_checksum != 0) previous_run ON current_run.object_id = previous_run.object_id WHERE current_run.object_id IS NULL) ORDER BY status
Query result example
-- 5;2550;"DELETED"
Query result interpretation
The query returns the id of the snapshot from where the object is deleted, the object id and the object status
Query for SQL server
-- Metric level SELECT a.snapshot_id, a.object_id, CASE a.metric_id WHEN 10351 THEN 'ADDED' --3751 ms. WHEN 10352 THEN 'DELETED' WHEN 10353 THEN 'UPDATED' WHEN 10354 THEN 'UNCHANGED' END AS STATUS FROM dss_metric_results a JOIN dss_objects o ON o.object_id = a.object_id AND o.object_type_id != -102 JOIN dss_snapshots s ON s.snapshot_id = a.snapshot_id WHERE a.metric_id IN ( 10351, 10352, 10353, 10354 ) AND s.snapshot_status = 2 AND s.enhancement_measure = 'AEP' AND a.metric_value_index = 1 AND a.snapshot_id = <current snapshot id> -- except functional AND NOT EXISTS (SELECT 1 FROM efp_tran_info t JOIN dss_link_info l ON l.previous_object_id = t.object_id WHERE ( ( a.metric_id = 10352 AND l.snapshot_id = Dss_get_previous_snapshot (a.snapshot_id) ) OR ( a.metric_id != 10352 AND l.snapshot_id = a.snapshot_id ) ) AND l.link_type_id BETWEEN 11002 AND 11006 AND t.snapshot_id = a.snapshot_id AND t.status IS NOT NULL AND a.object_id = l.next_object_id AND NOT EXISTS (SELECT 1 FROM efp_tran_exclusion E WHERE E.object_id = a.object_id)) AND a.object_id IN ( -- Pure Delta SELECT previous_run.object_id FROM (SELECT doi.snapshot_id, doi.object_id, dob.object_full_name FROM dss_objects dob JOIN dss_object_info doi ON dob.object_id = doi.object_id WHERE doi.snapshot_id = <current snapshot id> AND doi.object_checksum != 0) current_run RIGHT JOIN (SELECT doi.snapshot_id, doi.object_id, dob.object_full_name FROM dss_objects dob JOIN dss_object_info doi ON dob.object_id = doi.object_id WHERE doi.snapshot_id = <previous snapshot id> AND doi.object_checksum != 0) previous_run ON current_run.object_id = previous_run.object_id WHERE current_run.object_id IS NULL) ORDER BY status
Query result example
-- 5;2550;"DELETED"
Query result interpretation
The query returns the id of the snapshot from where the object is deleted, the object id and the object status
Notes/comments
Related Pages