Compare Objects

The Compare Objects tool is used to compare the definition of objects. It is mainly used to compare the definition of tables, however it has been configured to compare some other types of objects.

You run the Compare Objects tool from the Database Explorer using the compare icon on the Admin Toolbar. You will see this icon only if the Compare Objects function has been configured for your database and object.

The Compare Objects 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 Compare Objects, however you will not be able to use the Compare all objects in schema option. Alternatively you may wish to use the "old" compare tables function that was part of AQT v6.

Using Compare Objects

AQT can compare objects other than tables. The description below explains how Compare Objects works for tables.

Compare

This specifies the objects to be compared.

• you can compare either a single object, or all objects in a schema.
• when comparing all objects in a schema, you can specify a Filter to limit the number of objects compared.
• if you are running in evaluation mode, you will only be able to compare a single object to another single object.
• the compare-to object can be in the same or different database to the object being compared.

What to Compare

The What to Compare box shows you all the components that make up the definition of the table. These are:

• the table attributes (tablespace, pagesize, description etc)
• the table columns
• the "related objects" of the table (indexes, primary key, foreign keys etc).

Select or de-select components to specify what to include in the compare.

Each of these components is defined in AQT as a set of parameters. When you click on one of these components (in the left box) you will be shown these parameters in the Parameter for box:

• if the parameter is checked, AQT will compare that parameter
• if the parameter is not checked, AQT will not compare that parameter
• if the parameter has an asterisk * beside the name, the parameter is a compare parameter (this is described later in in How Object Compare works).

In general you should not change the Parameter compare settings from the default values.

Other Options

• For columns, compare by column name. By default, when comparing columns, AQT will compare by column-number (the order it is defined in the table). If you aren't interested in the order of the columns in the table, you can check this option. The columns will then be compared by column name.
• Ignore case when comparing. When this is checked, AQT will ignore the case when comparing values. De-select this if you want to do case-dependent compares.

Report File

AQT can generate a report file that details the differences between the objects. This can be used as a useful summary of the differences.

• Write report file. Specified whether a report file is to be generated
• report file name. Use this to specify the name of the report file. Click on View to view the report file in Notepad (it can be printed from there).
• Only report on differences. When this option is specified, information is written to the report file only for objects that are found to be different.
• Detailed report. This specifies that a detailed report for the objects is to be written.
• Show report when finished. When this option is selected, the report file will be displayed when the compare has completed.

When viewing the report file in Notepad it is recommended that a non-proportional font (such as Courier New or Fixedsys) is used. The information in the report file is aligned in particular columns, so will display better with these fonts.

Compare button

This runs the Compare, and shows you the results on the Results tab. To cancel the compare, click on the Abort button (you may neeed to go back to the Options tab to do this).

Comparing Views, Triggers, Procedures and Functions

These objects are difficult objects to compare, as they are generally defined by a block of text, such as a view or procedure definition. A "simple" compare of this text will show a difference when only a single byte is different; this can often happen due to differences in formatting such as extra blanks or linefeeds. To deal with this, AQT will unformat the text before comparing it. Unformatting the text will remove all linefeeds, tabs and extra blanks. By doing this, AQT is more able to deal with simple differences in the formatting of the definitions.

When you view the compare results, you will be shown the unformatted text in the compare results. This may be less readable than the original view / procedure definition, however is useful for seeing the difference between the definitions.

Viewing the Compare results

These are shown on the Results tab.

This window is divided into three parts, a top grid, and a left and right lower grids:

• thoughout the grids
  • a tick means the object compares successfully
  • a cross indicates that differences were found in the compare
  • a dot indicates that nothing was compared. You will see this for tables that are in one database/schema but not the other.
  • a zero indicates that there were no related objects for that table to compare.

Top grid

This shows you one row per table compared. For each row, there is a column in the grid showing you how each Component of the table compares. These correspond to the components selected in the What to Compare box in the Options tab.

• click on a tick or cross in the top grid to show you details of that compare in the left lower grid.
• clicking on a particular index in the left lower grids will show you details of the indexes in the right lower grid.
• click on View > Only Show Differences to only show rows in the top grid when the objects are different. This is handy of you are comparing a lot of objects, and just want to see the ones that are different.

Left lower grid

Shows details of the item highlighted in the top grid.

In the example of indexes:

• in the the left lower grid you will see the list of the indexes in the two tables.
• For each of these, the OK column shows you whether the index compares successfully or not. The Differences column gives a brief summary of what is different between the two indexes.

Right lower grid

• there is one row in the right lower grid per parameter being compared. This correspond to the Parameters for Index selection in the Options tab.
• the OK column show whether the parameters compare successfully.

How Object Compare works

Compare "related objects" (such as indexes)

When comparing a related object (such as an index), Compare Objects will get the list of all indexes for the two tables. It will then go through these lists of indexes to determine which indexes "match" (eg. are the "same" index in both tables) and which indexes do not "match" (eg. are defined in one table but not the other).

The key to this matching process is the compare parameters. These are the parameters which are used to determine whether two objects are "the same". For instance, when we compare the indexes from two tables, we look at the index-columns - if these are the same then the two indexes are "the same".

The following table shows how some common objects are compared:

Comparing objects across different database types

AQT can compare objects across different database types (eg. Oracle to SQL Server). In this case:

• only parameters that are in common to both databases are compared. For instance, Oracle tables have parameters of Tablespace and Description; SQL Server tables do not; these parameters will therefore be excluded from the compare. They will not appear in the Parameter For box
• when comparing columns, AQT will convert column types between databases. For instance, it will deem Oracle NUMERIC and SQL Server DECIMAL to be the same, Oracle LONG and SQL Server TEXT to be the same.

AQT's Compare between different databases is good but not perfect. There are many small differences between databases that are difficult for the Compare Objects to deal with.

Known Problems

Problem with Collation orders

There is a known problem when comparing databases which have different collation orders. This can apply when comparing (for instance) DB2 z/OS tables to a tables in 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, this list of table can be returned in different orders. This will cause the AQT object compare function to think that the some tables exist on one system but not the other (and vica-versa), when in fact they exist on both systems.

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