Table Data Comparer

<< Database Comparer | IBExpert | Log Manager >>

tutorial available

General
Options
Log
IBEBlock

The Table Data Comparer can be found in the IBExpert Tools menu. It allows you to compare data of two tables in different databases and obtain a script detailing all discrepancies which includes corresponding INSERT, UPDATE and DELETE statements. And since IBExpert version 2021.02.09 it is also possible to compare system (RDB$*) tables.

This feature is unfortunately not included in the free IBExpert Personal Edition.

General

The General page displays the default file path and name for the resulting comparison script. This can of course be altered as wished.

The first step is to select the Master or Reference Database from the drop-down list of all registered databases. This is the reference database, to which the second database and its table(s) are to be compared. Then select the Target Database, i.e. the database whose table(s) need to be assessed and altered in order to conform with the reference database and table(s). The databases and tables must already exist.

The Online synchronization feature allows you to update the target database simultaneously with the comparison process. Online synchronization is performed in a separate thread. If the result file name is specified, IBExpert also creates script file. If you only need online synchronization, leave the script file name editor empty.

Then select the tables to be compared. Tables with the same name in both databases are listed next to each other in the Tables to be compared list. If you wish to compare tables with different names, click the arrow to the right of the table field and select the desired table from the list of all tables in this database. Tables with different names must have the same structure. An error is raised if there is no primary key defined for the reference table.

Temporary tables (GTT) are not ibncluded in the tables selection list.

To select all tables use the right-click context-sensitive menu. As you will see in the illustration below, system tables are not selected when using this function.

Selected generators/sequences can also be synchronized as part of the table comparison. And the connection role can be used if specified.

If you wish you can save your current settings into a file and load previously saved settings from file using the relevant toolbar icons.

Options

The Options page allows:

Selection of INSERT, UPDATE or DELETE records.
Option to include milliseconds into time/timestamp values.
The options Use ALTER SEQUENCE instead of SET GENERATOR and Use UPDATE OR INSERT instead of INSERT/UPDATE are relevant for Firebird 2.0 and Firebird 2.1 respectively.
Compare blobs using the Firebird HASH function: this option will be ignored if one of the servers of the source or target database doesn't support the HASH function.
Put INSERT/UPDATE/DELETE statements into Firebird blocks (EXECUTE BLOCK): this will not affect statements which contain parameters to load data from a blob file.
Commit each block and Commit after specified number of records options.
Put BLOB values into script as hex strings: if this option is enabled the values of blob fields will be written directly into the result script in hexadecimal notation, i.e.

       UPDATE MYTABLE SET BLOB_FIELD = x'4E657276656E';

Hexadecimal notation for binary values is available in Firebird 2.5 and Firebird 3. The length of the binary value which may be encoded in hexadecimal notation is limited to 32765 bytes in Firebird 2.5 and to 65535 bytes in Firebird 3. If the blob value exceeds the limit it will be written in the lob-file as is.

Include old values of fields (for UPDATE statements only) option: If this option is enabled, the old values of fields (for UPDATE statements) will be included into the result script as comments:

      UPDATE TDC_DATA
          SET INT_DATA = 57555,
              /* OLD.INT_DATA = 91186 */
              STR_DATA = 'some string value',
              /* OLD.STR_DATA = '' */
              STR_DATA2 = 'bla, bla',
              /* OLD.STR_DATA (trimmed) = 'comment...' */
              BLOB_DATA = :h15A73_4D
              /* OLD.BLOB_DATA = (BLOB) */
          WHERE (ID = 871);

(trimmed) means that the old value contains a '*/' sequence which cannot be shown within a comment: IBExpert trims the rest of string and shows '...' instead.

* Use UNIQUE constraint in case of missing PRIMARY KEY constraint: implemented in IBExpert version 2021.02.09. For system (RDB$*) tables UNIQUE constraints will be used automatically, for user tables this option should be enabled manually if it is really necessary.
Note: If two or more UNIQUE constraints are defined for a table, a random constraint will be used to identify records.

* Synchronisation queue limit: It is possible to limit the queue by the number of statements in it or by memory occupied by the queue. A positive value means limitation by number of statements. A negative value means limitation by memory size (in megabytes). Default value is 0 - no limits.

* If the Ignore syncronization errors option is ON all INSERT/UPDATE/DELETE errors during online synchronization are ignored, allowing an attempt to execute all statements in the synchronization queue.

Note: Online synchronization ignores DML-statements generated for system tables.

To start the table comparison simply click the Compare button (green arrow) or [F9].

Log

The Table Data Comparer resolves dependencies between master and detail tables while creating the script.

The resulting log:

displays whether the database connections were successful, records searched, time taken and the number of discrepancies found. The resulting script file may then be loaded into the Script Executive if wished.

If the target database ODS version is equal to or greater than 11.1 string values with the character set OCTETS will be presented in the result script in hex representation (e.g. x'7D81AE05CB'). OCTETS character set strings (CHAR/VARCHAR) are written into a LOB file if it is not possible to convert them into hexadecimal format.