Docs
...
Setting business goals for replication assets

Setting a business goal for a Data Replication asset

Last updated: Mar 27, 2025
Setting a business goal for a Data Replication asset

Change log

The Change log business goal configures the replication asset to document and track modifications that are made to a source database schema over time in a record called a change log. In addition, the Change log business goal synchronizes schemas and tables from the source to the target database.

A copy of the changes to the source database and the change log are delivered to the target database.

Configuring the Change log business goal

To create an asset with a Change log business goal:

  1. From the Assets tab of your project, click New asset > Replicate data.
  2. Enter a name.
  3. Select Change log as the business goal for your replication asset.
  4. On the Source options page, select a source connection from the list, or click Add Connection to create a new one. Make sure that your source connection is one of the options that are listed in the previous section.
  5. Click Select data.
  6. Select a schema to see the list of tables for that schema. You can select one or more schemas and tables.
  7. On the Target options page, select a target connection from the list, or click Add Connection to create a new one. Make sure that your target connection is one of the options that are listed in the previous section. If your target data store is Kafka, see Creating a Change log for Kafka target data stores. If your target data store is Db2 on Cloud or Db2 Warehouse on Cloud, see Creating a Change log for Db2 target data stores.

Creating a Change log for Kafka target data stores

For Kafka target data stores, the only Change log type is Log and apply DDL. This type of change log:

  • Populates DDL information in the chosen CHANGE_LOG_DDL topic
  • Replicates DDL without preserving the previous state. Does not retain schema history.
  • Logs DDL information only if a separate topic is specified. If you don't specify a topic for logging DDL information, this information won't be logged.

Now, finish configuring your asset:

  1. Choose whether to include the following optional parameters:

    Log DDL information to topic
    When selected, you must also select a destination topic where DDL information will be logged.
    Log transaction_id to commit_timestamp mapping information to topic
    When selected, you must also select a destination topic where mapping information will be logged.

  2. Specify any columns and JMESPath expressions to include in the target topic. You can use these columns to sort a Change log event, to search for a specific event, or to make sure that a Change log event occurred. By default, the following columns are included in your change log tables.

    SEQUENCE
    A monotonically increasing inter-transaction sequence counter that serves as a primary key for your change log target table.
    This column cannot be removed.
    JMESPath expression:
    meta(@, 'output_sequence')
    OPTYPE
    A column that indicates the kind of DML operation that took place.
    The possible values are:
    • INSERT
    • UPDATE_BEFORE
    • UPDATE_AFTER
    • DELETE
    JMESPath expression:
    meta(@, 'dml_operation_type')
    TRANSACTION_ID
    The numeric value that is used to identify each individual operation.
    JMESPath expression:
    meta(@, 'transaction_id')
    TIMESTAMP
    The time when the DML operation was committed in the source database.
    JMESPath expression:
    meta(@, 'commit_timestamp')

  3. Optional: Configure Kafka connections.
    Choose whether to edit the default selections of:

    • Kafka producer config properties
    • Kafka message format
    • Maximum producers
    • Replication header prefix
    • Default topic
    • Topic mappings
    • Override the default Kafka message parameters

    For more information about configuring Kafka connections, see Replicating Apache Kafka data.

  4. Click Review.

  5. On the Review page, review the summary, then click Create.

Creating a Change log for Db2 target data stores

  1. Choose the Change log type:

    Log and apply DDL
    Populates DDL information in the target CHANGE_LOG_DDL table.
    Replicates DDL without preserving the previous state. Does not retain schema history.
    Applies to relational databases.
    Does not drop or truncate topics.
    Schema evolution with new table
    Retains schema history by backing up the table for all DDL operations.
    Creates a new table with new DDL structure every time a new table is created in the egest database. The previous table is preserved and data is automatically replicated in the newly created table.
    Schema evolution in same change log table
    Retains schema history by backing up the table only for DDL operations that change table attributes, such as DROP, and RENAME_TABLE. For other changes to the source table, columns are backed up in the same Change log table.
    Adds new information in the new table or columns and preserves the original schema with renamed columns and tables.

  2. Choose whether to include the following optional parameters:

    OBJECT_SUFFIX
    A suffix that is used to preserve the previous state of the table.
    Applies only to Schema evolution with new table and Schema evolution in same change log table types.
    CHANGE_LOG_METADATA_CLEANUP
    When checked, metadata tables from the source database are dropped off when the replication is deleted.

  3. Specify any columns and JMESPath expressions to include in the target table. By default, the following columns are included in your change log. You can use these columns to sort a change log event, to search for a specific event, or to make sure that a Change log event occurred.

    SEQUENCE
    A monotonically increasing inter-transaction sequence counter that serves as a primary key for your change log target table.
    This column cannot be removed.
    JMESPath expression:
    meta(@, 'output_sequence')
    OPTYPE
    A column that indicates the kind of DML operation that took place.
    The possible values are:
    • INSERT
    • UPDATE_BEFORE
    • UPDATE_AFTER
    • DELETE
    JMESPath expression:
    meta(@, 'dml_operation_type')
    TRANSACTION_ID
    The numeric value that is used to identify each individual operation.
    JMESPath expression:
    meta(@, 'transaction_id')
    TIMESTAMP
    The time when the DML operation was committed in the source database.
    JMESPath expression:
    meta(@, 'commit_timestamp')

  4. Optional: Specify the target database configuration:
    If you don't want your data to be replicated in the default target schema, specify the schema where your data will be replicated.

  5. Click Review.

  6. On the Review page, review the summary, then click Create.

Change log output

After you run a replication with a change log business goal, the CHANGE_LOG_DDL table is created in your target schema and is populated with the following columns:

sequence_id
An automatically generated integer that is used to identify the order in which individual replications occurred.
ddl_type
The type of DDL that was replicated. See the DDL type table.
ddl_info
The complete DDL in SQL format.
ddl_object
The name of the object where the DDL modification occurred.
timestamp
The time when the DDL replication occurred. If the replication was patched, this column will contain only the patch_detection timestamp.

Table 2. ddl_type and their corresponding descriptions in the CHANGE_LOG_DDL table
ddl_type Description
0 REPLICATION_PATCHED
1 CREATE_TABLE
2 DROP_TABLE
3 RENAME_TABLE
4 ALTER_TABLE_ADD_COLUMN
5 ALTER_TABLE_DROP_COLUMN
6 ALTER_TABLE_RENAME_COLUMN
7 ALTER_TABLE_ALTER_COLUMN
8 ADD_PRIMARY_KEY
9 DROP_PRIMARY_KEY
10 ADD_UNIQUE_KEY
11 DROP_UNIQUE_KEY
12 ADD_CHECK_CONSTRAINT
13 DROP_CHECK_CONSTRAINT
14 TRUNCATE_TABLE

Learn more

Parent topic: Data Replication (beta)