alter compareset

Changes the attributes of an existing compareset. alter compareset fails if the compareset you are modifying is being used in an executing job.

Syntax

To alter description:

alter compareset compareset_name [force]
set desc [{to|=}] description

To drop a target table:

alter compareset compareset_name [force]
drop target target_connection_name owner_name
target_table_name
[and target_connection_name owner_name
target_table_name …]

This command also deletes all partition boundary values stored for this compareset.

To add a target connection:

alter compareset compareset_name  [force]
	add target target_connection_name owner_name 
target_table_name target_alias [where “constraint”]
[and target target_connection_name owner_name 
target_table_name target_alias2 where “constraint”]...] 
map 
    source.column_name = target_alias.column_name 
[ = target_alias2.column_name ...], 
[and source.column_name = target_alias2.column_name 
[ = target_alias2.column_name ...]…]

To drop a column mapping:

alter compareset compareset_name [force]
drop map source_column_name [and source_column_name…]

To add a column mapping:

alter compareset compareset_name  [force]
	with target target_connection_name owner_name 
target_table_name target_alias 
[and target target_connection_name owner_name 
target_table_name target_alias2]] 
add map 
    source.column_name = {target1}target_alias.column_name 
[ = {target2}target_alias2.column_name] [set key=true], 
[and source.column_name = {target1}target_alias2.column_name 
[ = {target2}target_alias2.column_name] [set key=true]…]

To alter a where constraint:

alter compareset compareset_name  [force]
[alter source where constraint]
[alter target target_connection_name owner_name
target_table_name where constraint]

Note: You cannot modify the source where constraint and the target where constraint at the same time.

To drop a where constraint:

alter compareset compareset_name  [force]
[alter source where “”]
[alter target target_connection_name owner_name
target_table_name where “”]

To alter key columns:

alter compareset compareset_name  [force]
alter map
source.column_name set key {false | true}
[source.column_name set key {false | true}...]

Parameters

compareset_name – the name of the compareset.
description – 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.
force – alters a compareset that points to one or more comparisons. You must use force to modify such comparisons.

Examples

Example 1 – drops the target connection “conn_bak1” from the existing compareset “cust_orders”:
```
alter compareset cust_orders 
drop target conn_bak1 cust_owner cust_orders
go
```
Example 2 – drops some column mappings from the existing compareset “cust_orders”:
```
alter compareset cust_orders  
drop map
id and cust_id
go
```

Usage

If you do not redefine the target alias, you can use the keywords “target1” and “target2” to refer to the target tables; the sequence must be consistent with the definition used when you created the compareset.
When using the add map clause in alter compareset, you must include all the target connections.