About CDC Control Tables

In jobs that include a change data capture transformation, you can use a control table to prevent the update of target rows that were processed in an earlier run. When you run a job that uses a control table, the CDC transformation first finds in the source the most recent insert, update, or delete action for a given unique identifier (business key). The most recent source row is then compared to the prior actions that appear in the control table. If the unique identifiers match, and if the rest of the rows are identical, then the source row is a duplicate and it is not added to the target.
Control tables are optional, so you need to use one only if the source changed data table contains information that was already loaded into the target.
The control table can be in SAS format or in native database format.
Column definitions in the control table are similar to those that are required in the source changed data tables.
You can use the New Table Wizard to create control tables.
In control tables, the names and order of the following columns can vary, because you identify those columns in the properties window of the CDC transformation:
Application Name
identifies the application that compares the source change data records to the records in the target to test for previous updates. A typical value for this column is SAS Data Integration Studio. The column type is character and the length is 64.
Table Name
identifies the source changed data table. The column type is character and the length is 64.
Context
provides the unique identifiers in the target that are not to be overwritten. The context is a character value with length of 32 for DB2, Attunity, and General. Oracle context is numeric with a length of 8.
Rows Processed
records the number of source changed data records that were processed the last time that the job was run. This value is updated at the end of the job run, as an output from the CDC transformation. The type of this column is numeric and the length is 8.
Timestamp
identifies the time and date when the job was run, in DATETIME16.6 format. The type of this column is numeric and the length is 8.
Last updated: January 16, 2018