Core Options

DA server provides various comparison and job options, which you can use to optimize row and schema comparison queries.

Compressed Data Transfer (CDT)

During CDT, the row data between remote DA agent (excluding local DA agent) and DA server gets compressed thereby improving overall comparison time in distributed environments that have high network latency. Compressed data includes:

All row data transmitted during the initial row or key comparison
All retries of row or key comparison
Verified differences of row or key comparison

CDT is optional for row comparison and not supported for schema comparison. Hashes do not compress well; the initial comparison and the in-flight data comparisons do not see much benefit in using CDT when the columns use the "column_hash" or "row_hash" option. However, literal data compresses well. CDT is beneficial to the initial comparison and in-flight data comparisons when the columns use the "literal" column option. The verify differences phase always benefits from using CDT.

Consider CDT when:

Local area network (LAN) or wide area network (WAN) is a bottleneck
Performing literal comparisons
You expect hundreds or thousands (or more) of differences
Your primary key column is large (as key columns are never hashed)

Do not choose CDT when:

You use a local DA agent.
LAN or WAN performance is not an issue.
You always use hashing and either never use the “verify differences” option or you use it, but expect few or no differences.

To use this option, set compress_data_transfer to true.

External Sort Option

An order by clause specifies that a select statement returns a result set with the rows being sorted by the values of the key columns. DA server requires source and target table rows to be sorted before they can be compared. For very large tables, this sorting may have a large negative impact on the Adaptive Server temporary database space. To reduce the impact of processing order by clause in the databases, use the external sort option, which:

Omits the order by clause and receives unsorted rows from the database.
Sorts rows as they are written to flat files on your system.
Reads simultaneously from all flat files and returns the sorted rows for comparison.

You can control and configure the external sort option by tuning the associated configuration parameters for best possible results.

To use this option, set external_sort to true.

Database Hash Comparison

Note: Adaptive Server supports the hashbytes function in version 15.0.2 and later. If your source or target database is earlier than 15.0.2, you cannot use the database_hash option.

The database_hash column comparison option does not support large objects (LOB), such as text, image, and unitext. This is because the Adaptive Server hashbytes function does not accept LOB types as a parameter. All LOB types use a “first N bytes” parameter, where N is a configurable parameter. If the number of bytes in the LOB column is less than “N”, the entire column value is used.

Adaptive Server Hashbytes Null Handling

The Adaptive Server Transact-SQL® hashbytes function ignores null values.

For example, if a source table has column_a=34 and column_b=NULL and a target table has column_a=NULL and column_b=34, the equality test is:

hashbytes(34, NULL) = hashbytes(NULL, 34),

which computes as:

hashbytes(34) = hashbytes(34), (a “false positive” match).

To manage the Adaptive Server hashbytes limitation, DA server provides a configuration parameter, db_hash_ase_ignore_null, to help reduce the chances of a “false positive” row match. Setting db_hash_ase_ignore_null to false eliminates this issue by adding an extra value to denote the “is null” state of a column. The above example becomes:

hashbytes(0, 34, 1, NULL) = hashbytes(1, NULL, 0, 34),

which computes as:

hashbytes(0, 34, 1) = hashbytes(1, 0, 34).

Data Reconciliation Option

DA server can fix data differences between your source and target databases. When creating a new job, DA server provides these two comparison options:

create_recon_script – generates a script that includes insert, update, and delete statements when you set this option to true.
auto_reconcile – generates and executes the insert, update, and delete statements on the database that requires reconciliation when you set this option to true.

Note: Set create_col_log to true for the reconciliation option to work.

Scheduling Option

When you create a comparison job, you can assign specific schedules to it. You can schedule a job based on days, weeks, or months. You can also set it using the UNIX clock daemon cron, which executes commands at a specified date and time.

Note: Although the schedule format is based on the cron, it does not use the UNIX cron command. DA server manages the scheduling.