SQL Queries - CAST Central Base - Queries on snapshots - How to get the number of SQL tables for a given snapshot

Purpose of Query

 This query result will get the number of SQL tables for a given snapshot.

Applicable CAST Version
Release
Yes/No
8.3.x(tick) 
8.2.x(tick) 
8.1.x(tick)  
8.0.x(tick)  
7.3.x(tick)  
Applicable RDBMS
RDBMS
Yes/No
Oracle Server (tick) 
Microsoft SQL Server (tick) 
CSS2 (tick) 
Query for CSS
Following query will provide the internal as well as external SQL Table for a particular snapshot.


SELECT ds.SNAPSHOT_NAME AS "Snapshot" ,
dot.OBJECT_TYPE_NAME AS "Object Type" ,
COUNT(doi.OBJECT_ID) AS "Objects Count",
CASE
WHEN dli.LINK_TYPE_ID = -3
THEN 'External'
WHEN dli.LINK_TYPE_ID = 3
THEN 'Internal'
ELSE 'Error'
END AS "Status"
FROM DSS_SNAPSHOTS ds
JOIN DSS_OBJECT_INFO doi
ON doi.SNAPSHOT_ID = ds.SNAPSHOT_ID
JOIN DSS_OBJECT_TYPES dot
ON dot.OBJECT_TYPE_ID = doi.OBJECT_TYPE_ID
JOIN DSS_LINK_INFO dli
ON dli.NEXT_OBJECT_ID = doi.OBJECT_ID
AND dli.SNAPSHOT_ID = ds.SNAPSHOT_ID
AND dli.LINK_TYPE_ID IN (-3,
3)
WHERE dot.OBJECT_TYPE_NAME LIKE 'SQL Table%'
AND ds.SNAPSHOT_NAME LIKE '<SNAPSHOT NAME>'
GROUP BY ds.SNAPSHOT_NAME ,
ds.SNAPSHOT_DATE ,
dot.OBJECT_TYPE_NAME,
dli.LINK_TYPE_ID
ORDER BY ds.SNAPSHOT_DATE ,
dot.OBJECT_TYPE_NAME,
dli.LINK_TYPE_ID
 
Here replace <SNAPSHOT NAME> with the snapshot name in which you are interested.
Query result example

 


Query result interpretation
 Here, we do not have any SQL tables for the snapshot
Query for Oracle
TBD
Query result example

Query result interpretation

Query for SQL server
Same as CSS2
Query result example

Query result interpretation

Notes/comments



Related Pages