Find change data references for a table row with minimal Change tracking

When the table Policy defines change tracking as Level 0 or Level 1, the Change table is not used. Instead, the Change Tracking table identifies the type of change and the row in the source table that was changed.

The following example is a simple case of an application table with only one CDC table policy applied. This query identifies all the changes made to a specific row in a source table in the order in which the changes are made. The change data is accessed in two steps in this use case.

In step 1, the query returns a result set with each row containing one instance of the RecID and the Partition ID of a unique source table row with one or more changes. In this case, Time Range is optional. The query design is as follows:

  • Join

    • Pub.” _File” to get source table definition’s unique dbkey based on table name, owner.
    • Change Tracking to get source table number, recid, partition number to identify source table changes for a particular source table row.
  • Group the joined data so that multiple changes for a given row are reduced down to one row in the Result Set.
The first section of the query performs the JOIN operation and is as follows:
select ct."_Source-Table-Number", ct."_Partition-Id", ct."_Recid",      
                                        -- Source table recid, partn id
from   pub.”_File” f
inner join
       pub."_Cdc-Change-Tracking" ct
          on f."_File-Number" = ct."_Source-Table-Number"
where f."_file-name" = ‘customer’    -- table name
and   f."_Owner"     = ‘pub’         -- table name
and   ct."_Time-Stamp" between ? and ?  -- time range parameters from ETL 
group by ct.”_Source-Table-Number",  ct."_Partition-Id",  ct."_Recid" ;

Optionally, the Recid column from the query in Step 1 can be used to get the current version of the source table row that changed, and is as follows:

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

Step 2 uses a query which returns a result set of the set of changes for each given RecID from the first query in the change sequence order:

select ct.*                      -- specify change tracking fields here.
from   pub."_Cdc-Change-Tracking" ct
where  ct."_Source-Table-Number" = ?    -- source table number from step 1
and    ct."_Partition-Id"        = ?    -- partition id from 1st query.
and    ct."_Recid"               = ?    -- recid        from 1st query.
order by ct1."_Change-Sequence"      -- should map to index _Part-Rec-Id
                              -- since leading keys are const parameters

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 to Source row
      enddo
      send changed Source row values to Data Warehouse.
enddo