Upgrading the master schema

The administrator of the master database server executes the upgrade scripts. During the upgrade, the administrator should deny concurrent write access to the tables in the publication (by using the LOCK TABLE statement) and all synchronization access to the catalog (by using MAINTENANCE MODE).

For example, the following script adds a new table to the schema and includes it in the existing publication:

-- Set the sync mode to Maintenance to allow changes to tables that are
-- referenced by publications. Setting the sync mode to Maintenance also
-- blocks synchronization access to the master database.
SET SYNC MODE MAINTENANCE ;
COMMIT WORK ;

-- Alter the existing table by adding a new column to it
LOCK TABLE MYTABLE IN LONG EXCLUSIVE MODE ;
COMMIT WORK ;
ALTER TABLE MYTABLE ADD COLUMN NEWCOL INTEGER ;
COMMIT WORK ;

-- Set a default value to the new column.
-- While the sync mode is set to Maintenance, updates are not sent to
-- replicas. Therefore, if any updates were done on the master, the same
-- updates must be done locally on each replica while its sync mode is
-- set to Maintenance.
UPDATE MYTABLE SET NEWCOL = 1 ;
COMMIT WORK ;
-- Release the lock in the MYTABLE table.
UNLOCK TABLE MYTABLE ;
COMMIT WORK ;

-- Create a new table in the 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 ;

-- Create a new version of the publication.
"CREATE OR REPLACE PUBLICATION MYPUBLICATION
BEGIN
  RESULT SET FOR MYTABLE
  BEGIN
    SELECT * FROM MYTABLE ;
    RESULT SET FOR MYSECONDTABLE
    BEGIN
      SELECT * FROM MYSECONDTABLE WHERE MYTABLEID = MYTABLE.ID ;
    END
  END
END";
COMMIT WORK ;

-- Change the version information of the master 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 ;

As a result of a successful execution of the script in the master database, the schema of the master database is upgraded and the version name of the catalog is changed to VER2. The database is also opened again for synchronization access by setting the sync mode to NORMAL. However, the replica databases cannot synchronize with the master database until the replica database schemas are upgraded to the same level, see Detecting the need for an upgrade to the replica schema.