To purge target tables and preserve schema

1. In the Settings tab, click the Sync Behavior sub-tab. Select the target database for the synchronization task from Default Target Database list.

2. There are two options for Full Synchronization Behavior:

Drop and recreate target table as needed is the default behavior.

Select Purge target table and preserve schema if:

• You do not want to delete  indices in the target table that are not in the source table. This could happen if the target table is used for specialized reporting

or

• Your security policy does not allow you to drop tables

There are two options for Purge target table and preserve schema:

1. • Purge using DELETE FROM  

Choose the Delete feature when all deletes are logged, and can be rolled back in case something goes wrong.

If there is an error on a Truncate command, the database is left in the state of the error, as the command is permanent. If a Delete command has problems, the database rolls back to the previous state on transaction failure.

1. • Purge using TRUNCATE TABLE (for target servers where truncate is available)

The CONNX Data Synchronization tool may perform a Truncate more quickly than a Delete. Choose Truncate if:

 

You do not want to delete indexes in the target table that are not in the source table. This could happen if the target table is used for specialized reporting.

or

Your security policy does not allow you to drop tables.

 

Truncate does not activate triggers, referential integrity constraints, or other database restrictions.  Since database integrity is maintained on the source database for the synchronization, choose Truncate if performance is a concern:

 

If an unsupported database is chosen, the sync succeeds, but a warning message is issued.

3. On the Tables tab, select a source table to synchronize, and then click the Sync Now button.

4. The Synchronize dialog box appears. Select the Full Reload radio button, and then click the OK button.
   
   
    

5. The Processing Estimates dialog box appears.
   
   
   
   There is a short pause as the CONNX Data Synchronization tool creates a target location.
   
   Full Synchronization Behavior:

There are two kinds of ways to process a full synchronization. One is to drop and recreate the schema on the target table every time. The other is to delete the data in the target table. When deleting data, we can either truncate the data or delete the data. Truncation is typically much faster, but not all systems support truncate. If you select truncation, and the target does not support truncation, you will receive a warning and Delete will be used instead. An advantage to dropping and recreating the schema is that changes in the source table will automatically be reflected in the target  and so the system is self-healing. However, there may be customizations on the target server. In this case, we want to purge the data instead. Truncation, while faster, is not typically logged and therefore the user will have to redo a failed sync. Deletion works on every system, but is much slower than dropping and adding the schema or truncation.

If the target database does not support truncation, a warning appears for every table selected for deletion even though the synchronization continues and succeeds. Click the OK button.


 

6. The message appears in the Status Description column on the Tables tab.