create compareset

Syntax

create compareset compareset_name
[set desc [{to|=}] description]
with source source_connection_name  owner_name 
source_table_name source_alias [where “constraint”]
target target_connection_name owner_name 
target_table_name target_alias [where “constraint”]
[and target target_connection_name owner_name 
target_table_name target_alias [where “constraint”]...] 
 map {all [set strict_column [{to|=}] {true|false}]
[and set strict_key [{to|=}] {true|false}]
[and set strict_type [{to|=}] {true|false}] 
[and set keep_computed [{to|=}] {true|false}]
[and set keep_encrypted [{to|=}] {true|false}]|
source_table_alias.column_name = target_table_alias.column_name
[ = target_table_alias.column_name ...] 
[set key=true ], 
[and source_table_alias.column_name = 
target_table_alias.column_name [ = 
target_table_alias.column_name ...] 
[set key=true]…] }

Parameters

• compareset_name – the name of the compareset to be created.

• description – (optional) description of the agent. Use double quotes if you are using a reserved word or blank spaces.
• source_connection_name – the name of the source connection.
• target_connection_name – the name of the target connection.
• owner_name – the name of the owner for the source or target tables.
• source_table_name – the name of the source table.
• target_table_name – the name of the target table.
• source_alias – the alias that refers to source connection, owner, and table.
• target_alias – the alias that refers to target connection, owner, and table.
• column_name – the name of the column.
• key – specifies whether the column is a primary key or an unique key.
  Note: When you use the map all statement, the compareset key columns are set by examining the primary key, identity and uniquely indexed columns in the source database table.
• strict_column – if set to true, this parameter throws an exception if one or more source columns do not exist in target tables. By default, strict_column is false.
• strict_key – if set to true, this parameter checks the source database table for at least one primary key, identity or uniquely indexed column. If no primary key or a uniquely indexed column is found, the command fails and the compareset is not created. If set to false, this parameter uses all non-LOB columns as compareset key columns. By default, strict_key is false.
  Note: When strict_key is false and all non-LOB columns are used as the compareset key columns, the keys might not be unique. Non-unique keys are detected during the data comparison and this might terminate the data comparsion prematurely.
• strict_type – if set to true, this parameter checks the type name, scale, and precision. By default, strict_type is false.
  Note: The strict_type, strict_column, strict_key, keep_computed, and keep_encrypted are used only when you use the map all statement, which requires the source and target databases to be online; while for explicit mappings, databases need not be online.

Examples

• Example 1 – creates a new compareset named “cust_orders”:

  create compareset cust_orders 
  with source conn_prod cust_owner cust_orders t1 
  where “id>100”
  target conn_bak1 cust_owner cust_orders t2 
  where “id>100”
  and target conn_bak2 cust_owner cust_orders t3
  where “id>100”
  map  
  t1.id = t2.id = t3.id set key=true  
  and t1.cust_id = t2.cust_id = t3.cust_id 
  and t1.sku = t2.sku = t3.sku
  and t1.date= t2.date = t3.date
  go

• Example 2 – creates a new compareset named “cust_orders” and automatically maps all the target and source columns:

  create compareset cust_orders  
  with source conn_prod cust_owner cust_orders t1 
  where “id>100”
  target conn_bak1 cust_owner cust_orders t2 
  where “id>100”
  and target conn_bak2 cust_owner cust_orders t3
  where “id>100”
  map all
  go

Usage