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’ ;
Note: This step can be skipped if the source table number has been already obtained by the application numerous times.
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
