Data between master and replica is synchronized using synchronization messages. The synchronization messages are programmed using SQL statements (MESSAGE) that are executed in the replica database. Messages include the database operations needed for synchronization.
In the evaluation setup, the synchronization of data between the master and replica is showcased using a set of SQL scripts.
1 Synchronize the master and replica databases by executing in the replica the SQL statement shown in replica3.sql.
The statements create a message, send the message to the master, and wait for a reply.
REPLICA3.SQL
--********************************************************* -- replica3.sql -- Creates a new message with name ’my_msg’ and -- appends two tasks to message my_msg: -- propagate all local transactions to the master -- refresh data from master to the replica using -- the pub_demo publication
-- Execute in the REPLICA database. -- -- Note: AUTOCOMMIT must be off --********************************************************* SET CATALOG replica_catalog ; MESSAGE my_msg BEGIN ; MESSAGE my_msg APPEND PROPAGATE TRANSACTIONS ; MESSAGE my_msg APPEND REFRESH PUB_DEMO ; MESSAGE my_msg END ; COMMIT WORK ; -- Send the message to the master, do not wait for reply. MESSAGE my_msg FORWARD; COMMIT WORK; -- Request reply to the message separately from master. MESSAGE my_msg GET REPLY TIMEOUT DEFAULT ; COMMIT WORK;
Query table SYNCDEMO on both the replica and master to verify that both databases contain two rows.
SELECT * FROM SYNCDEMO;
2 Delete a row from the master by executing in the master the SQL statements shown in master2.sql.
--************************************************* -- master2.sql -- Deletes a row from the sample table -- Execute in the MASTER database --************************************************** SET CATALOG sync_demo_catalog ; DELETE FROM SYNCDEMO WHERE ID = 2; COMMIT WORK ;
3 Query table SYNCDEMO on both the replica and master to verify that the replica contains two rows and the master contains one.
SELECT * FROM SYNCDEMO;
4 Resynchronize the master and replica databases by executing in the replica the SQL statements shown in replica3.sql above.
5 Query table SYNCDEMO on both the replica and master to verify that both replica and master contain one row.
SELECT * FROM SYNCDEMO;
6 Insert two more rows in the replica by executing in the replica the SQL statements shown in replica4.sql.
REPLICA4.SQL
--******************************************************* -- replica4.sql -- This script inserts two rows to the REPLICA database -- and saves the transaction to be propagated to the MASTER -- -- Execute in the REPLICA database -- ******************************************************* SET CATALOG sync_demo_catalog; CALL SYNCDEMO_INSERT (1,3,102,’Third row’,’1998-05-15 12:10:00’,’R’); SAVE CALL SYNCDEMO_INSERT (1,3,102,’Third row’,’1998-05-15 12:10:00’,’M’); CALL SYNCDEMO_INSERT (1,4,103,’Fourth row’,’1998-05-15 12:10:01’,’R’); SAVE CALL SYNCDEMO_INSERT (1,4,103,’Fourth row’,’1998-05-15 12:10:01’,’M’); COMMIT WORK;
7 Resynchronize the master and replica databases by executing in the replica the SQL statements shown in replica3.sql above.
8 Query table SYNCDEMO on both the replica and master to verify that both replica and master contain three rows.
SELECT * FROM SYNCDEMO;
9 Update one row in the replica by executing in the replica the SQL statements shown in replica5.sql.
REPLICA5.SQL
--********************************************************* -- replica5.sql -- This script updates one row in the REPLICA database -- and saves the row to be propagated to the MASTER -- Execute in the REPLICA database --********************************************************* SET CATALOG sync_demo_catalog ;
10 Resynchronize the master and replica databases by executing in the replica the SQL statements shown in replica3.sql above.
11 Query table SYNCDEMO on both the replica and master to verify that both the replica and master databases contain three rows and that the update has been propagated to the master database.
SELECT * FROM SYNCDEMO;
12 Update one row at the master by executing in the master the SQL statements shown in master3.sql.
MASTER3.SQL
--********************************************************* -- master3.sql -- This script updates one row in the MASTER database
-- Execute in the MASTER database --********************************************************* SET CATALOG sync_demo_catalog ; CALL SYNCDEMO_UPDATE (1,3,203,’Row 3 masterchange’, ’1998-05-15 12:10:00’,’M’); COMMIT WORK ;
Important: Do not synchronize the data to replica.
13 Query table SYNCDEMO on both the replica and master to verify that both the replica and master contain three rows and that the last update has occurred only at the master database.
SELECT * FROM SYNCDEMO;
14 Update the same row in the replica by executing in the replica the SQL statements shown in replica6.sql.
REPLICA6.SQL
--********************************************************* -- replica6.sql -- This script updates one row in the REPLICA database -- and saves the row to be propagated to the MASTER -- -- Execute in the REPLICA database --********************************************************* SET CATALOG sync_demo_catalog ; CALL SYNCDEMO_UPDATE (1,3,203,’Row 3 replicachange’,’1998-05-15 12:10:00’,’R’); SAVE CALL SYNCDEMO_UPDATE (1,3,203,’Row 3 replicachange’,’1998-05-15 12:10:00’,’M’); COMMIT WORK;
15 Query table SYNCDEMO on both the replica and master to verify that the updates in master and replica are now different.
SELECT * FROM SYNCDEMO;
16 Resynchronize the master and replica databases by executing in the replica the SQL statements shown in replica3.sql above.
17 Query table SYNCDEMO on both the replica and master to verify that the conflict caused by updating the same row was processed properly.
SELECT * FROM SYNCDEMO;
Both master and replica should now contain four rows. One of the rows should have invalid (-1) status since the last update operation at the replica will cause a conflict at master database.