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 (tick)
8.2.x (tick)
8.1.x (tick)
8.0.x (tick)
Applicable RDBMS

 

RDBMS
Yes/No
Oracle Server (tick)
Microsoft SQL Server (tick)
CSS2 (tick)


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 :

  1. Are part of previous run but not current run
  2. 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 :

  1. Are part of previous run but not current run
  2. 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 :

  1. Are part of previous run but not current run
  2. Are part of current run but not previous run
Notes/comments
 

 

Related Pages