Get change data values driven by a source table row

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. In this use case, the application is looking for any possible changes for an identified source table row R. The application identifies the source table row R using application-specific criteria. Any changes that exist for that specific source table row R are found in the CDC tables, and then handled for row R. This is done in 3 steps.

In Step 1, the source table row R is identified using the RowID of row R. In this step, the details that are fetched are the RowID (RecID) and any partition ID of the source table row R. The partition ID information is needed if the source table is either Multi-tenant or if it is table Partitioned.

select  rowid,  pro_get_partid(pro_partn_rowid)
from    my_source_table S
where   my_key1   = <some value> 
and     my_key2   = <some value>
and     my_column = <some value>;
-- Result Set values are saved for use in query below.

Step 2 on the CDC tables gets the source table number. 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’ ;

Step 3 uses a query which gives a Result Set of the set of change values for each source table row R from the preliminary query, in change sequence order.

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 2nd query.
and    ct."_Partition-Id" = ?      -- partition id from preliminary query.
and    ct."_Recid"        = ?      -- recid        from preliminary query. 
and    c."_Operation"    != 3  -- optional - exclude 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:

Query #1
save rowid, partitioned for row R
query #2
build query #3
do
    query #3
    exitloop if no data
    accumulate type of changes, aggregate changes, to Source row
    send changed Source row values to Data Warehouse.
enddo

However, if the table has horizontal partitioning enabled and the CDC policy specifies Change tracking as Level 0 or Level 1, the change table record is unavailable. To achieve the same result as when the change table record is available, use the source table directly.

Step 1 on the CDC tables retrieves the source table number. By searching for an active CDC table policy for the source table, this step determines whether change tracking is active.

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’;

Step 2 uses a query that returns a set of change values for each source table row R, whose pro_partn_rowid matches the value returned by the pro_get_partn_rowid scalar function. pro_get_partn_rowid generates a base64 SQL representation of the row's record ID and partition ID using _cdc-change-tracking._Recid and _cdc-change-tracking._Partition-Id, respectively, and compares the result to the pro_partn_rowid from the partitioned source table.

select *
from pub.my_partioned_source_table P
inner join
pub."_cdc-change-tracking" c
 on P.pro_partn_rowid =pro_get_partn_rowid("_cdc-change-tracking"."_Recid",
"_cdc-change-tracking"."_Partition-Id") 
where c.source_table_number = ?; -- result returned from Step 1 query