create compareset foreach

Creates a compareset for each table after searching for all matching table names in the source and target databases.

Syntax

create compareset [compareset_name_prefix] foreach table
  with
    source source_connection_name owner_name
    target target_connection_name owner_name
    [and target target_connection owner_name]…
  [{include | exclude} table_name_pattern [and table_name_pattern]…]
  [set abort_on_collision  [{to|=}] {true|false}
    [and set commit_batch_size [{to|=}] number_of_comparesets
    [and set strict_all_columns [{to|=}] {true|false}    [and set strict_column_keys [{to|=}] {true|false}
    [and set strict_column_types [{to|=}] {true|false}
    [and set keep_computed_columns [{to|=}] {true|false}
    [and set keep_encrypted_columns [{to|=}] {true|false}
  ]]]]]]
go

Parameters

compareset_name_prefix – a string to prefix all compareset names.
source_connection_name – the name of the source database connection.
target_connection_name – the name of the target database connection.
owner_name – the name of the owner of the source and target tables.
table_name_pattern – a pattern that table names must match to be included or excluded.

create_compareset_parameter – See Create Compareset Parameters table.

Note: The source and target connection_name and owner_name pairings must be unique within each command.

Create Compareset Parameters
Parameter	Value
abort_on_collision	A compareset name generated during the execution of this command might match an existing compareset name (either created manually or through an earlier invocation of this command). When this parameter is set to true and a generated compareset name matches an existing compareset name, the command aborts immediately and all uncommitted comparesets are discarded. Valid values: true or false. Default: true.
commit_batch_size	The number of comparesets DA server stores in memory before committing them to the internal database. If an error is generated, all uncommitted comparesets are discarded. Valid values: 1 to 2147483647. Default: 1000.
strict_all_columns	When this parameter is set to true and one of the target tables does not contain all of the source columns, no compareset is generated. Valid values: true or false. Default value: false.
strict_column_keys	When this parameter is set to true and one of the source tables has no primary key, identity or uniquely indexed column, no compareset is generated. Valid values: true or false. Default: false.
strict_column_types	When this parameter is set to true and any target column type does not exactly match the source column type, no compareset is generated. Valid values: true or false. Default: false.
keep_computed_columns	Whether to include computed columns in the compareset column mappings. Valid values: true or false. Default: false.
keep_encrypted_columns	Whether to include encrypted columns in the compareset column mappings. Valid values: true or false. Default: false

Examples

Example 1 – creates a compareset for each table that exists in both the source and target databases for DA connections "conn_venus" and "conn_pluto" respectively. Each compareset name uses the default prefix:
```
create compareset foreach table
  with source conn_venus dbo
       target conn_pluto dbo
go
```
Example 2 – creates a compareset for each table that exists in both the source and target databases using the prefix "cmpset_", excluding the SAP Replication Server tables, which have names that are prefixed with "rs_":
```
create compareset cmpset_ foreach table
  with source conn_venus dbo
       target conn_pluto dbo
  exclude rs_*
go
```
Example 3 – creates a compareset for each table that exists in both the source and target databases using the prefix "cmpset_" excluding SAP Replication Server tables. The command does not abort if a compareset is generated with the same name as one that already exists and buffered comparesets are committed to the DASD in batches of 100:
```
create compareset cmpset_ foreach table
  with source conn_venus dbo
       target conn_pluto dbo
  exclude rs_*
  set abort_on_collision false
    and set commit_batch_size 100
go
```
Example 4 – creates a compareset for each table that exists in the source and both target databases using the default prefix, including tables only with names that begin with the prefix "customer_" or "cust_". Each target table must contain all the source table columns with the same name, type, precision, and scale:
```
create compareset foreach table
  with source conn_venus dbo
       target conn_pluto dbo
   and target conn_earth dbo
  include customer_*
      and cust_*
  set strict_all_columns true
    and set strict_column_types true
go
```

Usage

When overriding the default compareset prefix:
- The compareset name prefix cannot begin with a digit.
- Do not create unnecessary duplicate comparesets.
  Note: DA creates duplicate comparesets, if you run create compareset foreach multiple times with different prefixes.
You can use wildcards in the table_name_pattern. For example:
- include a* and b* – includes all table names beginning with a or b only.
- exclude *_???– excludes all table names that end with an underscore followed by any three characters, such as table_001 and table_002.