Compare the rows of the target cursor or table to those of the source cursor or table.

Syntax

COMPARE [/*+ … */] target TO source [WITH KEY (col1,...)] [SAVE [TO target_clog] [AS 'name']]

Arguments

Hint Description Used with
FORCE_BATCHSIZE(N) The size of batches of INSERTs to the log table.  
FORCE_FETCHSIZE(N) The size of fetches from both source and target.  
[FORCE_TRANSFERTHREADS] Used only for COMPARE or SYNC. The number of transfer threads of execution that will be used to move data from place to place in that part of the query structure. FORCE_TRANSFERTHREADS will cause the compare engine to use N difference loggers. COMPARE TO
FORCE_SORT({TRUE|FALSE}) Use the sorted or unsorted compare engine.  
FORCE_RUNMODE Puts the temporary table on the given system. Forces the optimizer to move query data to and then execute the query on a user-specified system rather than a system selected by the optimizer. COMPARE without SAVE
  • source - source datasource in form system.schema.database

  • target - target datasource in form system.schema.database

  • WITH KEY

    • The sync actions are performed on the target table matching the source table or cursor.

    • Rows are matched on the Key columns of the target.

    • If the KEY is not given, SYNC searches for the table’s primary key.

Description

  • This statement compares the records in the target table or query with those in the source table or query, matching records on the key.

  • WITH KEY - If the KEY is not given for a table COMPARE looks for the table’s primary key.

    • N.B. for a source query, the KEY must be given.

Results

The result is a set of rows with a structure identical to the compared rows with the addition of a diff column.

Additional Information

  • Diff column

    • The diff column encodes the action to take on the target to make it match the source.

    • It is an INT taking the following values:

Value Description
-1 The target row must be deleted from the target to match the source.
0 The target row must be updated in the target to match the source.
+1 the row must be inserted into the target to match the source.

The result set may be saved to a table, by default target_CLOG.

  • target_CLOG - target_CLOG has an additional VARCHAR column, compare_name.

    • compare_name is populated with the time in ISO format.

    • The table will be created if it does not exist and the results will be appended if it does.

  • For updates:

    • columns that are identical will be NULL in the result set

    • only the values to set on update will be not null.

    • NULL update values will be flagged using a special indicator.

Examples

Example tables are drawn from the imdb database, for instance:

  • cast_info (id, person_id, movie_id, person_role_id, note, nr_order, role_id)

  • movie_keyword (id, movie_id, keyword_id)

  • company_name(id, name, country_code, imdb_id, name_pcode_nf, name_pcode_sf)

The target has a primary key.

COMPARE lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword ;

Use all the columns as the comparison key.

COMPARE lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword WITH KEY (*) ;

Compare based on a non-primary key.

COMPARE lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword WITH KEY (keyword, phonetic_code) ;

Save the result of the compare to a permanent table, lim.imdb.public.keyword_clog.

COMPARE lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword SAVE;

Save the compare results to a named table.

COMPARE lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword SAVE TO lms.cirrodb.dbo.keyword_clog ;

Save the compare results with a specific name.

COMPARE lim.imdb.public.keyword TO lms.cirrodb.dbo.keyword SAVE AS 'Post-run check #1' ;

See Also