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

Oracle

For Oracle, there are two ways you can specify the order in which rows are returned.

• The NLS_SORT session parameter specifies the collation order used for sorts. You can set this with a statement such as the following (in the Run SQL window):

  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.

• Alternatively, you can specify that the compare uses a Query (rather than a Table) and use a NLSSORT function on the ORDER BY statement. Example:

  select * from table order by NLSSORT(pkcol, 'NLS_SORT = ascii7')

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.

• You the CAST function to cast your primary-key columns into a different collation sequence. Example:

  select cast(pkcol as varchar(20) CCSID ASCII) as pkcol1, col2, col3 etc from table order by 1

• If you are running DB2 for z/OS v9 or later you can use the COLLATION_KEY function on the ORDER BY. Example:

  select * from table order by COLLATION_KEY(pkcol, 'UCA400R1_LEN_S3')

DB2/UDB

Similar to the above:

• you can use the CAST method for returning rows in a particular order.
• for DB2/UDB v9.5 or higher, there is a COLLATION_KEY_BIT function. This is similar to the COLLATION_KEY function in the above example.

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:

• NLSSORT(:, 'NLS_SORT = ascii7')
• COLLATION_KEY(:, 'UCA400R1_LEN_S3')

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