Try OpenEdge Now
skip to main content
SQL Development
Change Data Capture : Use cases : Listing source tables with Change volume

Listing source tables with Change volume

This use case shows how to build a query to get the list of source tables and their corresponding change volume within a certain time interval. This use case can be used with any level of Change Tracking from Level 0 to Level 3. This query can be used to check for a volume of change within a given interval. A source table is added to the results of the query if a minimum of one change is made to the source table during the specified timeframe. The query is as follows:
select f."_owner” as “Owner”, f."_file-name" as "Table", count(*)
from pub."_File" f
inner join pub."_CDC-Table-Policy" tp
on f.rowid = tp."_Source-File-Recid"
and tp."_Policy-Instance" = 0
inner join pub."_Cdc-Change-Tracking" ct
on f."_File-Number" = ct."_Source-Table-Number"
and ct."_Time-Stamp" between ? and ?
-- time range parameters from ETL app.
group by owner, table;