Avoiding errors when using the table compare utility with DATE columns

If you use the asntdiff table compare utility to compare tables that have columns with the DATE data type, you can receive error messages or the utility can report false differences when the source and target databases are not bound with the correct DATETIME option.

In this situation, even with the source and target tables are identical, asntdiff can return a value of U 2 in the DIFF column that indicates a difference exists at the target table. You might also receive an ASN4009E error that indicates that the date or timestamp format might be different between the source and target.

To resolve the problem, connect to both databases and rebind asntdiff with the correct DATETIME option. The following examples differ by version:

Version 10.1, Version 9.7 Fix Pack 6, or newer

This example uses a list file rather than individual bind files:

db2 bind @asntdiff.lst DATETIME ISO BLOCKING ALL GRANT PUBLIC

Prior versions

Individual bind files are used:

db2 bind asntdiff.bnd DATETIME ISO BLOCKING ALL GRANT PUBLIC
db2 bind asntdapi.bnd DATETIME ISO BLOCKING ALL GRANT PUBLIC
db2 bind asntdsql.bnd DATETIME ISO BLOCKING ALL GRANT PUBLIC

The only valid value for DATETIME is ISO.