The administrator defines for each database type (master and replica), a set of scripts (which might 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.
Some scripts might be the same for both the master and replica databases, for example, some or all CREATE TABLE statements.
Some scripts will run only on the master database, for example, scripts that create publications and some will run only on the replica database, for example, scripts that register a replica.
In the following scripts, the table MYTABLE is set to support incremental publications by setting 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.
After each replica database is created, the administrator connects to the replica server, sets the current catalog, and executes the scripts. As part of the schema creation process, the replica database is registered with the master database.
Sample script to set up the master database:
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 ;
SET SYNC PARAMETER SYNC_APP_SCHEMA_VERSION 'VER1'; COMMIT WORK ;
Sample script to set up the replica database:
CREATE TABLE MYTABLE ( ID INTEGER NOT NULL, STATUS INTEGER NOT NULL, TEXTDATA VARCHAR NOT NULL, PRIMARY KEY (ID, STATUS));
ALTER TABLE MYTABLE SET SYNCHISTORY ; COMMIT WORK ;
CALL SYNC_REGISTER_PUBLICATION (NULL, 'MYPUBLICATION'); COMMIT WORK ;
SET SYNC PARAMETER SYNC_APP_SCHEMA_VERSION 'VER1'; COMMIT WORK ;