This use case is applicable when Level 3 change tracking is in effect. Every UPDATE action tracks the data Before the UPDATE, and After the Update. This tracking happens both Before and After produce Change table data rows. The ETL application uses Before and After data to aggregate change values to be propagated to the Data Warehouse.
Step 1 fetches the source table number for efficient access to the Change Tracking table. 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.
Step 2 aggregates the updated numeric information for eventual transmission to the Data Warehouse.
select sum( case c."_Operation" when 3 then -c.Quantity
when 4 then +c.Quantity else null end)
as c_difference, -- Quantity - assumed Source table column.
min(c.itemnum) -- assumed Source table column.
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.
group by ct."_Source-Table-Number", ct."_Change-Sequence"
order by ct."_Source-Table-Number", ct."_Change-Sequence";
-- Group By, Order By keys map to index _Sequence-Id
Note: In the Change table, a given change sequence value has at most 2 Change table rows (before and after the Update).
In this use case, the ETL application would save Change values from this query. The query produces one row for each of the UPDATE, DELETE, and INSERT operations by design of the Group By clause.
For example, if the Data Warehouse contains a table Item_Chg that tracks fluctuations in inventories from the Item table in the source database, the high-level logic of the application may look like this:
Execute query #2
for each Result Set row R:
Let tmp_cdiff := R.c_difference
Let tmp_itmn := R.c_itemnum
Execute sql update to Data Warehouse:
UPDATE Item_Chg
SET ic_diff = ? -- parameter from tmp_cdiff
ic_item = ? -- parameter from tmp_itmn
WHERE ……
end for
