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.