ALTER TABLE table_name SET {SYNCHISTORY | NOSYNCHISTORY}
Usage
The SET SYNCHISTORY / NOSYNCHISTORY clause tells the server to use the incremental publications mechanism of solidDB® advanced replication architecture for the specified table. By default, SYNCHISTORY is not on. When this statement is set to SYNCHISTORY for a specified table, a shadow table is automatically created to store old versions of updated or deleted rows of the main table. The shadow table is called a synchronization history table or simply a history table.
The data in a history table is referred to when a replica gets an incremental REFRESH from a publication in the master. For example, if you delete the recordwith Ms. Smith’s telephone bill from the main table, a copy of the record is stored in the synchronization history table. When the replica refreshes, the master checks the history table and tells the replica that Ms. Smith’s record was deleted. The replica can then delete that record also. If the percentage of records that were deleted or changed is fairly small, an incremental update is faster than downloading the entire table from the master. If you perform a full REFRESH, rather than an incremental REFRESH, the history table is not used. The data in the table on the master is simply copied to the replica.
Versioned data is automatically deleted from the database when there are no longer any replicas that need the data to fulfill REFRESH requests.
You must use this command to turn on synchronization history before a table can participate in master/replica synchronization. You can use this command on a table even if data currently exists in that table; however ALTER TABLE SET SYNCHISTORY can only be used 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 on 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 only shows the mode of tables for which the mode was explicitly set. SYS_TABLEMODES does not show the mode of tables that are in the default mode. If SYNCHISTORY (or NOSYNCHISTORY) is not set for the table, the query returns an empty result set.
Usage in master
Use SET SYNCHISTORY in the master to enable incremental publications on a table.
Usage in replica
Use SET SYNCHISTORY in the replica to enable incremental REFRESHES on a table.
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. However, at the same time, the SYS_SYNC_KEEPLOCALCHANGES parameter must be set to yes with the following command:
set sync parameter SYS_SYNC_KEEPLOCALCHANGES 'Yes';
Examples
ALTER TABLE myLargeTable SET SYNCHISTORY; ALTER TABLE myVerySmallTable SET NOSYNCHISTORY;
Return values
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.