Example: Upgrading a distributed schema

This example describes a typical sequence of steps used to create and update the schema of a distributed system. The schema is first created (without using the Maintenance Mode features) and the upgraded using the Maintenance Mode features.

When the distributed system is first created, the administrator of the overall system defines for each database type (master, replicas) a set of scripts (which may be stored procedures) that create the schema of the database catalog. These scripts are responsible for creating all database objects of the schema, including procedures, triggers, events, publications, and so on. Many of the scripts may be the same for the replica as for the master. As an example, some or all CREATE TABLE commands may be the same on the replica and master. Other scripts will be different for master and replicas. For example, the scripts that create publications are run only on the master, while the scripts that register a replica are run only on the replicas.

Each synchronizable catalog may have a schema version name as one of its properties. If master and replica catalogs have different schema version names, sending the synchronization message from replica to master fails. The name of the property is SYNC_APP_SCHEMA_VERSION. This version name can be set using the SET SYNC PARAMETER statement.

Creating the Initial Master Schema

The script to create the initial master database is below:

CREATE TABLE MYTABLE (
  ID INTEGER NOT NULL PRIMARY KEY, STATUS INTEGER NOT NULL, TEXTDATA VARCHAR NOT NULL);
  ALTER TABLE MYTABLE SET SYNCHISTORY ;
COMMIT WORK ;
"CREATE PUBLICATION MYPUBLICATION
BEGIN
  RESULT SET FOR MYTABLE
  BEGIN
    SELECT * FROM MYTABLE ;
  END
END";
COMMIT WORK ;

Creating a Replica Schema

When each replica is first created, the replica's administrator connects to the replica server, sets the current catalog, and executes the scripts. As part of the schema creation process, the replica is registered with the master.

After this process, both the master and the replica catalogs have the same version name, which means that their schemas are compatible with each other. You should set the synchronization parameter SYNC_APP_SCHEMA_VERSION to the same value in both the master and replica(s) so that they can recognize that their schemas are compatible.

In the replica, the initial schema looks as follows:

In the above script, the table MYTABLE is set to support incremental publications by setting the SYNCHISTORY on. Additionally, the replica database registers to the MYPUBLICATION publication by calling the SYNC_REGISTER_PUBLICATION system procedure. The version name for both the master and the replica database catalogs is set to be 'VER1'.

The administrator of the overall system creates for each database type (master and replicas) a set of scripts that change the schema from the current version to the upgraded version. Upon completion of the scripts, the version name is upgraded to a new one.

The new scripts can be distributed to the replicas using any data distribution mechanism, including synchronization across a advanced replication database hierarchy.

When defining a schema upgrade, the following rules apply:

▪Any new database objects may be added to the schema. Any database object may also be dropped from the schema. If a table is dropped from the schema, it must be removed from publication definitions first.

▪The call interface (i.e. parameter list) of stored procedures should not be changed. If such a change is needed, the new procedure should have a different name. The old procedure should remain in the system for a while to guarantee successful execution of transactions that have been saved in replicas but that have not yet been propagated to the master.

▪Publications can be changed by adding/removing result sets or adding/removing columns of a result set. Changing search criteria of a result set is also possible; however, it may force the next refresh of that result set to be a full refresh rather than an incremental refresh.

▪The parameter list of publications must not change. If you must change the parameter list, then you must drop the old publication and create a new one. The refreshes from the new publication will be full refreshes rather than incremental refreshes.

If you write scripts to make changes to the schema, most of the commands will be the same for the replica as for the master. For example, if you add a new column to a table on the master, then you may want to add that new column to the corresponding tables on each replica. However, there are some commands that will not be the same on the master and replica. For example, the commands to change the publications do not apply to the replicas. Similarly, if you make changes that do require to completely drop and re-create a publication, and thus that require a replica to reregister with the master, the reregister commands will be executed only by the replica(s), not by the master. As you write your scripts, you may want to organize them in such a way that you make maximum re-use of the common elements (such as ALTER TABLE statements) without running other statements (such as statements to create publications or register replicas) on the wrong servers.

Below, we show a simple example of upgrading a schema of a distributed database system using the schema upgrade capabilities of solidDB^®.

Typically the first database schema to be upgraded is that of the master database. After upgrading the master, the databases of the replicas are upgraded. In a system that has more than 2 tiers (and thus where intermediate-level nodes are both masters and replicas), the process is to start at the topmost tier and upgrade it. Upon completion of the scripts in a database, the version name of the affected database catalog is upgraded to a new one. This indicates to the next tier of databases that they need to be upgraded as well.

Upgrading the Master Schema

The master schema should be upgraded prior to upgrading the replica schemas. To do this, the administrator of the master database server executes the upgrade scripts. During the upgrade, the administrator usually 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).

The following script shows how to add a new table to the schema and include 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 ;

-- 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 ;

After successful execution of the script in the master database, the schema of the master database has been upgraded and the version name of the catalog has been 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 before they upgrade their schema to the same level.

Detecting the Need for Upgrading Replica Schema

If the master and replica have each defined their version by setting the bulletin board parameter SYNC_APP_SCHEMA_VERSION, and if the master and replica versions do not match, then an error is returned when the replica attempts to synchronize with the master next time. Typically, the data is synchronized by executing the following kind of SQL script in the replica database.

MESSAGE syncmsg BEGIN ;
MESSAGE syncmsg APPEND PROPAGATE TRANSACTIONS ;
MESSAGE syncmsg APPEND REFRESH MYPUBLICATION ;
MESSAGE syncmsg END ;
COMMIT WORK ;
MESSAGE syncmsg FORWARD TIMEOUT 10 ;
COMMIT WORK ;

If the version name of the master database does not match the version name of the replica database, then the statement

will fail with error:

Although sending the message to the master failed, the message stays persistent in the replica database. After the replica schema is upgraded to match the master's schema, the failed message can be resent to the master by using the statement

After the need for replica schema upgrade has been detected, the administrator of the replica server needs to upgrade the schema to the new version using version upgrade scripts developed for that replica database.

Upgrading the Replica Schema

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

After a successful upgrade, the schema version name is changed to the new one. The possibly hanging synchronization message(s) can now be resent to the master.

In our example, the script for upgrading the replica to match with the new version of the master schema looks like the following:

-- 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 ;

-- 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’s 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 ;