Product: | Syniti Data Replication, DBMoto |
Version: | Syniti Data Replication 9.6 and above, DBMoto 8 and above |
ID: | 3001 |
Summary: | Database sort sequence differences cause mismatched data in the Verifier |
Sometimes issues can occur when using the Verifier because it uses a sort on the primary key field to identify and compare records. If the target table primary key is missing, or if the primary key is not numeric, mismatches can occur between source and target records because of the database's default sort order. The information below provides suggestions on how to overcome this issue.
A basic requirement for using the Verifier to compare source and target tables after replication is to have a primary key set on the target table (in addition to the one required for replication on a source table.) This ensures that source and target rows are correctly compared.
If there is no primary key on the target table, the comparison between source and target may not be accurate because the sorting mechanism for each DBMS can be different. Additionally, in some cases where the primary key has been defined, the sorting mechanism still causes differences to be found. To manage this issue, the Verifier offers settings to override the default ORDER BY clause. For example, Informix and MS SQL Server use different tables internally to order the data when running an ORDER BY clause. The Verifier performs a SELECT statement with an ORDER BY clause so records are returned in a different sequence in each databases. This problem is more noticeable when there are special characters in the primary key. Standard characters usually do not show differences.
For SQL Server tables, to solve the problem with special characters, specify the field name and define a collate clause such as "collate SQL_Latin1_General_CP850_BIN" as in the screenshot below.
For IBM Db2 for i (iSeries, AS/400) tables:
- Stop the Replication Agent (Syniti DR) or Data Replicator (DBMoto).
- Right click on the source connection and choose Connection Properties.
- Check the Verifier Sort Sequence Table option.
If it is set up as QSYS/QASCII, remove the value and apply the changes. - Run the Verifier.