Sync Behavior
DataSync settings are accessed by clicking Gear Icon -> Settings.
The Sync Behavior page controls how DataSync behaves during the synchronization process. Here is a description of the available items:
Select Default Target
This drop down box contains the list of all the databases defined in the CDD. The selected database will be used as the target for Table Syncs and the default target for Transformation Syncs. The target database for transformations can be overridden when defining transformations.
Full Reload Threshold(%)
The percentage amount that appears in Full Reload Threshold is the number that will determine if it is more efficient to perform a full reload or an incremental update based on the percentage of rows changed in the source table. This number should be very small, in the range of 3 to 20 percent.
Max Worker Threads
The number in the Max Worker Threads text box reflects the amount of threads DataSync uses. (Recommended = 4 threads per CPU on the Data Sync machine) The maximum number is the number of threads on the PC (and also the number of servers on the host database server) that can be used when processing the synchronization. For example, if there are 1000 tables of the same size, 1 out of 3 threads would process about 333 tables each. Increasing the number of threads uses more resources on the PC and on the host database server, but can also increase performance.
Take note that increasing the thread count to a level that is too high can severely impact performance and possibly cause failures. It is strongly recommended that you do not exceed ten threads per CPU available on the Data Synchronization machine and also to consider the system's total memory, since each thread can allocate large blocks of memory when processing large tables. In order to test the effects of multiple threads, create a group and then synchronize the group. A few tests with different threading values can determine a good level for Max Worker Threads for your system through examination of the group synchronization time.
Full Synchronization Behavior
There are two methods to process a full synchronization. One is to drop and recreate 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 table 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.
There are two options for Full Synchronization Behavior:
Drop and recreate target table as needed - the default behavior.
Purge target table and preserve Schema - Select this option 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.
Select this option also if your security policy does not allow you to drop tables.
Method Used to Purge Data
There are two options for purging data:
Purge using DELETE FROM
Choose this method when 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.
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.
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.