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

Purpose of Query

To get the delta of objects between two snapshots the following query can be run on the central repository.

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   *
FROM      (
                 SELECT doi.snapshot_id AS current_snapshot_id,
                        doi.object_id AS object_id,
                        dob.object_full_name AS object_full_name,
                        t.typdsc AS object_type_description
                 FROM   dss_objects dob
                 JOIN   dss_object_info doi
                 ON     dob.object_id = doi.object_id
                 JOIN typ t
		 on dob.object_type_id = t.idtyp
                 WHERE  doi.snapshot_id = <current snapshot id>
                 AND    doi.object_checksum != 0 ) current_run
FULL JOIN
          (
                 SELECT doi.snapshot_id AS previous_snapshot_id,
                        doi.object_id AS object_id,
                        dob.object_full_name AS object_full_name,
                        t.typdsc AS object_type_description
                 FROM   dss_objects dob
                 JOIN   dss_object_info doi
                 ON     dob.object_id = doi.object_id
		 JOIN typ t
		 on dob.object_type_id = t.idtyp
                 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
or 	  previous_run.object_id is NULL

Query result example

4;477313;“Entities.SalesForce.BrokerObj.SiteType.get”; ““Java Class””;;;""

“;;;“5;77314;“Entities.SalesForce.BrokerObj.SiteType.set”,““Java Class””

Query result interpretation

The query returns snapshot id , object id, object_full name and object type of objects that :

Are part of previous run but not current run
Are part of current run but not previous run

Query for Oracle

SELECT   *
FROM      (
                 SELECT doi.snapshot_id AS current_snapshot_id,
                        doi.object_id AS object_id,
                        dob.object_full_name AS object_full_name,
                        t.typdsc AS object_type_description
                 FROM   dss_objects dob
                 JOIN   dss_object_info doi
                 ON     dob.object_id = doi.object_id
                 JOIN typ t
		 on dob.object_type_id = t.idtyp
                 WHERE  doi.snapshot_id = <current snapshot id>
                 AND    doi.object_checksum != 0 ) current_run
FULL JOIN
          (
                 SELECT doi.snapshot_id AS previous_snapshot_id,
                        doi.object_id AS object_id,
                        dob.object_full_name AS object_full_name,
                        t.typdsc AS object_type_description
                 FROM   dss_objects dob
                 JOIN   dss_object_info doi
                 ON     dob.object_id = doi.object_id
		 JOIN typ t
		 on dob.object_type_id = t.idtyp
                 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
or 	  previous_run.object_id is NULL

Query result example

4;477313;“Entities.SalesForce.BrokerObj.SiteType.get”; ““Java Class””;;;””

“;;;“5;77314;“Entities.SalesForce.BrokerObj.SiteType.set”,““Java Class””

Query result interpretation

The query returns snapshot id , object id, object_full name and object type of objects that :

Are part of previous run but not current run
Are part of current run but not previous run

Query for SQL server

SELECT   *
FROM      (
                 SELECT doi.snapshot_id AS current_snapshot_id,
                        doi.object_id AS object_id,
                        dob.object_full_name AS object_full_name,
                        t.typdsc AS object_type_description
                 FROM   dss_objects dob
                 JOIN   dss_object_info doi
                 ON     dob.object_id = doi.object_id
                 JOIN typ t
		 on dob.object_type_id = t.idtyp
                 WHERE  doi.snapshot_id = <current snapshot id>
                 AND    doi.object_checksum != 0 ) current_run
FULL JOIN
          (
                 SELECT doi.snapshot_id AS previous_snapshot_id,
                        doi.object_id AS object_id,
                        dob.object_full_name AS object_full_name,
                        t.typdsc AS object_type_description
                 FROM   dss_objects dob
                 JOIN   dss_object_info doi
                 ON     dob.object_id = doi.object_id
		 JOIN typ t
		 on dob.object_type_id = t.idtyp
                 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
or 	  previous_run.object_id is NULL

Query result example

4;477313;“Entities.SalesForce.BrokerObj.SiteType.get”; ““Java Class””;;;””

“;;;“5;77314;“Entities.SalesForce.BrokerObj.SiteType.set”,““Java Class””

Query result interpretation

The query returns snapshot id , object id, object_full name and object type of objects that :

Are part of previous run but not current run
Are part of current run but not previous run

Notes/comments

Related Pages