SQL Queries - CAST Central Base - Queries on Objects - How to get the list of modified objects between two snapshots

Purpose of Query

The following query can be run on the central repository, It will provide you with the list of modified objects between two snapshots.

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

SELECT object_full_name, 
       current_checksum, 
       previous_checksum 
FROM   (SELECT doi.object_id       AS current_object_id, 
               dso.object_full_name, 
               doi.object_checksum AS current_checksum 
        FROM   dss_object_info doi 
               JOIN dss_objects dso 
                 ON doi.object_id = dso.object_id 
        WHERE  snapshot_id = <current snapshot_id>)current_snapshot 
       JOIN (SELECT doi.object_id       AS previous_object_id, 
                    doi.object_checksum AS previous_checksum 
             FROM   dss_object_info doi 
                    JOIN dss_objects dso 
                      ON doi.object_id = dso.object_id 
             WHERE  snapshot_id = <previous snapshot_id>)previous_snapshot 
         ON previous_snapshot.previous_object_id = 
            current_snapshot.current_object_id 
WHERE  previous_snapshot.previous_checksum != current_snapshot.current_checksum

Query result example

“FULLNAME”;2004672353;1337449206

Query result interpretation

The objects returns the object full names, the checksum of the objects in the previous snapshot and the checksum of the object in the current snapshot

Query for Oracle

SELECT object_full_name, 
       current_checksum, 
       previous_checksum 
FROM   (SELECT doi.object_id       AS current_object_id, 
               dso.object_full_name, 
               doi.object_checksum AS current_checksum 
        FROM   dss_object_info doi 
               JOIN dss_objects dso 
                 ON doi.object_id = dso.object_id 
        WHERE  snapshot_id = <current snapshot_id>)current_snapshot 
       JOIN (SELECT doi.object_id       AS previous_object_id, 
                    doi.object_checksum AS previous_checksum 
             FROM   dss_object_info doi 
                    JOIN dss_objects dso 
                      ON doi.object_id = dso.object_id 
             WHERE  snapshot_id = <previous snapshot_id>)previous_snapshot 
         ON previous_snapshot.previous_object_id = 
            current_snapshot.current_object_id 
WHERE  previous_snapshot.previous_checksum != current_snapshot.current_checksum

Query result example

“FULLNAME”;2004672353;1337449206

Query result interpretation

The objects returns the object full names, the checksum of the objects in the previous snapshot and the checksum of the object in the current snapshot

Query for SQL server

SELECT object_full_name, 
       current_checksum, 
       previous_checksum 
FROM   (SELECT doi.object_id       AS current_object_id, 
               dso.object_full_name, 
               doi.object_checksum AS current_checksum 
        FROM   dss_object_info doi 
               JOIN dss_objects dso 
                 ON doi.object_id = dso.object_id 
        WHERE  snapshot_id = <current snapshot_id>)current_snapshot 
       JOIN (SELECT doi.object_id       AS previous_object_id, 
                    doi.object_checksum AS previous_checksum 
             FROM   dss_object_info doi 
                    JOIN dss_objects dso 
                      ON doi.object_id = dso.object_id 
             WHERE  snapshot_id = <previous snapshot_id>)previous_snapshot 
         ON previous_snapshot.previous_object_id = 
            current_snapshot.current_object_id 
WHERE  previous_snapshot.previous_checksum != current_snapshot.current_checksum

Query result example

“FULLNAME”;2004672353;1337449206

Query result interpretation

The objects returns the object full names, the checksum of the objects in the previous snapshot and the checksum of the object in the current snapshot

Notes/comments

Related Pages