This use case shows how to build a query to get the list of source tables which had changes during a certain interval of time. This use case can be used with any level of Change Tracking from Level 0 to Level 3. This use case can be used to check for changes within a given time interval or without a time interval.
This query uses a subquery to get efficient access to the database. This ensures that the overall query time is not affected by the size of the Change Tracking table if it is very large.
select f."_owner, f."_file-name" as "Table"
from pub."_File" f
inner join pub."_CDC-Table-Policy" tp
on f.rowid = tp."_Source-File-Recid"
and tp."_Policy-Instance" = 0
where 0 <
(select 1
from pub."_Cdc-Change-Tracking" ct
where f."_File-Number" = ct."_Source-Table-Number"
and tp."_Policy-Id" = ct."_Policy-Id"
and ct."_Time-Stamp" between ? and ?
-- time range parameters from ETL app.
offset 0 row fetch next 1 rows only
) ;
Note: Each of the CDC source tables, determined by table Policy and the table’s name in the schema, is checked against the Change Tracking table. If at least one change exists, in the given time frame (if any), then that source table is put into the query’s Result Set.
