Enable Replication of truncate table

You can enable replication of the truncate table command to particular destination database tables when you create or define a subscription, if you are using Adaptive Server Enterprise version 11.5 or later.

The truncate table command can truncate one or more partitions. Replication Server will recreate the same command executed at the primary database. This requires the replicate site to have the same partition names, otherwise, DSI shuts down.

You have an option to skip truncate table and apply appropriate action at the replicate site, or use rs_truncate function string to customize the action in the replicate site. Replication Agent sends this command once the LTL version is set to 700.

To create or define a subscription that enables replication of truncate table, log in to Replication Server and enter:

create subscription subscription
	for table_rep_def 
	with replicate at data_server.database
	 ...
	subscribe to truncate table

When truncate table executes at the destination database, Adaptive Server deallocates whole data pages. It does not delete rows one at a time.

Note: Replication Server executes truncate table at the replicate database as the maintenance user. Among the permissions granted to maintenance user is replication_role. If you revoke the maintenance user replication_role, you cannot replicate truncate table unless the maintenance user has been granted sa_role, the maintenance user owns the table, or the maintenance user is aliased as the database owner.

Warm standby applications can copy the execution of truncate table to standby databases without a subscription. See Replication Server Administration Guide Volume 2 > Manage Warm Standby Applications > Alter Warm Standby Database Connections > Alter Logical Connections > Replicate truncate table to Standby Databases.

See Replication Server Reference Manual > Replication Server Commands for complete command syntax and usage guidelines for define subscription and create subscription.