The Data Compare will compare values in different ways depending on the data types of the columns.
Data Compare will use a simple string comparison:
When you are comparing numeric values, AQT will convert the numeric value to a common format before doing the compare. This common format:
So 1234.00, +1234 will compare successfully to 1234.
When you are comparing numeric columns, AQT has a choice of using a Character Compare or Numeric Compare (as discussed above). Whether AQT will do this is determined by the Numeric Compare Option. This takes values:
In the case of options 1 and 3, AQT will check the values of the column to see if they are numeric. If so, a numeric compare is done, else a character compare is done.
If you are wishing to compare LOB values (such as BLOBs or CLOBs), then it is recommended that you select Compare full LOB values. When this option is selected, AQT will:
This will result in a complete compare of the LOB values. The difference of even a single bit will result in the compare being unsuccessful.
Date values are complicated as there are a number of different date data types - Date, Datetime and Timestamp. In addition, different databases have different variations on the date data types.
To enable the different date values to be compared AQT will convert the date values to a common format, which is:
This means that the following values will be compared to be the same value:
However a DB2 Timestamp of 2008-08-04-16:37:23.000000 will not compare successfully to a DB2 Date of 2008-08-04 as the time part of the timestamp value is non-zero.
The timestamp scale is the sub-second part of the timestamp value. In the case of the value 2008-08-04-16:37:23.123456 the scale is 123456.
When timestamps are compared, the scales must match. For instance:
Oracle Timestamp |
DB2 Timestamp |
|
2008-08-04-16:37:23 |
2008-08-04-16:37:23.000000 |
Compares OK (as the Oracle value has an implicit scale of 000000) |
2008-08-04-16:37:23 |
2008-08-04-16:37:23.123456 |
Not the same (as the scales are different) |
However, if you specify Ignore diff in timestamp scale... |
||
2008-08-04-16:37:23 |
2008-08-04-16:37:23.123456 |
Compares OK (as the differences in timestamp scale is ignored) |
Note that the scale will only be ignored when the scale is a different length between the two databases. So, if you are comparing two DB2 timestamp values, they much match exactly (including the scale) even when Ignore diff in timestamp scale is specfied.
This covers the case of comparing a character column to a numeric column, or a character column to a date column.
In both these cases the numeric or date value will be converted to its common format, then this compared to the string value. So: