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:
- Captures ALTER TABLE DDLs from the transaction log without identifying the DDL type (ADD/DROP/MODIFY COLUMN).
- Reads the new table metadata from the source backend.
- 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.
- Uses the new table metadata to parse the subsequent DML events.
When capturing DDL changes, the following limitations apply:
- When a rapid sequence of DDL-DMLs-DDL occurs, Replicate may read the table metadata after the second DDL which may (on rare occasions) result in missing data due to incorrect parsing of DML events from the log.
- 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:
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).
The specified Start processing changes from time is 10:00 am.
A column named Age was added to the Employees table at 10:10 am.
The user clicks OK in the Advanced Run Options window at 10:15 am.
Changes that occurred between 10:00 and 10:10 may result in CDC errors.
In any of the above cases, the affected table(s) must be reloaded in order for the data to be properly replicated to the target.
- 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.