This page gives background information on Compare syntax.

Compare Engines

The COMPARE_ENGINE hint defines the engine to use in any COMPARE statement. There are four engines which execute source and target queries, process resulting records and log differences.

  • SIMPLESORT - A Single-threaded sorted compare.

  • ASYNCSORT - An Asynchronous sorted compare, sorted with single-threaded read but asynchronous, multi-threaded difference handling.

  • SYMMETRICHASH - Symmetric hash is unsorted with asynchronous, multi-threaded read and difference handling.

  • OUTERJOIN - Outer join, unsorted compare with full read of source and asynchronous, multi-threaded lookup of target.

!!! ORIGINAL COMPARE ENGINE DETAILS !!!

There are three distinct engines that can be used. These each execute the source and target queries, process the resulting records and log differences. However, the strategy for each engine is distinct.

Single-threaded, sorted

  • The source and target queries include an ORDER-BY, forcing the databases to perform a sort.

  • After both statements have been executed, the engine steps through the result sets checking the values.

  • Any difference is inserted into the log table in the same thread.

  • This engine can be very effective when the queries return quickly and few differences need to be logged.

  • To force this engine to be used, add the hint FORCE_TRANSFERTHREADS(1).

Sorted with single-threaded read but asynchronous, multi-threaded difference handling

  • The source and target queries include an ORDER-BY, forcing the databases to perform a sort.

  • After both statements have been executed, the engine steps through the result sets checking the key values. Whether the keys match or not, an item is put on a queue.

  • In separate threads, consumers process the queue. If there are both source and target records, the non-key values are checked. Any differences are logged.

  • This engine can be scaled according to the number of differences expected. The hint FORCE_TRANSFERTHREADS(N) will cause N consumer threads to be used.

  • Large, easily sorted tables can also handled by scaling the number of consumers. This is the default engine, with one consumer thread.

Unsorted with asynchronous, multi-threaded read and difference handling

  • Each query, without any ORDER-BY clause, is executed its own thread.

  • As the record is read, a hash table is checked for a matching key from the other reader thread.

    • If found, the has table entry is removed and both source and target records are put on a queue for processing.

    • If not found, the record is put into the hash table.

  • Once the readers have finished, any records in the hash table are queued.

  • In separate threads, consumers process the queue. If there are both source and target records, the non-key values are checked. Any differences are logged.

  • Because the queries execute in parallel, this engine can be useful for queries that take longer to return their first records.

  • This engine is an option when the database tables cannot be sorted in a reasonable time. However, performance should be improved by even imperfectly sorted data.

  • To force this engine to be used, add the hint FORCE_SORT(FALSE).

  • The hint FORCE_TRANSFERTHREADS(N) will cause N consumer threads to be used.

Sorted compares utilize the underlying databases to perform sorts. The performance of these sorts will depend on the properties of the table and database: index on the sort key, database memory,…

Unsorted compares do not ask the databases to sort, i.e. the source and target queries have no ORDER-BY clause. However, the hash table it uses internally can consume significant memory in the Cirro RDBMS agent, so this must be sized accordingly.

JDBC Types

JDBC types handled as themselves include:

The JDBC types that are handled as alternate types:

JDBC Type Handled As
BIT Treated as BOOLEAN or VARBINARY depending on length. (In Java, Boolean or ByteBuffer.)
BIGINT BIGINT
BOOLEAN BOOLEAN
TINYINT, SMALLINT, INTEGER All treated as Types.INTEGER (Integer)
REAL, ORACLE_BINARY_FLOAT All treated as REAL. (Float)
FLOAT, DOUBLE, ORACLE_BINARY_DOUBLE All treated as DOUBLE. (Double)
NUMERIC, DECIMAL Both treated as BIGINT if scale is zero; or DECIMAL. (Long or BigDecimal)
CHAR, VARCHAR, LONGVARCHAR, NCHAR, NVARCHAR, LONGNVARCHAR All treated as VARCHAR. (String)
DATE (Date), TIME (Time), TIMESTAMP, TIME_WITH_TIMEZONE, TIMESTAMP_WITH_TIMEZONE  
All treated as TIMESTAMP. (Timestamp)  
BINARY, VARBINARY Both treated as VARBINARY. (ByteBuffer)
  • The mapped type is used for data value comparisons and for any INSERT.

  • The log table creation does not use these types.

JDBC types not handled

Several JDBC types are NOT handled. These are:

  • OTHER

  • JAVA_OBJECT

  • DISTINCT

  • STRUCT

  • ARRAY

  • BLOB

  • CLOB

  • REF

  • DATALINK

  • ROWID

  • NCLOB

  • SQLXML

  • REF_CURSOR

See Also