Upgrading the replica schema

In a typical upgrade process, the administrator of the master database writes a set of scripts to modify the schema, and then sends the appropriate scripts to the administrator of the replica database.

After a successful upgrade, the schema version name is changed to the new name. Any outstanding synchronization messages can now be resent to the master database.

The following script is an example that you might use to update the replica database schema and set the version of the replica database schema to match the version of the master database schema:

-- Set the sync mode to Maintenance to allow changes to tables that
-- are referenced by publications.
-- The synchronization functions of the replica database are suspended.
SET SYNC MODE MAINTENANCE ;
COMMIT WORK ;

-- Alter the existing table by adding a new column to it.
-- Updates done in maintenance mode are not rolled back in the next
-- synchronization.
-- Corresponding updates have already been done in the master DB.
LOCK TABLE MYTABLE IN LONG EXCLUSIVE MODE ;
ALTER TABLE MYTABLE ADD COLUMN NEWCOL INTEGER
;
COMMIT WORK ;
UPDATE MYTABLE SET NEWCOL = 1 ;
COMMIT WORK ;

-- Release the lock on the MYTABLE table.
UNLOCK TABLE MYTABLE ;
COMMIT WORK ;

-- Create a new table in the replica schema.
CREATE TABLE MYSECONDTABLE (
  ID INTEGER NOT NULL,
  MYTABLEID INTEGER NOT NULL,
  STATUS INTEGER NOT NULL,
  TEXTDATA VARCHAR NOT NULL,
  UPDATETIME TIMESTAMP NOT NULL,
  PRIMARY KEY (ID, MYTABLEID, STATUS)) ;
ALTER TABLE MYSECONDTABLE SET SYNCHISTORY ;
COMMIT WORK ;

-- Note that changes in the publication definition do not require any
-- actions in the replica end. The changes in the publication meta
-- data as well as data of the added tables are automatically sent
-- to the replicas.
-- Change the version information of the replica database catalog.
SET SYNC PARAMETER SYNC_APP_SCHEMA_VERSION 'VER2';
COMMIT WORK ;

-- Set the sync mode back from MAINTENANCE to NORMAL.
SET SYNC MODE NORMAL ;
COMMIT WORK ;

After the script has been executed successfully in the replica database, any outstanding synchronization messages can be resent to the master database by executing the following statement for each held message: