Previous Topic

Next Topic

Book Contents

Book Index

Collation Order problem with Data Compare

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.

Two Pass Compare

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.

DB2 for z/OS

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.


Similar to the above:

SQL Server

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.

Order-By Mask

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).