The comparison and synchronization tool has a few options. Out of the box these settings have some optimal values for various cases. However the user can get more with the same tool by managing them. In the article we'll describe a few problems and how to resolve them with mentioned options.
I. Extra Spaces
Different value length or different number of trailing spaces is a usual case. For example, if the user compares CHAR(5) and VARCHAR(5) columns with the same length he or she can’t see differences between 'ABC ' and 'ABC' by default. The data comparer offers "show spaces as dot" for this case. The option applicable to main comparison data grid and to face-to-face viewer for two rows comparison. Please note that the user be able to view column data type as tooltip of the column header.
The related option named "remove trim spaces". The comparison software removes all spaces from end of value if the option is switched on. In our example it will truncate 'ABC ' to 'ABC' before comparison.
II. Extra Quoting
Sometime the database value contains extra quoting. The most popular reason is import "as is" from CSV files or something like that. Therefore the user have to compare clear value with quoted values: 'ABC' with ABC. DTM Data Comparer has "ignore quotation for string data" option. The tool removes quote chars from both sides of value before comparison.
III. NULL Value Comparison
There are different presentation of non-existing value in the different databases, data files and other sources. For example, NULL is a good practice for regular databases but empty string can be used for same purpose in the text file.
The second problem is domain depended. Does one empty (or NULL value) equal to another empty value or not? For some cases like "Last Name" the empty string are different because we can't compare persons by the first name only. But for non-significant columns any NULL should be equal to another NULL.
Our comparison software has two options for NULL value management. The first is "empty strings equals NULL value" and the second is "NULL not equals to another NULL value". These options covers mentioned problems and allows the user to tune not-existing value comparison.
IV. Synchronization Database and Integrity
There are three modes of the data synchronization process. In the first one the program ignores errors, writes information to log file and complete script. The user enabled to analyze problems, fix sources and rerun synchronization. In the second mode DTM Data Comparer stops synchronization on any error. The third mode is same as the second but the software rollbacks changed values to original state.
To manage the mode users can switch two options: "stop synchronization on the first error" and "rollback on error". Please note that the second options disables "write COMMIT statements to script" feature due to transaction compatibility reasons.