The Data Compare tool allows you to compare the data between two tables, views or queries. It can be invoked from the Database Explorer by clicking on Tools > Compare Data, or the Compare Data icon.
The Data Compare tool is part of the Extended Edition of AQT, which is a separately purchasable component of AQT. If you have not purchased the Extended Edition you can still use Data Compare, however you will be limited to comparing 100 rows.
Data Compare has the following features:
you can compare either tables, views, or the result of queries
the tables can be in the same or different databases
you can generate a script to resynchronize the contents of the two tables
Data Compare can be run in batch mode
Specifying the Tables or Queries
You use the Compare and Compare To boxes to specify the tables or queries to be compared. Click on the three-dots button to select a table, or type the table name into the Table text-box.
Unique Key
In order to compare tables, the tables must have a column (or set of columns) that uniquely identifies each row in the tables. This is used to match the rows from the two tables. AQT will get this key for you automatically if your table has a primary key, unique key, or user-defined key. Otherwise you will need to specify this.
If the table doesn't have a primary key, or you are comparing a query, you can specify which columns comprise the unique key in two ways:
on the Options tab, clicking on the box just right of the Unique Key box.
on the Columns tab, checking the Key checkbox
If you are comparing a query, the query must return the data in the order of the unique key columns (eg. must have an order by keycol1, keycol2 clause).
Filter Rows
The Filter Rows is used to apply a search condition to your table. You would use this to exclude rows that you do not wish to be included in the Compare.
Code this as a Where clause but without the Where keyword. Examples:
cust_code is not null
order_number > 0 and order_date > '1998-02-05'
Report File
AQT can generate a report file giving a text-based report of the Data Compare. The report file is mainly useful when running the Data Compare in batch.
you can specify whether a report file it to be produced by selecting Generate report file
the report file defaults to datacomp_report.txt in your query directory. You can change this to another file by clicking on the button with three dots.
you can browse the report file by clicking on View
if you select Show report file when compare completes, AQT will automatically show you the report file once the compare has finished.
Other Options
only show rows that are different. When this option is selected, AQT will only display (in the Results tab) the rows which are different. By default, AQT will show all rows from both tables.
compare full LOB Values. By default, AQT only compares the first 10000 bytes of blob columns. When this option is selected, AQT will do a full compare of the LOB values. It does this by writing the LOBs as files, then doing a bit-by-bit compare of the LOB files.
ignore case. When this option is checked, AQT will do a case-insensitive compare of values (eg. 'smith' and 'Smith' will be deemed to be the same value).
treat zero length strings as nulls. When this option is selected, AQT will consider a zero-length string and NULL to be the same.
Max rows. This specifies the maximum rows to compare. If zero is specified, AQT will compare the entire tables. If you are in evaluation mode of the Extended Edition of AQT, this will be set to 100.
Stop after. Specifies that the compare is to stop after a given number of differences have been found.
Append to Script / Report files. When this option is selected, AQT will append to the script and report files. This is useful when running multiple compare in batch-mode.
Ignore diff in timestamp scale. When this is used, differences in the scale (sub-second) part of timestamp values is ignored. This is useful for comparing timestamp columns between different database types. This is discussed in more detail in How the Data Compare compare values.
Numeric Compare option. This specifies how numeric values will be compared. See How the Data Compare compares values for information on the values for this.
Order-By Mask
This option appears on the More Options tab. It is discussed in the section Collation Order Problem.
Saving / Retrieving Compare Specifications
You can save or retrieve your options for a data compare. This is done with File > Save Compare Options and File > Open Compare Options.
Other notes
There is a small oddity when comparing queries. If you have the same column in a query more than once, AQT will only compare the first of these columns. This is due to the way AQT matches the columns-to-compare, to the columns in the query. This is not a major problem, as having a column more than once in a query is something you would generally not wish to do.
There are some limitations with using Data Compare with HP SQL/MX. See Database Notes > HP SQL/MX for more on this.
