10 Altering Replication

Altering a replication scheme

You can use ALTER REPLICATION to alter your replication scheme on the master and subscriber data stores. Any alterations on the master store must also be made on its subscribers.

If you use ALTER REPLICATION to change a replication scheme that specifies a DATASTORE element, then:

• You cannot use SET NAME to change the name of the DATASTORE element

• You cannot use SET CHECK CONFLICTS to enable conflict resolution

Most ALTER REPLICATION operations are supported only when the replication agent is stopped (ttAdmin -repStop). However, it is possible to dynamically add a data store to a replication scheme while the replication agent is running, as described in "Creating and adding a subscriber data store".

The procedure for ALTER REPLICATION operations that require the replication agents to be stopped is:

1. Use the ttRepStop procedure or ttAdmin -repStop to stop the replication agent for the master and subscriber data stores. While the replication agents are stopped, changes to the master data store are stored in the log.

2. Issue the same ALTER REPLICATION statement on both master and subscriber data stores.

3. Use the ttRepStart procedure or ttAdmin -repStart to restart the replication agent for the master and subscriber data stores. The changes stored in the master data store log are sent to the subscriber data store.

This section includes the following topics:

• Adding a table or sequence to an existing replication scheme

• Adding a DATASTORE element to an existing replication scheme

• Dropping a table or sequence from a replication scheme

• Creating and adding a subscriber data store

• Dropping a subscriber data store

• Changing a TABLE or SEQUENCE element name

• Replacing a master data store

• Eliminating conflict detection

• Eliminating the return receipt service

• Changing the port number

• Changing the replication route

ALTER REPLICATION r1
  ADD ELEMENT elem_seq SEQUENCE seq
    MASTER westds ON "westcoast"
    SUBSCRIBER eastds ON "eastcoast"
  ADD ELEMENT elem_westleads TABLE westleads
    MASTER westds ON "westcoast"
    SUBSCRIBER eastds ON "eastcoast";

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
    INCLUDE SEQUENCE my.seq
  ALTER ELEMENT ds1 DATASTORE
    INCLUDE TABLE my.tab1;

ALTER REPLICATION my.rep1
  ADD ELEMENT ds1 DATASTORE
       MASTER rep2
       SUBSCRIBER rep1, rep3;

ALTER REPLICATION my.rep1
ADD ELEMENT ds1 DATASTORE
MASTER rep2
SUBSCRIBER rep1, rep3
INCLUDE TABLE my.tab2
INCLUDE SEQUENCE my.seq;

ALTER REPLICATION my.rep1
ADD ELEMENT ds2 DATASTORE
MASTER rep2
SUBSCRIBER rep1
EXCLUDE TABLE my.tab1
EXCLUDE SEQUENCE my.seq;

ALTER REPLICATION my.rep1
  ALTER ELEMENT ds1 DATASTORE
    EXCLUDE TABLE my.tab1;
DROP TABLE my.tab1;

ALTER REPLICATION r1
  DROP ELEMENT elem_seq;
DROP SEQUENCE seq;

ALTER REPLICATION r1
  ALTER ELEMENT elem_westleads
    ADD SUBSCRIBER backup3 ON "backupserver";

ALTER REPLICATION r1
  ALTER ELEMENT elem_westleads
    DROP SUBSCRIBER backup3 ON "backupserver";

ALTER REPLICATION r1
  ALTER ELEMENT Eelem_westleads
    SET NAME newelname;

ALTER REPLICATION r1
  ALTER ELEMENT * IN westds
    SET MASTER newwestds;

ALTER REPLICATION r1
  ALTER ELEMENT elem_accounts_1
    SET NO CHECK;

ALTER REPLICATION r1
  ALTER ELEMENT elem_waccounts
    ALTER SUBSCRIBER eastds ON "eastcoast"
      SET NO RETURN;

ALTER REPLICATION r1
  ALTER STORE eastds ON "eastcoast"
    SET PORT 22251;

ALTER REPLICATION r1
  DROP ROUTE MASTER eastds ON "eastcoast"
             SUBSCRIBER westds ON "westcoast"
             MASTERIP "192.168.1.100"
  ADD ROUTE MASTER eastds ON "eastcoast"
            SUBSCRIBER westds ON "westcoast"
            MASTERIP "192.168.1.101" PRIORITY 2;

Altering a replicated table

You can use ALTER TABLE to add or drop columns on the master data store. The ALTER TABLE operation is replicated to alter the subscriber data stores.

If you use ALTER TABLE on a data store configured for bidirectional replication, first stop updates to the table on all of the replicated data stores and confirm all replicated updates to the table have been received by the data stores before issuing the ALTER TABLE statement. Do not resume updates until the ALTER TABLE operation has been replicated to all data stores. This is necessary to ensure there will be no write operations in the pre-altered format after the table is altered on all data stores.

Also, if you are executing a number of successive ALTER TABLE operations on a data store, you should only proceed with the next ALTER TABLE after you have confirmed the previous ALTER TABLE has reached all of the subscribers.

Truncating a replicated table

You can use TRUNCATE TABLE to delete all of the rows of a table without dropping the table itself. Truncating a table is faster than using a DELETE FROM table statement.

Truncate operations on replicated tables are replicated and result in truncating the table on the subscriber data store. Unlike delete operations, however, the individual rows are not deleted. Even if the contents of the tables do not match at the time of the truncate operation, the rows on the subscriber data store are deleted anyway.

The TRUNCATE statement replicates to the subscriber, even when no rows are operated upon.

When tables are being replicated with timestamp conflict checking enabled, conflicts are not reported.

Dropping a replication scheme

You can use the DROP REPLICATION statement to remove a replication scheme from a data store. You cannot drop a replication scheme when master catchup is required unless it is the only replication scheme in the data store.

You must stop the replication agent before you drop a replication scheme.

DROP REPLICATION repscheme;

DROP REPLICATION repscheme;
DROP TABLE tab;