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.