SQL Queries - CAST Central Base - Queries on Objects - How to check if an object is modified between two snapshots due to analyzer
Purpose of Query
This query checks if the object was modified between two snapshots due to the analyzer.
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 obj.object_id AS previous_object_id,
obj.object_full_name AS previous_object_full_name,
obj.object_checksum AS previous_checksum
FROM <Name ofprevious local base>.dss_objects obj
JOIN <Name of current central base>.dss_translation_table tra
ON tra.site_object_id = obj.object_id
JOIN <Name of current central base>.dss_objects dob
ON dob.object_id = tra.object_id
JOIN <Name of current central base>.dss_object_info doi
ON dob.object_id = doi.object_id
JOIN <Name of current central base>.dss_snapshots snp
ON snp.snapshot_id = doi.snapshot_id
WHERE snp.snapshot_name IN ( 'Name of one of the 2 snapshots',
'Name of the second snapshot'
)
AND dob.object_full_name = 'The full name of the object')
previous_run
FULL OUTER JOIN (SELECT obj.object_id AS current_object_id,
obj.object_full_name AS current_object_full_name,
obj.object_checksum AS current_checksum
FROM <Name of current local base>.dss_objects obj
JOIN <Name of current central base>.dss_translation_table tra
ON tra.site_object_id = obj.object_id
JOIN <Name of current central base>.dss_objects dob
ON dob.object_id = tra.object_id
JOIN <Name of current central base>.dss_object_info doi
ON dob.object_id = doi.object_id
JOIN <Name of current central base>.dss_snapshots snp
ON snp.snapshot_id = doi.snapshot_id
WHERE snp.snapshot_name IN ( 'Name of one of the 2 snapshots',
'Name of the second snapshot'
)
AND dob.object_full_name =
'The full name of the object')
current_run
ON previous_run.previous_object_id =
current_run.current_object_id
Query result example
"Computed on 2017"``;``""``PARTICIPATING_JEE.CUSTOMER``""``;231185933
"Computed on 2016"``;``""``PARTICIPATING_JEE.CUSTOMER``""``;0
Query result interpretation
The query returns the snapshot name, object full name and object checksum
Query for Oracle
SELECT *
FROM (SELECT obj.object_id AS previous_object_id,
obj.object_full_name AS previous_object_full_name,
obj.object_checksum AS previous_checksum
FROM <Name ofprevious local base>.dss_objects obj
JOIN <Name of current central base>.dss_translation_table tra
ON tra.site_object_id = obj.object_id
JOIN <Name of current central base>.dss_objects dob
ON dob.object_id = tra.object_id
JOIN <Name of current central base>.dss_object_info doi
ON dob.object_id = doi.object_id
JOIN <Name of current central base>.dss_snapshots snp
ON snp.snapshot_id = doi.snapshot_id
WHERE snp.snapshot_name IN ( 'Name of one of the 2 snapshots',
'Name of the second snapshot'
)
AND dob.object_full_name = 'The full name of the object')
previous_run
FULL OUTER JOIN (SELECT obj.object_id AS current_object_id,
obj.object_full_name AS current_object_full_name,
obj.object_checksum AS current_checksum
FROM <Name of current local base>.dss_objects obj
JOIN <Name of current central base>.dss_translation_table tra
ON tra.site_object_id = obj.object_id
JOIN <Name of current central base>.dss_objects dob
ON dob.object_id = tra.object_id
JOIN <Name of current central base>.dss_object_info doi
ON dob.object_id = doi.object_id
JOIN <Name of current central base>.dss_snapshots snp
ON snp.snapshot_id = doi.snapshot_id
WHERE snp.snapshot_name IN ( 'Name of one of the 2 snapshots',
'Name of the second snapshot'
)
AND dob.object_full_name =
'The full name of the object')
current_run
ON previous_run.previous_object_id =
current_run.current_object_id
Query result example
"Computed on 2017"``;``""``PARTICIPATING_JEE.CUSTOMER``""``;231185933"Computed on 2016"``;``""``PARTICIPATING_JEE.CUSTOMER``""``;0
Query result interpretation
The query returns the snapshot name, object full name and object checksum
Query for SQL server
SELECT *
FROM (SELECT obj.object_id AS previous_object_id,
obj.object_full_name AS previous_object_full_name,
obj.object_checksum AS previous_checksum
FROM <Name ofprevious local base>.dss_objects obj
JOIN <Name of current central base>.dss_translation_table tra
ON tra.site_object_id = obj.object_id
JOIN <Name of current central base>.dss_objects dob
ON dob.object_id = tra.object_id
JOIN <Name of current central base>.dss_object_info doi
ON dob.object_id = doi.object_id
JOIN <Name of current central base>.dss_snapshots snp
ON snp.snapshot_id = doi.snapshot_id
WHERE snp.snapshot_name IN ( 'Name of one of the 2 snapshots',
'Name of the second snapshot'
)
AND dob.object_full_name = 'The full name of the object')
previous_run
FULL OUTER JOIN (SELECT obj.object_id AS current_object_id,
obj.object_full_name AS current_object_full_name,
obj.object_checksum AS current_checksum
FROM <Name of current local base>.dss_objects obj
JOIN <Name of current central base>.dss_translation_table tra
ON tra.site_object_id = obj.object_id
JOIN <Name of current central base>.dss_objects dob
ON dob.object_id = tra.object_id
JOIN <Name of current central base>.dss_object_info doi
ON dob.object_id = doi.object_id
JOIN <Name of current central base>.dss_snapshots snp
ON snp.snapshot_id = doi.snapshot_id
WHERE snp.snapshot_name IN ( 'Name of one of the 2 snapshots',
'Name of the second snapshot'
)
AND dob.object_full_name =
'The full name of the object')
current_run
ON previous_run.previous_object_id =
current_run.current_object_id
Query result example
"Computed on 2017"``;``""``PARTICIPATING_JEE.CUSTOMER``""``;231185933"Computed on 2016"``;``""``PARTICIPATING_JEE.CUSTOMER``""``;0
Query result interpretation
The query returns the snapshot name, object full name and object checksum
Notes/comments
Related Pages