Sync Options

The Sort Buffer text box reflects the maximum amount of memory in megabytes that is used per thread to sort files. The number should reflect the amount of memory available on the machine that is used as the sorting buffer and the number of threads used. The actual amount used may be much smaller if the tables being synchronized are small. The default sort buffer of 80 megabytes should be compatible with most systems that follow our recommended guidelines for minimal machine configuration.

Transaction Batch Size refers to how many records are inserted into the target database in a single transaction. Some database systems have problems if too many records are involved in a transaction. For that reason, DataSync offers the ability to set this field manually. 1000 is the default.

You can select Create indexes before table population to reorder the synchronization process so that indexes are created first. The effect of this setting depends on the database system.

For instance, if the target is Oracle, it will be faster to create the indexes after the load of the data. Inserting rows with an existing index updates the indexes on every insert, whereas creation of an index at the end of the synchronization is somewhat more efficient.

On the other hand, if the target system is RMS, if indexes are created as the synchronization completes, then two entire copies of the file will exist on the target system, as a sequential file is converted into an indexed file with a CONVERT/FDL command.

The best choice strongly depends on the type of database system you are using and on the amount of resources available for performing the synchronizations.

Select the Store transaction log on target option to create a transaction log which is stored in the target database. If this option is not selected, the transaction log is not created.

A transaction log is a table that contains the unique index information for each row that was inserted, updated, or deleted, along with a flag that tells what sort of action was performed on each row. The transaction log table will be created on the target database and will have the name of the source table with the characters _ACD appended to the end. By using a transaction log, systems that need an audit process can tell exactly what was changed and what sort of operation was performed on the data. It should be noted, however, that the creation of a transaction log may slow synchronizations.

For each verified change, the target table transaction log will change the 'C' (for changed) in the ACD column of the transaction log to 'V' (for verified). If Logical update verification analysis is selected, for each row that has had a change detected via CRC differences, a column by column search is performed to confirm a difference.

Logical update verification analysis should only be used in special cases, where a careful analysis has shown that more data is being processed than is necessary. Selecting this option makes the synchronizations run more slowly, since for every change detected, every single column in the record is compared from source to target - one a column at a time. If a post-process step that is very slow must be performed on the changes (for example some expensive ETL operations), then logical update verification analysis might be worthwhile. Some examples might include the following:

Multiple physical values with different internal representations but the same physical meaning, such as positive and negative zero on one's complement machines or positive signed and unsigned decimal numbers (e.g. signed + 1 and unsigned + 1 have the same meaning but different internal representation)

Insignificant changes. For example, if the very last bit of a floating point number changed in the mantissa, the actual meaning of what is stored may not really have changed. For instance, someone might have edited a field that contained the value "2.1" as a floating point number. Though no changes were made, the value was saved again into the database. It is possible that the number will differ slightly due to the nature of floating point. A comparison shows that both the source and target numbers are intended as 2.1, but a CRC comparison shows that some bit has changed and consider them as completely different.

Masked values. For example, if a null-terminated string is stored in the source database, anything after the null string terminator is a physical difference that causes a different CRC, but the data stored may be the same. For instance, the string of bytes 'D', 'o', 'g', 0, 'f', 'o', 'o', 'd',0 will be interpreted simply as "Dog" since a null terminator (zero) follows the 'g'. The string of bytes 'D', 'o', 'g', 0, 'f', 'i', 'g', 'h', 't', 0 will also be interpreted simply as "Dog" since a null terminator (zero) follows the first 'g'. Anything beyond the null terminator has no meaning as far as the database is concerned. But CRC values will indicate that there has been a change.

By default, only the unique index used to perform incremental updates is created on the target table after synchronization. Select the Recreate all indexes on target table to recreate all indexes from the source table on the target table.

If you are using SQL Server or Oracle as the target database, select Create target tables in separate schemas to create target tables under different owners. DataSync creates a user for the purpose of creating tables with identical table names from different source catalogs. For example, if you want to synchronize both the test and production version of a table into a single SQL Server or Oracle database, CONNX fully qualifies the target name (product.table or test.table) so that there is no name collision.

This option will append a date and time column named cnxSyncDate to the end of each row in the synchronized target tables. The date and time of the last synchronization operation performed against the target row appears in the new column.

This option will prevent log information from being written to the datasync.log file. The datasync.log file is stored in the datasync folder.

Synchronization records that are older than the specified value will be purged from the database. This helps keeps the database size smaller. The default is 365 days.