How to create a table replication in one same database? (DBMR2758)
Generally, table replication is applied between different databases. However, it also can be used in one same database.
There are two ways:
First, use Create Replication syntax. But for this usage, the two options, "CLEAR DATA" and "CLEAR AND FLUSH DATA", cannot be contained in the Create Replication syntax.
For example:
CREATE REPLICATION rp_name
WITH PRIMARY AS SYSADM.FILE1
REPLICATE TO DBSAMPLE5:UTENTE.FILE1;
CREATE REPLICATION rp_name
WITH PRIMARY AS SYSADM.FILE1
REPLICATE TO DBSAMPLE5:UTENTE.FILE1
FLUSH DATA;
Note: If users create the replication with JDBA Tools, the button "Clear Data" cannot be checked.
If need to clear the original data of destination table, users should delete all records manually before creating the table replication.
Second, use triggers. Please note that this way only applies to a SYNC table replication, and it will occupy an extra connection as a global transaction.
For example:
CREATE TRIGGER TR_INSERT AFTER INSERT ON SYSADM.FILE1
FOR EACH ROW
(INSERT INTO UTENTE.FILE1 VALUES(new.c1,new.c2));
CREATE TRIGGER TR_UPDATE AFTER UPDATE ONSYSADM.FILE1
FOR EACH ROW
(UPDATE UTENTE.FILE1 SET C1=NEW.C1,C2=NEW.C2);
CREATE TRIGGER TR_DELETE AFTER DELETE ON SYSADM.FILE1
FOR EACH ROW
(DELETE FROM UTENTE.FILE1 WHERE C1=OLD.C1 AND C2=OLD.C2);
Version: All
Product: Normal/Bundle
Platform: Windows/Linux