The following CREATE TABLE SQL statement creates a table that is typical in a database set up for synchronization. Note also that the ALTER TABLE SET SYNCHISTORY Advanced Replication extension prepares the table for incremental publications. Be sure also to set up the table for incremental publications if you are using large tables, as opposed to tables that are small and heavily updated.
Note Before you execute the ALTER TABLE SET SYNCHISTORY statement, be sure you have defined your databases to be masters and replicas using the SET SYNC MASTER and SET SYNC REPLICA statements. Failure to define masters and replicas results in an error message when you attempt to use the ALTER TABLE statement. See Setting up databases for synchronization for details.
Note If the replica is read-only (no changes are done to the replicated parts of the publication), the statement ALTER TABLE ... SET SYNCHISTORY is not needed. In the same time, the following Flow Replica-resident parameter should be set:
set sync parameter SYS_SYNC_KEEPLOCALCHANGES 'Yes';
Note also that, in this case, ALTER TABLE ... SET HISTORY COLUMNS cannot be used.
CREATE TABLE CUST_ORDER ( ID VARCHAR NOT NULL, SYNC_STATUS CHAR(3) NOT NULL, CUST_ID VARCHAR NOT NULL, PRODUCT_ID VARCHAR NOT NULL, QUANTITY INTEGER NOT NULL, PRICE DECIMAL(10,2) NOT NULL, UPDATETIME TIMESTAMP NOT NULL, PRIMARY KEY (ID, SYNC_STATUS)); ALTER TABLE CUST_ORDER SET SYNCHISTORY; COMMIT WORK;
Some remarks about the example:
▪ The ID column is a generated primary key value (surrogate key) of the new row:
ID VARCHAR NOT NULL,
The value should preferably be a composite that contains two parts: the unique ID of the database where the row was first created and a sequence number within that database.
The reason for recommending usage of surrogate keys is the requirement of global key uniqueness. Inserting a row in two different replica databases with the same key value must not be permitted. If allowed, the next transaction propagation task would produce a unique constraint violation error in the master database. Whenever such an error occurs, the synchronization process halts and cannot continue until the problem has been fixed by deleting the duplicate row from the database.
▪ The SYNC_STATUS column holds information about the synchronization status of the row:
SYNC_STATUS CHAR(3) NOT NULL,
If the row is a valid one, then the value can be, for example, "OK". On the other hand, if an update conflict or other transaction validation error has happened when the row was committed in the master database, then the row should be inserted to the database with for example, value "C01" (first update conflict of this row). The existence of this column makes it possible to store multiple versions of the same row to the database: one official version (with status "OK") and multiple additional versions for conflict resolution and error recovery purposes. The primary key of the table is composed of the ID and SYNC_STATUS columns.
▪ The UPDATETIME column contains a timestamp that indicates the last date and time when the row was updated:
UPDATETIME TIMESTAMP NOT NULL,
The application logic (including the stored procedures that form the transactions) can use this column to detect update conflicts in the system.