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=true | false] 
[set strict_type=true | false] 
[set keep_computed=true | false]
[set keep_encrypted=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: You must define at least one key column in your compareset. When you use the map all statement, both the mapping and the key property come from the database.
• 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_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, 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

  The returned result is:

  Compareset “cust_orders” was created successfully

• 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

  The returned result is:

  Compareset “cust_orders” was created successfully

Usage