The process of capturing data is driven by CDC triggers which fire for Create, Update, and Delete (CUD) operations. The data captured when the trigger fires is driven by CDC Table and Field policies that must first be created and activated. The triggers execute within a language-initiated (ABL or SQL) transaction, prior to execution of the record activity in the database.
To understand the process, consider the following simple execution flow:
When the CREATE CUSTOMER statement executes:
The original customer record being created is written to the Source data table.
The CDC triggers in the database engine consult the table and field level policies for the source table being tracked, and data is captured according to the table policy level detail value and field positions selected.
The trigger first writes a record to the Change Tracking table to indicate transactional scope and sequence.
The trigger then writes a data capture change record to the Change table based on Table and Field policies.
Upon transaction commit, all records are committed to the database.
To further understand, the flow, the following terms must be understood:
Change Table Policy — A table used to select a Source table for CDC activity. Table policies are created as a first step in designing what data will be captured from a Source table.
Change Field Policy — A table used to select fields from an identified Source table for CDC activity. A policy represents only one field. A Change Table policy can have several related table policies. The maximum number of field policies per Change Table policy is the number of fields in the Source table.
Change Tracking Table — A table used by the CDC process to insure transactional sequence. This table is created when CDC is first enabled, before any policies are defined. The table represents transactional information for all tables that participate in the CDC process.
Change Table — A user defined data capture table. The construction of this table begins when a CDC policy on the table level is created.
After the execution of one create statement on a table with an active CDC Table policy, the Change Tracking Table is updated, and depending on the level of the Table policy, and the presence of Field policies, the CDC trigger can also write data to the Change Table.
The data captured by the triggers reflects the source records after they are modified. The action of capturing the data is stored in the system wide Change Tracking Table. The captured data is then stored in a source specific Change Table. The Change table is a relational table with data types known to the ABL and SQL. This form of data allows for easy extraction and transformation of the data into an external data store (Data Warehouse). The process of extraction/transformation/loading (ETL) is a user application program.
Tables identified as using CDC need to have their Change Tables monitored for file system growth and be periodically purged. The purge activity can be part of the extraction activity or conducted as a separate activity.
Caution: Managing the growth of the Change Tracking Table and the Change Tables is a very serious issue. The growth of these files cannot be left unmanaged. If either table consumes all the space available to it, the database will shut down until space is added. If the database shuts down, current transactions will be backed out (including the originating transaction).
There may be times when changes for identified CDC tables no longer need to be captured. You can stop capturing change information by deactivating capture activity. This action sets the policy status to inactive. The policies and change data remain in place.
When the CREATE CUSTOMER statement executes:
The original customer record being created is written to the Source data table.
