How the Data Compare compares values

The Data Compare will compare values in different ways depending on the data types of the columns.

Character Columns

Data Compare will use a simple string comparison:

if Ignore Case is specified, AQT will do a case-independent compare - eg. test and TEST will be regarded as being equal.
if you specify Options > Display Options > Remove trailing spaces from string values, the Data Compare will ignore trailing spaces on the strings - eg. 'test' and 'test ' will regarded as being equal.

Numeric Compare

When you are comparing numeric values, AQT will convert the numeric value to a common format before doing the compare. This common format:

removes the + sign at the start
removes any trailing zeros after the decimal point

So 1234.00, +1234 will compare successfully to 1234.

Numeric Compare Option

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:

0. Do not use Numeric Compare (Character Compare will be used). In this case 1234.00 and 1234 will be deemed to be different values.
1. Use Numeric Compare if either of the two columns are defined to be numeric.
2. Use Numeric Compare only if both the columns are defined to be numeric.
3. Always use Numeric Compare. This option will can be used to do a numeric compare on columns even if they are defined as character.

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.

LOB Columns

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:

read the full LOB values, and write the LOB values to files
do a bit-by-bit comparison of the LOB files

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 Columns

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:

in the case if timestamp columns, if the decimal part is 0, it is ignored
in the case of timestamp and datetime columns, if the time part is zero it is ignored.

This means that the following values will be compared to be the same value:

DB2 Timestamp - 2008-08-04-00:00:00.000000
Oracle Datetime 2008-08-04-00:00:00
DB2 Date 2008-08-04

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.

Timestamp scale

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.

Comparing Character to non-Character values

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:

character value '1234' will compare successfully to numeric values 1234.00 and +1234. However the character value '1234.00' will not.
the string value '2008-08-04' will compare successfully to the timestamp value 2008-08-04-00:00:00.000000 . However the character value '2008-08-04-00:00:00.000000' will not.

Advanced Query Tool

https://www.querytool.com