Data Analysis & Visualization
Comparing all Tables in Schema
The Compare all Tables in Schema feature allows you to compare all the tables in a schema to tables in another schema or database in a single operation.
You can also generate a script to resync the contents of the tables.
Specify the following:
Compare tables in schema. You specify here the schema/database containing the tables you are comparing.
To tables in schema. You specify here where the schema/database the tables will be compared to.
Match Tables. This field is used to match the names of the tables being compared. This is described later.
Selecting the Tables to be compared
Ctrl+A will select all tables to be compared
Ctrl+N will select no tables
Filter can be used to select a set of tables
Ctrl+L will select the tables used in the last compare. This is useful if you are rerunning the compare and the tables have been de-selected.
Match tables
The Match tables field is used to "match" the tables being compared.
If the names of your compare and compare-to tables are the same, then use a Match tables of <name> (this is the default). Then, for example, AQT will compare table SALES it the compare schema to table SALES in the compare-to schema.
However, in many cases these might be different. For instance, the tables in the compare-to schema might all be prefixed with "TEST_". In this case you want table SALES in the compare schema to be compared to table TEST_SALES in the compare-to schema. In this case specify a Match tables of TEST_<name>.
When you specify a value for Match tables, the Table2 column in the grid will be reloaded, and you will get a message as to whether the table exists in the schema.
The syntax for specifying Match tables is the same used in the Data Loader mapping.
Tables MUST have a Unique Key
The compare requires that all tables have a unique key. This can either be a primary key, unique index or user-defined key.
The compare will not be run for any table that does not have a unique key.
Compare Tables only
By default, compare will only compare tables (and not views, aliases etc).
If you wish to compare views as well as tables, deselect the Compare Tables only checkbox. The table list will now contain both tables and views.
As (for most databases) views cannot have a primary key or unique index, you will only be able to compare a view if a user-defined key has been defined for it.
Compare Options
The Compare all Tables in Schema function uses the Data Compare. On the Options tab you can specify the options used for the compare. These options are the same as those described in the Data Compare.
Running Multiple Compare in Batch
Compare all Tables in Schema can be run in batch (unattended mode).
To create a script statement to run the compare, use File > View Compare Script. Note that the script statement will include the tables you have selected to compare, so ensure that this is selected correctly before clicking on View Compare Script.
The script statement can be run from either the Run SQL window or included as part of a batch script.
Generating a Resync Script
You can generate a script to resync the contents of the tables. The options for doing this are on the Options tab.
As with Data Compare, you have the option generating the script to:
make the Compare tables the same as the Compare-To tables.
make the Compare-To tables the same as the Compare tables.
Resync Script File Name
You have the option of a creating either
a single script file with the changes to all the tables or
a separate script file per table.
To have a separate resync file per table, de-select the Use a single Script file? checkbox. AQT will change the script file name to include <tname> in the file name.
When the compare is run, AQT will substitute the table name where <tname> has been coded. You can also specify <schema> to include the schema in the file name.
Seperate Resync Files for Updates / Inserts / Deletes
You the option Generate upd/insert/del as seperate files to have updates / inserts / deletes statements to be written to separate files.
This will be done in addition to the "main" resync file with all the changes.
Report or Script File
When the compare finishes, you can automatically be shown the Report and/or Resync script files. These are specified on the Show Report file and Show Script file options.
No means you will not be shown the file when the compare completes
Prompt means you will be prompted to see the file when the compare completes
Yes means you will be shown the file when the compare completes.
Alternatively, once the Compare has been run:
If you have a single resync script, you will get a button at the bottom of the window View Script
If you have a separate resync script file per table, you can view the resync script by:
click on the entry in the grid
click on View > Script File
Note that this option will only be enabled if a resync script has been created for that table. This will only happen if AQT has found some differences between the tables.
Running the Resync Script File
When the compare has finished, you can automatically run the script file to resync the contents of the tables. This is particularly useful when running the compare in batch / unattended mode to resync the two schemas.
The option Run Script file specifies whether the script is to be automatically run once the compare has completed. This takes values No / Prompt / Yes as described earlier. Note:
when running the compare as a batch script, Prompt will be interpretted as No. You must specify this as Yes to run have the script run.
if you Pause the compare, the script will not be run.
Alternatively, once the Compare has been run:
If you have a single resync script, you will get a button at the bottom of the window Run Script
If you have a separate resync script file per table, you can run the resync script by:
click on the entry in the grid
click on Run Script File on the panel that is displayed
Start with new Script File
On the main compare page there is an option Start with new Script file. This is used when you are using the single script file option.
By default this option is checked, so AQT will clear the contents of the script file before the compare is run. If you Pause the compare, this option will be de-selected, so AQT will append to the script file when the compare is resumed.
Running the Compare
To run the Compare, click on the Compare button.
click on Abort to terminate the current compare
click on Pause to stop the compare after the current compare completes
As the compare runs you will be shown the progress of the compare. In this:
Read1 - the number of rows read from the Compare table
Read2 - the number of rows read from the Compare-to table
OK - the number of rows which are the same
Not in Tab1 - the number of rows in the Compare-to table which are not in the Compare table
Not in Tab2 - the number of rows in the Compare table which are not in the Compare table
Diff - the number of rows which are in both tables but are different
The Compare will create a number of files
These are:
Report File. This will have a summary of all the compares.
Compare Report. By default there is one of these created per table compared. They will contain details of the compare and the differences found.
Resync Script Files. These will be created if you specify that a resync script is to be generated. This is discussed in Generating a Resync Script.
The names and directories for these files are specified on the Options tab.
If you are comparing a lot of tables, a many files can be created. It is recommended that a separate directory is used to hold these files. AQT doesn't delete files from this directory, so you may wish to clean files from this directory on a periodic basis.
Once the Compare has completed
Click on:
View Report File. This will show you the report file, which has a summary of the compare.
View Script. This button will appear if you are generating a resync script, and you have selected to use a single script file.
Run Script. This button will appear in the same circumstances as View Script. This will run the resync script.
When you click on a row in the grid another panel will be displayed. You will have options:
View Compare Report - this will show you a report of this the errors the load encountered
View Script File. This button will appear if you are generating a resync script, and you have selected to use a separate script file per table.
Run Script File. This button will appear in the same circumstances as View Script. This will run the resync script.
You can use the Reset button to return the window to it's original state to rerun the compare.
Table Selection
As you run the compare, the tables are de-selected once they have been compared. This is so that they will not be compared a second time if you resume the compare after it has been paused.
Once the compare has completed, all tables will be de-selected. If you wish to run the compare a second time, click on Select > Select Tables used in last compare (or Ctrl+L).
Alternatively, if you select the option Select > Retain selection after compare run the tables will not be de-selected as the compare is run. In this case you need to be careful not to rerun these compares after a pause.