SQL statements and replication

You can run most SQL statements while replication is active. For some statements, however, you must set alter mode or stop replication.

You can run the following SQL statements with no limitations while Enterprise Replication is active:
  • ADD INDEX
  • ALTER INDEX . . . TO CLUSTER
  • ALTER FRAGMENT
  • ALTER INDEX
  • ALTER TABLE (except for the replication key)
  • CREATE CLUSTER INDEX
  • CREATE SYNONYM
  • CREATE TRIGGER
  • CREATE VIEW
  • DROP INDEX
  • DROP SYNONYM
  • DROP TRIGGER
  • DROP VIEW
  • RENAME COLUMN
  • RENAME DATABASE
  • RENAME TABLE
  • SET object mode (no disabling of replication key constraint)
  • START VIOLATIONS TABLE
  • STOP VIOLATIONS TABLE
  • TRUNCATE TABLE

After you define Enterprise Replication on a table by including that table as a participant in a replicate, you cannot exclusively lock a database that is involved in replication (or perform operations that require an exclusive lock). However, you can exclusively lock a table in a database.

You can rename both dbspaces and sbspaces while Informix® Enterprise Replication is active.

You cannot use the DROP TABLE SQL statement against a table that is included in a replicate.

You must first set alter mode with the cdr alter command before you can make these changes:
  • Add shadow columns:
    • ALTER TABLE ... ADD CRCOLS;
    • ALTER TABLE ... ADD REPLCHECK;
    • ALTER TABLE ... ADD ERKEY
  • Remove or disable the replication key constraint.
  • Modify the replication key columns. For example, alter a column to add default values or other integrity constraints.
  • Change the replication key from one or more columns to others. For example, if a replication key is defined on col1, you can change the replication key to col2.

You must stop replication before you make these changes:

  • Drop conflict resolution shadow columns with ALTER TABLE ... DROP CRCOLS.
  • Add or drop rowids.

SQL statements are limited to a maximum of 15000 bytes.