ALTER TABLE ... SET SYNCHISTORY

Use the ALTER TABLE ... SET SYNCHISTORY or SET NOSYNCHISTORY clause to tell the server whether to use the incremental publications mechanism of the solidDB Advanced Replication architecture for the specified table.

▪ In the master database, use the ALTER TABLE ... SET SYNCHISTORY statement to enable incremental publications on a table.

▪ In a replica database, use the ALTER TABLE ... SET SYNCHISTORY statement to enable incremental REFRESHES on a table.

Note If the replica database is read-only, no changes are made to the replicated parts of the publication, and the statement ALTER TABLE ... SET SYNCHISTORY is not needed. However, at the same time, the SYS_SYNC_KEEPLOCALCHANGES parameter must be set to ‘yes’. For more information, see SET SYNC PARAMETER.

When you set SYNCHISTORY, a shadow table is automatically created to store old versions of updated or deleted rows of the specified table. The shadow table is called a synchronization history table, see Creating incremental publications.

You must use this statement to turn on synchronization history before a table can participate in master/replica database synchronization. You can use this statement on a table even if data currently exists in that table; however you can use the ALTER TABLE ... SET SYNCHISTORY statement only if the specified table is not referenced by an existing publication.

SET SYNCHISTORY must be specified in the tables of both master and replica databases.

To check if SYNCHISTORY is set for a table, check the SYS_TABLEMODES system table. The MODE column contains the SYNCHISTORY information.

For example, use the following query:

SELECT mode
FROM SYS_TABLES, SYS_TABLEMODES
WHERE table_name = 'MY_TABLE' AND SYS_TABLEMODES.ID =
SYS_TABLES.ID;
MODE
----
SYNCHISTORY
1 rows fetched.

SYS_TABLEMODES shows only the mode of tables for which the mode was explicitly set. If SYNCHISTORY (or NOSYNCHISTORY) is not set for the table, the query returns an empty result set.

Error code	Description
13047	No privilege for operation
13100	Illegal table mode combination
13134	Table is not a base table
25038	Table is referenced in publication publication‑name; drop or alter operations are not allowed
25039	Table is referenced in subscription to publication publication‑name; drop or alter operations are not allowed.

ALTER TABLE myLargeTable SET SYNCHISTORY;
ALTER TABLE myVerySmallTable SET NOSYNCHISTORY;