Consider the following when designing databases and tables for replication:
Databases on all server instances involved in replication must be created with logging.
It is recommended that you replicate tables only from databases created with unbuffered logging. Enterprise Replication evaluates the logical log for transactions that modify tables defined for replication. If a table defined for replication resides in a database that uses buffered logging, the transactions are not immediately written to the logical log, but are instead buffered and then written to the logical log in a block of logical records. When this occurs, Enterprise Replication evaluates the buffer of logical-log records all at once, which consumes excess CPU time and memory. When you define a table for replication in a database created with unbuffered logging, Enterprise Replication can evaluate the transactions as they are produced.
To create a database with unbuffered logging, use:
CREATE DATABASE db_name WITH LOG
To minimize impact on the system, Enterprise Replication uses buffered logging whenever possible, even if the database is defined as unbuffered. For more information, see the section on CREATE DATABASE in the IBM Informix Database Design and Implementation Guide.
The following table types are not supported by Enterprise Replication:
Because RAW tables are not logged, they cannot be replicated using Enterprise Replication.
Because the database server deletes temporary tables when an application terminates or closes the database, you should not include these tables in your replication environment.
For more information on table types, see IBM Informix Database Design and Implementation Guide.
Enterprise Replication collects out-of-row data for transmission after the user transaction has committed. Due to activity on the replicated row, the data might not exist at the time Enterprise Replication collects it for replication. In such cases, Enterprise Replication normally applies a NULL on the target system. Therefore, you should avoid placing a NOT NULL constraint on any replicated column that includes out-of-row data.
In an update-anywhere replication environment, you must provide for conflict resolution using a conflict-resolution rule (see Conflict Resolution). When you create a table that uses the time stamp or time stamp plus SPL conflict-resolution rule, you must define the shadow columns, cdrserver and cdrtime on both the source and target replication servers.
For more information, see Preparing Tables for Conflict Resolution.
All tables involved in replication must have a PRIMARY KEY constraint defined on at least one column, which forces the column to be unique. (For more information about primary keys, see the IBM Informix Database Design and Implementation Guide and the IBM Informix Guide to SQL: Syntax.)
If you plan to use SERIAL data types (SERIAL and SERIAL8) as the primary key for a table, the same serial value might be generated on two servers at the same time.
To avoid this problem, use the CDR_SERIAL configuration parameter to generate non-overlapping (unique) values for SERIAL columns across all database servers in your replication environment. Set CDR_SERIAL in the ONCONFIG file for each primary (source) database server. For more information and examples, see CDR_SERIAL Configuration Parameter.
If you do not set CDR_SERIAL, you must specify that the serial column is part of a composite primary key, to avoid generating non-unique SERIAL primary keys. The non-serial column part of the primary key identifies the server on which the row was initially created.
If a table includes a cascading delete, when a parent row is deleted, the children are also deleted. If both the parent and child tables participate in replication, the deletes for both the parent and child are replicated to the target servers.
If the same table definition exists on the target database, Enterprise Replication attempts to delete the child rows twice. Enterprise Replication usually processes deletes on the parent tables first and then the children tables. When Enterprise Replication processes deletes on the children, an error might result, because the rows were already deleted when the parent was deleted. The table in Table 8 indicates how Enterprise Replication resolves cascading deletes with conflict-resolution scopes and rules.
For more information on cascading deletes, see the ON DELETE CASCADE section in the IBM Informix Guide to SQL: Syntax.
| Conflict-Resolution Rule | Conflict-Resolution Scope | Actions on Delete Errors |
|---|---|---|
| Time stamp | Row-by-row or transaction | Continue processing rest of the transaction |
| Ignore | Transaction | Abort entire transaction |
| Ignore | Row-by-row | Continue processing rest of the transaction |
A trigger is a database object that automatically sets off a specified set of SQL statements when a specified event occurs.
If the --firetrigger option is enabled on a replicate, any triggers defined on a table that participates in replication are invoked when transactions are processed on the target server. However, because Enterprise Replication only replicates the final result of a transaction, triggers execute only once on the target regardless of how many triggers execute on the source. In cases where the final evaluation of the transaction results in no replication (for example, an INSERT where the final row image is a DELETE, as shown in Table 5), no triggers execute on the target database.
If the same triggers are defined on both the source and target tables, any insert, update, or delete operation that the triggers generate are also sent to the target database server. For example, the target table might receive replicate data caused by a trigger that also executes locally. Depending on the conflict-resolution rule and scope, these operations can result in errors. To avoid this problem, define the replicate to not fire triggers on the target table.
For more information on triggers, see Enabling Triggers and the CREATE TRIGGER section in IBM Informix Guide to SQL: Syntax.
When using constraints, ensure that the constraints you add at the target server are not more restrictive than those at the source server. Discrepancies between constraints at the source and target servers can cause some rows to fail to be replicated.
4For tables that have referential integrity constraints set 4up between them, if you need to resynchronize the data in the tables, you 4can perform synchronization on the replicate set. For replicate sets, Enterprise 4Replication synchronizes tables in an order that preserves referential integrity 4constraints (for example, child tables are synchronized after parent tables).
4When you perform synchronization, rows that fail to be repaired 4due to discrepancies between constraints are recorded in the ATS and RIS files. 4For more information about ATS and RIS files, see Monitoring and Troubleshooting Enterprise Replication.
In bi-directional Enterprise Replication, if you replicate tables using sequence objects for update, insert, or delete operations, the same sequence values might be generated on different servers at the same time, leading to conflicts.
To avoid this problem, define sequence objects on each server so that the ranges of generated sequence values are disjunct. For more information about the CREATE SEQUENCE and ALTER SEQUENCE statements of SQL, see the IBM Informix Guide to SQL: Syntax.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]