Previous Topic

Next Topic

Book Contents

Book Index

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:

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.

Advanced Query Tool
https://www.querytool.com
© 2023 Cardett Associates Ltd. All rights reserved.