Data Compare

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'

Specifying the Columns

You can specify which columns in your tables are to be compared by clicking on the Columns tab. This shows you the columns in your tables / queries.

• on the left grid, select the Compare checkboxes to include or exclude a column from the compare.
• on the right grid, you can move entries up or down. You do this to indicate which column in second table matches the column in the first table.
• the Insert Entry and Delete Entry buttons are useful if you need to add a blank row to the grid. You will need to do this if your first table has a column which doesn't correspond to any column in the second table. In this case you match this column to a blank entry.
• columns which will not be compared are "greyed-out".
• the Key checkbox can be used to specify which columns in the table comprise the unique key

Generate Script

AQT can generate a script to resynchronize the contents of the two tables. In the Generate Script box you can specify whether:

• no script is to be generated
• AQT is to generate a script that makes the Compare table the same as the Compare to table. This script will consist of a number of insert / update / delete statements against the Compare table.
• AQT is to generate a script that makes the Compare to table the same as the Compare table. This script will consist of a number of insert / update / delete statements against the Compare to table.

File specifies the name of the script file that AQT will generate.

Once the compare has been run, you can view the script file by clicking on the View Script button. You can run the script file by clicking on the Run Script button.

If you are comparing queries instead of tables, you cannot generate a script. AQT can generate SQL for inserting / deleting / updating a table, but not for updating a query.

Note: when generating Update and Insert statements, AQT will only include the columns which are being compared. If you exclude a column from the compare, it will not be included in the Update / Insert. Because no matching column has been specified, AQT is unable to know what value to use for the Update / Insert.

Generate the Update / Insert / Delete statements to separate files

By default, the resynchronization script will have all the SQL statements in a single file.

Sometimes it can be useful to have the Update / Insert / Delete statements in separate files - this is can be needed if you are resynchronizing a set of tables that have foreign keys defined between them. In this case the update / inserts / deletes for the various tables have to be run in a particular order.

To do this, go to the More Options tab and click on Generate Updates, Inserts, Deletes to separate files. AQT will write the update, insert, delete statements to the specified files, as well as to the "main" resynchronization script file.

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 batch report
• 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 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.

Order-By Mask

This option appears on the More Options tab. It is discussed in the section Collation Order Problem.

Running the Compare

To run the Compare click on the Compare button. To stop the compare, click on Abort.

Once the compare has completed, you will be shown the compare results on the Results tab.

• the two tables are shown side-by-side. Key columns are shown as black-text on grey-background. Columns which are not compared are shown as grey-text on white-background.
• the icon on the left shows you whether that row was compared OK, is different, or is not present in one of the tables.
• values which are different between the two tables are shown in yellow.
• as you scroll through the tables, the other table will scroll with it.
• as you click on cell, AQT will show you, in the status bar, the value of the cell in the other table.
• if you double-click a cell (or right-click and select View), you will be taken to the Data Compare Detail window. This shows you the two values side-by-side for easy comparison of the values.
• if you have a LOB-file, you can right-click and select View as to view the LOB value in an external application.
• if you are running a large compare, it will run faster if you use option only show that are different. This will reduce the overhead involved in writting the data to this grids.
• you can print a grid with File > Print Grid (or Ctrl+P). This will print the grid you have last clicked-on.
• you can copy a set of cells by selecting them and hitting Edit > Copy Cells (or Ctrl+C). You can select all cells in a grid with Edit > Select All (or Ctrl+A).
• if you have many rows in the grid, you can easily move to the rows that don't match by hitting Edit > Find non-matching row (or F3).

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.