Supported DDL statements

Qlik Replicate automatically changes the metadata of the target table to reflect DDL statements performed on the source endpoint.

Supported DDL statements include:

• Create table
• Drop table
• Rename table
• Add column
• Drop column
• Rename column
• Change column data type

For information about supported DDL statements for a specific endpoint, see the chapter describing that endpoint. For more information about DDL settings, see Apply Changes Settings. See also Limitations and considerations below.

How Replicate handles DDL changes

When DDL changes occur, Replicate:

1. Captures ALTER TABLE DDLs from the transaction log without identifying the DDL type (ADD/DROP/MODIFY COLUMN).
2. Reads the new table metadata from the source backend.
3. Compares the previous table metadata with the new table metadata in order to determine the change. Note that a single change may include multiple DDL operations performed on the backend.
4. Uses the new table metadata to parse the subsequent DML events.

Limitations and considerations

When capturing DDL changes, the following limitations apply:

• When a rapid sequence of operations occurs in the source database (for instance, DDL>DML>DDL), Replicate might parse the log in the wrong order, resulting in missing data or unpredictable behavior. To minimize the chances of this happening, best practice is to wait for the changes to be applied to the target before performing the next operation.

   

  As an example of this, during change capture, if a source table is renamed multiple times in quick succession (and the second operation renames it back to its original name), an error that the table already exists in the target database might be encountered.

   

• If you change the name of a table used in a task and then stop the task, Replicate will not capture any changes made to that table after the task is resumed.

• Renaming a source table while a task is stopped is not supported.

• Reallocation of a table's Primary Key columns is not supported (and will therefore not be written to the DDL History Control Table).
• When a column's data type is changed and the (same) column is then renamed while the task is stopped, the DDL change will appear in the DDL History Control Table as “Drop Column” and then “Add Column” when the task is resumed. Note that the same behavior can also occur as a result of prolonged latency.
• CREATE TABLE operations performed on the source while a task is stopped will be applied to the target when the task is resumed, but will not be recorded as a DDL in the attrep_ddl_history Control Table.

• Operations associated with metadata changes (e.g. ALTER TABLE, reorg, rebuilding a clustered index, etc.) may cause unpredictable behavior if they were performed either:

  • During Full Load

    -OR-

  • Between the Start processing changes from timestamp and the current time (i.e. the moment the user clicks OK in the Advanced Run Options window).

    Example:

    IF:

    The specified Start processing changes from time is 10:00 am.

    AND:

    A column named Age was added to the Employees table at 10:10 am.

    AND:

    The user clicks OK in the Advanced Run Options window at 10:15 am.

    THEN:

    Changes that occurred between 10:00 and 10:10 may result in CDC errors.

• The DDL statement ALTER TABLE ADD/MODIFY <column> <data_type> DEFAULT <> does not replicate the default value to the target and the new/modified column is set to NULL. Note that this may happen even if the DDL that added/modified the column was executed in the past. If the new/modified column is nullable, the source endpoint updates all the table rows before logging the DDL itself. As a result, Qlik Replicate captures the changes but does not update the target. As the new/modified column is set to NULL, if the target table has no Primary Key/Unique Index, subsequent updates will generate a "zero rows affected" message.

• Modifications to TIMESTAMP and DATE precision columns will not be captured.