Evaluating Data for Replication

Home | Previous Page | Next Page Introducing Enterprise Replication > About IBM Informix Enterprise Replication > How Enterprise Replication Replicates Data >

Evaluating Data for Replication

Enterprise Replication evaluates transactions based on a change to a final image of a row.

Row Images

Enterprise Replication evaluates the initial and final images of a row and any changes that occur between the two row images to determine which rows to replicate. Each row image contains the data in the row as well as the action performed on that row.

A row might change more than once in a particular transaction. For example, a transaction might insert and then update a row prior to committing. Enterprise Replication evaluates the net effect (final state) of a transaction based on the row buffers in the log. Enterprise Replication then determines what should replicate, based on the net effect, the initial state of the row, and whether the replicate definition (in particular, the WHERE clause) applies to the initial and final state.

Table 4 shows the logic that determines which rows are candidates for replication.

Table 4. Enterprise Replication Evaluation Logic
Initial Image	Replicate Evaluates	Final Image	Replicate Evaluates	Primary-Key Changed?	Send to Destination Database Server	Comments
INSERT	T or F	DELETE	T or F	Yes or no	Nothing	Net change of transaction results in no replication
INSERT	T or F	UPDATE	T	Yes or no	INSERT with final row image	Inserts final data of transaction
INSERT	T or F	UPDATE	F	Yes or no	Nothing	Final evaluation determines no replication
UPDATE	T	DELETE	T or F	Yes or no	DELETE with initial row image	Net result of transaction is delete
UPDATE	F	DELETE	T or F	Yes or no	Nothing	Net change of transaction results in no replication
UPDATE	T	UPDATE	T	Yes	DELETE with initial row image and INSERT with final row image	Ensures old primary key does not replicate
UPDATE	T	UPDATE	T	No	UPDATE with final row image	Simple update
UPDATE	T	UPDATE	F	Yes or no	DELETE with initial row image	Row no longer matches replicate definition
UPDATE	F	UPDATE	T	Yes or no	INSERT with final row image	Row now matches replicate definition
UPDATE	F	UPDATE	F	Yes or no	Nothing	No match exists, and therefore, no replication

Where:

Initial image is the before image of the transaction in the logical log.
The replicate evaluates to T (true) or F (false).
Final image is the image of the transaction that is replicated.

Table 4 illustrates how Enterprise Replication evaluates the row-image type (INSERT, UPDATE, DELETE), the results of evaluating the replicate WHERE clause for both the initial and final image, and whether the primary key changes as a result of the transaction.

Tip:

The evaluation logic in Table 4 assumes that the source and the destination tables are initially synchronized (identical before replication begins). If the tables were not synchronized, anomalous behavior could result.

After Enterprise Replication identifies transactions that qualify for replication, Enterprise Replication transfers the transaction data to a queue.

Evaluating Rows for Updates

Enterprise Replication evaluates rows for primary-key updates, for WHERE-clause column updates, and for multiple updates to the same row. The following list describes an occurrence in a transaction and the Enterprise Replication action:

Primary-key updates
Enterprise Replication translates an update of a primary key into a delete of the original row and an insert of the row image with the new primary key. If triggers are enabled on the target system, insert triggers are executed.
WHERE-clause column updates
If a replicate includes a WHERE clause in its data selection, the WHERE clause imposes selection criteria for rows in the replicated table.
- If an update changes a row so that it no longer passes the selection criteria on the source, it is deleted from the target table. Enterprise Replication translates the update into a delete and sends it to the target.
- If an update changes a row so that it passes the selection criteria on the source, it is inserted into the target table. Enterprise Replication translates the update into an insert and sends it to the target.
Multiple-row images in a transaction
Enterprise Replication compresses multiple-row images and only sends the net change to the target. Because of this, triggers might not execute on the target database. For more information, see Triggers.

