When the table Policy defines change tracking as Level 2 or Level 3, the Change table contains change values as determined by the CDC table and Field policy selected by the user. This Use Case gets all the changes that exist in the Change Tracking table for a given source table. In this Use Case, the change data values are accessed in 2 steps, first to get the source table number, and second to get the changes from the Change table. The changes are then examined by the application and appropriate action is taken.
Step 1 gets the source table number for Change Tracking table for efficient access. It verifies that Change tracking is active for source table by checking for an active CDC table Policy for the source table.
select f."_File-Number"
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" = ‘customer’
and f."_Owner" = ‘pub’ ;
Note: This step can be skipped if the source table number has been already obtained by the application numerous times.
Step 2 uses the following query to get a Result Set of change values, as needed by the application.
select c.* -- specify Change table fields here.
from pub."_Cdc-Change-Tracking" ct
inner join
pub.CDC_Customer c -- well-known Change table name.
on ct."_Change-Sequence" = c."_Change-Sequence"
where ct.”_Source-Table-Number” = ? -- from 1st query.
order by ct."_Source-Table-Number", ct."_Change-Sequence";
--ORDER BY keys map to index _Sequence-Id and should skip sorting.
