Get change data values for a source table row

When the table Policy defines change tracking as Level 0 or Level 1, the Change table contains change values as determined by the CDC table and Field policy selected by the user. This use case uses the Change table to get actual changed values. This use case assumes that the source table has only one CDC policy applied to it.

The first step gets the source table number for Change Tracking table for efficient access. It verifies whether 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’ ;

After getting the source table number, Step 2 identifies source table rows with changes. In the first section of Step 2, data is grouped so that multiple changes for a given row are reduced down to one row in the Result Set.

select ct."_Source-Table-Number", ct."_Partition-Id", ct."_Recid",      
                                             -- Source table recid, partn id
from   pub."_Cdc-Change-Tracking" ct
where  ct."_Source-Table-Number" = ?              -- from 1st query.
and    ct."_Time-Stamp" between ? and ?  -- time range parameters from ETL 
group by ct."_Source-Table-Number",  ct."_Partition-Id",  ct."_Recid" ;

In the second section of Step 2, the source table RecID can be used at any time to get the current row in the source table for that RecID, if one exists. However, if the row was deleted, it cannot be found. If the source table was partitioned (table Partitioned or Multi-tenant tables), the partition ID is also selected.

select S.*
from my_source_table S
where S.rowid = ? ;          -- recid parameter from ETL app.

After getting the source table number, the Change table name is used in a pre-defined query for Step 3.

Step 3 uses a query which gives a Result Set of the set of change values for each RecID from the results of the query in Step 2, in the order that the changes occurred.

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.
and    ct."_Partition-Id" = ?          -- partition id from 2nd query.
and    ct."_Recid"        = ?          -- recid        from 2nd query. 
and    c."_Operation"    != 3  -- optional - excludes Before Update images.
order by ct.”_Source-Table-Number", ct."_Partition-Id", ct."_Recid",
         ct."_Change-Sequence" ;
       -- Order By keys map to index _Part-Rec-Id and should skip sorting.

These changes could be Insert, Delete, or Update operations and the application logic can decide how to transfer each change to the Data Warehouse. Step 1 and Step 2 of the query can be embedded inside an application in nested loops as shown in the following table:

do
      query #1
      exitloop if no data
      do 
        query #2
        exitloop if no data
        accumulate type of changes, aggregate changes, to Source row
      enddo data found
      send changed Source row values to Data Warehouse.
enddo