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

  • /*+ … */ - Insert optional Cirro Hint

    • COMPARE_ENGINE - Define the engine for the comparison

    • FORCE_BATCHSIZE (n) - Use n batches of INSERTs to the log table.

    • FORCE_FETCHSIZE (n) - The size of fetches from both source and target.

    • FORCE_RUNMODE - used with COMPARE without SAVE, FORCE_RUNMODE puts the temporary table on the given system and 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.

    • FORCE_TRANSFERTHREADS (n) - used with COMPARE TO, 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.

  • 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.

  • SAVE TO - save results to a target_clog or as a name.

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

  • The key or combination of key columns in the compare statement should be unique.

  • For the purposes of COMPARE, NULL values are not considered unique to other NULL values.

  • Diff column is an INT that encodes the action to take on the target to make it match the source. It takes the following values:

    • -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. The default is 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