CDC Tables

There are two metadata tables: _Cdc-Table-Policy and _Cdc-Field-Policy. These tables are created in the source database when you enable Change Data Capture for the database. The metadata tables contain information about CDC policies for tables and their associated fields. You do not have to allocate space for the metadata tables; these tables reside in the schema storage area of the database. You should not manage these tables, they are managed by OpenEdge RDBMS.

The relationship between the CDC Table Policy Table and the Field Policy Table is a 1- to-many relationship. The hierarchy consists of two levels: Table policy (_Cdc-Table-Policy) and Field policy (_Cdc-Field-Policy). The table policy is a parent and the field policies are its children.

The CDC Table policy describes the policy attributes such as filename of the source table reporting to this policy, area the table resides in, active status of policy, and coverage level of the policy. This policy can point to a number of CDC Field policy records that identify the fields to be captured in the Change Table for this table. Once the new policy is defined, created, and committed, the policy becomes active. Each time a CUD record operation is executed, a record is written to the Change Tracking Table and the Change Table assigned to the policy.

There are CDC data tables: the _Cdc-Change-Tracking table, and an individual CDC Change Table for each source table for which you want to capture change data. Database administrators are responsible for allocating space for all CDC data tables, and specifying the area when enabling CDC.

A CDC Change Table is created in the source database when you define and activate a CDC policy for a source table. Note that a CDC Change Table is created only when the policy level is higher than minimal. CDC policies with the minimal level do not have a Change Table; their data is written only to the _Cdc-Change-Tracking table.

The _Cdc-Change-Tracking table maintains a record of all change capture activities for all tables with CDC policies. This table is created in the source database when you enable Change Data Capture for the database.

See CDC Schema for details of the schema for these tables.

Naming Change Tables

Change Tables can be given a name when the policy is designed, or a generated name is suggested at policy creation in the OpenEdge Management. Tables created through the ABL can be named by the policy designer. Change Table names can be a maximum of 32 characters and must be unique with their schema domain. The Change Table name must follow current table naming conventions.

If you leave the name blank when creating a policy, the table name is set using the default naming. In most cases the default naming is a good choice since it clearly identifies the table as a CDC table and it includes the name of the source table. The default naming appends "CDC_" to the name of the source table. For example, when the source table is Customer, the default Change Table name is CDC_Customer.

Change Table ownership and location

Tables created by the ABL are owned by the PUB schema and tables created by SQL can be owned by the PUB schema or another owner. The ABL can only drop tables in the PUB schema and SQL can only drop tables in the PUB and non-PUB schemas. Change Tables present an issue of ownership when they are created. A policy designer should consider the language ant ETL application is written in to best determine Change Table ownership upon creation. Access to the Change Table is controlled by the owning schema, this is particularly important for the ability to drop this table.

The policy designer needs to indicate Change Table ownership in the _Cdc-Table-Policy Table in the field _Ownership. The _Ownership field allows the designer to indicate ownership by specifying that ownership of the Change Table will be the same as the Source Table or that ownership will be PUB.

In addition, the Change Tables reside in a Type II area specified in the Table Policy for the table. This means the Source Table could reside in a Type I area and its Change Table will reside in a Type II area. The area location of the change files is modifiable after creation by the use of Table move. The name of the Change Table is not modifiable. If a new name is desired, a new policy will need to be added.

Life cycle of a Change Table

Once a Change Table is created for capture from a Source Table, it is possible for it to represent many policies over the course of its lifetime.

Many previous policies can come from a range of schema changes allowed:

Changing identifying fields (and by association their index) is not a permitted schema change. If this activity is required, CDC activity must be disabled for the table of interest and all change data removed. A new policy must be created to add the desired identifying fields and index.