Generate Resync Script

AQT can generate a script to resynchronize the contents of the two tables. Options for this are specified on the Script Options tab.

Generate Resync Script

In the Generate Resync 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.

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

Ensure lines are less than 72 bytes. This option is useful when generating SQL scripts for DB2 z/OS.

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.

View / Run the Resync script

These options are specified in Running the Resync Script.

Resyncing a Query

If you are comparing a query (rather than a table), you can only resync this if it is an Updateable Query. This is one which:

• only queries a single table
• isn't a summary query

This is the limitation of (most) databases, rather than AQT.

Which Statements to Generate

These options specify which SQL statements to include in the script. These allow you to control what categories of changes are to be applied to the target table.

By default AQT will include update, insert and delete statements. You may only wish to de-select deletes, for instance, if you only want updates and inserts applied to the target table. This can be used to avoid inadvertent deletion of data.

Generate Updates as Delete / Inserts. When this option is selected, AQT will generate an update statement as a delete followed by an insert. This is useful in circumstances when foreign key relationships on a table prevent an update from happening. In this case a delete/insert may work more successfully.

When this option is used, deletes and inserts statements will be created even if the insert and delete options are de-selected. The delete/insert can be considered as an update.

Bi-directional Resync

These options allow you to perform a bi-directional resync, which can be useful in some circumstances. For instance you might have an environment where rows are added to two tables in different databases and/or sites, and deletes/updates are not done. You may wish to resync the tables so they both contain all the rows. This requirement can be difficult to achieve by other means, however is easy with the Data Compare:

• specify that only inserts are included in the script
• resync TableA to TableB
• resync TableB to TableA

After this, both tables will then have all the rows.

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.

Only write to these files (and not to the main script file)

When this option is selected, AQT will only write to the three update/insert/delete files, and not to the main script file.