Synchronizing data by using synchronization messages
Data between master database and replica database is synchronized by using synchronization messages. The synchronization messages are programmed by 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 database can be experienced by using a set of SQL scripts.
1 In the replica database, synchronize the master and replica databases by executing the SQL statements that are shown in replica3.sql.
The statements create a message, send the message to the master database, and wait for a reply.
--********************************************************* -- 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;
2 Query table SYNCDEMO on both the replica and master databases to verify that both databases contain two rows.
SELECT * FROM SYNCDEMO;
3 Delete a row from the master database by executing (in the master database) the SQL statements that are 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 ;
4 Query table SYNCDEMO on both the replica and master databases to verify that the replica database contains two rows and the master database contains one row.
SELECT * FROM SYNCDEMO;
5 Resynchronize the master and replica databases by executing (in the replica database) the SQL statements that are shown in replica3.sql.
6 Query table SYNCDEMO on both the replica and master databases to verify that both replica and master databases contain one row.
SELECT * FROM SYNCDEMO;
7 Insert two more rows in the replica database by executing (in the replica database) the SQL statements that are shown in 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;
8 Resynchronize the master and replica databases by executing (in the replica database) the SQL statements that are shown in replica3.sql.
9 Query table SYNCDEMO on both the replica and master databases to verify that both replica and master databases contain three rows.
SELECT * FROM SYNCDEMO;
10 Update one row in the replica database by executing (in the replica database) the SQL statements that are shown in 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 ;
11 Resynchronize the master and replica databases by executing (in the replica database) the SQL statements that are shown in replica3.sql.
12 Query table SYNCDEMO on both the replica and master databases 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;
13 Update one row in the master database by executing (in the master database) the SQL statements that are shown in 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 with the replica database yet.
14 Query table SYNCDEMO on both the replica and master databases to verify that both the replica and master databases contain three rows and that the last update has occurred only at the master database.
SELECT * FROM SYNCDEMO;
15 Update the same row in the replica by executing (in the replica database) the SQL statements that are shown in 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;
16 Query table SYNCDEMO on both the replica and master databases to verify that the updates in master and replica databases are now different.
SELECT * FROM SYNCDEMO;
17 Resynchronize the master and replica databases by executing (in the replica database) the SQL statements that are shown in replica3.sql.
18 Query table SYNCDEMO on both the replica and master databases to verify that the conflict caused by updating the same row was processed properly.
SELECT * FROM SYNCDEMO;
Both master and replica databases should now contain four rows. One of the rows should have invalid (-1) status since the last update operation in the replica database will cause a conflict in the master database.