Enterprise Replication supports the replication of BYTE and TEXT data types (simple large objects) and BLOB and CLOB data types (smart large objects), and opaque user-defined data types, as well as all built-in Informix data types. However, Enterprise Replication implements the replication of these types of data somewhat differently from the replication of other data types. For more information, see Replicating Simple and Smart Large Objects, and Replicating Opaque User-Defined Data Types.

Send Data Queues and Receive Data Queues

Enterprise Replication uses send and receive queues to receive and deliver replication data to and from database servers that participate in a replicate:

Send queue
Enterprise Replication stores replication data in memory to be delivered to target database servers that participate in a replicate. If the send queue fills, Enterprise Replication spools the send-queue transaction records to a dbspace and the send-queue row data to an sbspace.
Receive queue
Enterprise Replication stores replication data in memory at the target database server until the target database server acknowledges receipt of the data. If the receive queue fills as a result of a large transaction, Enterprise Replication spools the receive queue transaction header and replicate records to a dbspace and the receive queue row data to an sbspace. For more information, see Large Transactions.

For more information, see Setting Up Send and Receive Queue Spool Areas and Preventing Memory Queues from Overflowing.

The data contains the filtered log records for a single transaction. Enterprise Replication stores the replication data in a stable (recoverable) send queue on the source database server. Target sites acknowledge receipt of data when it is applied to or rejected from the target database.

If a target database server is unreachable, the replication data remains in a stable queue at the source database server. Temporary failures are common, and no immediate action is taken by the source database server; it continues to queue transactions. When the target database server becomes available again, queued transactions are transmitted and applied (see Applying Replicated Data).

If the target database server is unavailable for an extended period, the send queue on the source database server might consume excessive resources. In this situation, you might not want to save all transactions for the target database server. To prevent unlimited transaction accumulation, you can remove the unavailable target database server from the replicate (see Managing Replication Servers). Before the database server that is removed rejoins any replicate, however, you must synchronize (bring tables to consistency) with the other database servers (see Resynchronizing Data Among Replication Servers).

Data Evaluation Examples

Figure 1, Figure 2, and Figure 3 show three examples of how Enterprise Replication uses logic to evaluate transactions for potential replication.

Figure 1. Insert Followed by a Delete

begin figure description - This figure is described in the surrounding text. - end figure description

Figure 1 shows a transaction that takes place at the Dallas office. Enterprise Replication uses the logic in Table 5 to evaluate whether any information is sent to the destination database server at the Phoenix office.

Table 5. Insert Followed by a Delete Evaluation Logic
Initial Image	Replicate Evaluates	Final Image	Replicate Evaluates	Primary-Key Changed?	Send to Destination Database Server
INSERT	T or F	DELETE	T or F	Yes or no	Nothing

Enterprise Replication determines that the insert followed by a delete results in no replication operation; therefore, no replication data is sent.

In Figure 2, Enterprise Replication uses the logic in Table 6 to evaluate whether any information is sent to the destination database server.

Figure 2. Insert Followed by an Update

Table 6. Insert Followed by An Update Evaluation Logic
Initial Image	Replicate Evaluates	Final Image	Replicate Evaluates	Primary-Key Changed?	Send to Destination Database Server
INSERT	T or F	UPDATE	T	Yes or no	INSERT with final row image

The replicate WHERE clause imposes the restriction that only rows are replicated where the exempt column contains a value of "N." Enterprise Replication evaluates the transaction (an insert followed by an update) and converts it to an insert to propagate the updated (final) image.

In Figure 3, Enterprise Replication uses the logic in Table 7 to evaluate whether any information is sent to the destination database server.

Figure 3. Update; Not Selected to Selected

Table 7. Update; Not Selected to Selected Evaluation Logic
Initial Image	Replicate Evaluates	Final Image	Replicate Evaluates	Primary-Key Changed?	Send to Destination Database Server
UPDATE	F	UPDATE	T	Yes or no	INSERT with final row image

The example shows a replicate WHERE clause column update. A row that does not meet the WHERE clause selection criteria is updated to meet the criteria. Enterprise Replication replicates the updated row image and converts the update to an insert.