The Data Compare doesn't work very well if you are comparing between databases which have different collating orders. When this is the case, the databases can return the data in different orders, even though the same query (select * from table order by pkcols) is run against them. This will cause AQT's merge-match processing to think the tables are different, when they may be the same.
We do not have a generic solution to this problem. However the following comments may be of some help.
The Two-Pass Compare can help to compare these tables more successfully, however this is option is only suitable if a small number of rows do not compare successfully
For Oracle, there are two ways you can specify the order in which rows are returned.
alter session set nls_sort=ascii7
This can be used to ensure that both databases involved in the compare return the data in the same order.
select * from table order by NLSSORT(
pkcol, 'NLS_SORT = ascii7'
For DB2 for z/OS, you can return the data in a particular collating sequence by two methods. In both of these cases you need to specify that you are comparing a Query rather than a Table.
select cast(pkcol as varchar(20) CCSID ASCII) as pkcol1, col2, col3 etc from table order by 1
select * from table order by COLLATION_KEY(pkcol, 'UCA400R1_LEN_S3')
Similar to the above:
For SQL Server you can specify a collation with:
select * from table order by pkcols COLLATE collation-name
You can get a list of valid collation-names with the following query:
SELECT * FROM sys.fn_helpcollations() WHERE name NOT LIKE 'SQL%'
A collation of Latin1_General_100_BIN after works well.
This option appears on the More Options tab.
This option makes it easy for you to amend the ORDER BY statement with a particular collation function. Examples:
When this is specified, this function is applied to all character columns in the key. The column name is substituted wherever a colon appears in this function.
This option allows you to use these functions in the Order By clause and continue to compare a Table rather than a Query (this is useful if you are also wishing to use Two Pass Compare, as Two Pass Compare cannot be used when you are comparing a query).