Preparing Tables for a Consistency Check Index

To improve the speed of consistency checking with an index, you must define the ifx_replcheck shadow column in the tables on both the source and target servers involved in replication.

To define the ifx_replcheck shadow column when you create a new table, use the WITH REPLCHECK clause. For example, the following statement creates a new table named customer with a data column named id and the ifx_replcheck shadow column:

CREATE TABLE customer(id int) WITH REPLCHECK;

To add the ifx_replcheck shadow column to an existing replicated table:

  1. Set alter mode on the table by running the cdr alter --on command.
  2. Alter the table using the ADD REPLCHECK clause.
  3. Unset alter mode on the table by running the cdr alter --off command.

Because altering a table to add the ifx_replcheck shadow column is a slow alter operation, make sure you have disk space at least twice the size of the original table plus log space.

For example, the following statements add the ifx_replcheck shadow column to an existing table named customer:

ALTER TABLE customer ADD REPLCHECK;

To drop the ifx_replcheck shadow column, use the DROP REPLCHECK clause with the ALTER TABLE statement. For example, the following statements drop the ifx_replcheck shadow column from a table named customer: 

ALTER TABLE customer DROP REPLCHECK;

For more information on the CREATE TABLE and ALTER TABLE statements, see the sections in the HCL OneDB™ Guide to SQL: Syntax.