Compare table definitions across two systems

With AQT v7 we introduced the Compare Objects function, which is part of the Extended Edition of AQT.

If you are not licensed for the Extended Edition you may still use this simpler, quicker compare.

At large sites it is common to have the same tables defined either in multiple databases (development, test, production etc) OR in multiple schemas within the same database. Often one needs to know whether these tables have the same definition, or, if not, to find out the differences.

AQT provides a powerful tool for doing this comparison. Note that this just compares the definition of the tables across two databases or schemas. If you want to compare the data between two tables see Comparing Table Data.

There are two functions for comparing table definitions:

• Compare to another table. You would use this option for comparing the definition of the currently-selected table to some other table. The other table can be of any name.
• Compare all tables in schema. You would use this option if you wished to compare the definition of a large number of tables (for instance all the tables in a schema) to the tables in another schema and/or database. This option requires that the tables have the same names in the two schemas.

To run these functions, from the Database Explorer, click on Tools > Compare, or click on the Compare icon.

These functions are not restricted to tables, but can also compare the column definitions of Views, Synonyms, Aliases and AS400 Logicals/Physicals.

Problem with DB2 z/OS

There is a known problem when comparing DB2 z/OS tables to tables on a non-DB2 z/OS platform. AQT obtains the list of tables to compare by running a query such as select name from systables order by 1. However if your table names contain numbers, DB2 z/OS will return this list of tables in a different order to non-z/OS systems. This is due to the difference in collating orders between mainframe and non-mainframe systems. This will cause the AQT table-compare function to think that the some tables exist on one system but not the other (and vica-versa), when in fact the tables exist on both systems.

At this stage we do not have a resolution to this problem.

Use with HP SQL/MX

There are some limitations with using Compare all tables in schema with HP SQL/MX. See Database Notes > HP SQL/MX for more on this.