Finding changed data references during minimal Change tracking

When the table Policy defines change tracking as Level 0 or Level 1, the Change table is not used. Instead, the Change Tracking table identifies the type of change and the row in the source table that was changed.

The following sample query can be used for a simple case of an application table with only one CDC table Policy active. This query finds all the changes known for that source table in the order in which the changes were made.

select ct.* -- specify change tracking fields here.
from pub.”_File” f
inner join
pub."_Cdc-Change-Tracking" ct
on f."_File-Number" = ct."_Source-Table-Number"
where f."_file-name" = ‘customer’
and f."_Owner" = ‘pub’
order by ct.”_Source-Table-Number", ct." _Change-Sequence";
-- sort keys map to index _Sequence-Id

The following example is for a more complex case of any change -tracked application table with multiple CDC table Policies applied to it. This query finds all the changes that were made to the source table in the order in which the changes were made for the policy that is currently active. In this case, the table policy defines that the client sets the table Name parameter and the Owner Name parameter for the table. For improved performance, this query should be performed in two steps. The single step query is as follows:

select ct.* -- specify change tracking fields here.
from pub.”_File” f
inner join
pub."_CDC-Table-Policy" tp
on f.rowid = tp.”_Source-File-Recid”
and tp."_Policy-Instance" = 0 -- Policy is current.
inner join
pub."_Cdc-Change-Tracking" ct
on f."_File-Number" = ct."_Source-Table-Number"
and tp.”_Policy-Id” = ct.”_Policy-Id”
where f."_file-name" = ? -- table name parameter from ETL app.
and f."_Owner" = ? -- table name parameter from ETL app.
order by ct."_Source-Table-Number", ct." _Change-Sequence";

-- Step 1 – get one row with CDC policy id, source table number
select tp."_Policy-Id", f."_File-Number" --for step 2 query.
from pub.”_File” f
inner join
pub."_CDC-Table-Policy" tp
on f.rowid = tp.”_Source-File-Recid”
and tp."_Policy-Instance" = 0 -- Policy is current.
where f."_file-name" = ? -- table name parameter from ETL app.
and f."_Owner" = ? ; -- table name parameter from ETL app.

-- Step 2
select ct.* -- specify change tracking fields here.
from pub."_Cdc-Change-Tracking" ct
where ct."_Source-Table-Number" = ? -- source table number from step 1
and ct."_Policy-Id" = ? -- Table policy id parameter from step 1
order by ct."_Source-Table-Number", ct." _Change-Sequence";
-- maps to index _Sequence-Id