alter job

Changes the attributes of an existing job.

Syntax

To drop a job comparison:

alter job job_name 
	drop comparison comparison_name [and comparison_name2 […]]

To add a job comparison:

alter job job_name
add comparison comparison_name
set COMPARESET =compareset_name
[and set COMPARE_MODE [{to|=}] {row_compare | key_compare | row_checksum | table_checksum | row_count} 
[and set ABORT_DIFF_ROW_COUNT [{to|=}] {true|false}
[and set ABORT_DIFF_MAX [{to|=}] max_diff_percent
[and set MAX_DIVISIONS [{to|=}] max_number_of_division
[and set RETRY_DIFF [{to|=}] {never | wait_and_retry }
[and set RETRY_MAX [{to|=}] number_of_retries
[and set RETRY_DELAY_SEC [{to|=}] number_delay_second
[and set HASH_TYPE [{to|=}] {database_hash | agent_hash}
[and set CREATE_COL_LOG [{to|=}] {true|false}
[and set CREATE_RECON_SCRIPT [{to|=}] {true|false}
[and set AUTO_RECONCILE [{to|=}] {true|false}
[and set EXTERNAL_SORT [{to|=}] {true|false}
[and set COMPRESS_DATA_TRANSFER [{to|=}] {true|false}
[and set PRIORITY [{to|=}] {highest | high | normal | low}
[and set desc [{to|=}] “description”
]]]]]]]]]]]]]
[with column option
set column_name [{to|=}] {literal | column_hash | row_hash}
[set column_name [{to|=}] {literal | column_hash | row_hash}]
 […]]
 [and compareset comparison_name2
…]

To alter the job comparisons:

alter job job_name
alter comparison comparison_name
[set COMPARESET =compareset_name
[and set COMPARE_MODE [{to|=}] {row_compare | key_compare | row_checksum | table_checksum | row_count} 
[and set ABORT_DIFF_ROW_COUNT [{to|=}] {true|false}
[and set ABORT_DIFF_MAX [{to|=}] max_diff_percent
[and set MAX_DIVISIONS [{to|=}] max_number_of_division
[and set RETRY_DIFF [{to|=}] {never | wait_and_retry }
[and set RETRY_MAX [{to|=}] number_of_retries
[and set RETRY_DELAY_SEC [{to|=}] number_delay_second
[and set HASH_TYPE [{to|=}] {database_hash | agent_hash}
[and set CREATE_COL_LOG [{to|=}] {true|false}
[and set CREATE_RECON_SCRIPT [{to|=}] {true|false}
[and set AUTO_RECONCILE [{to|=}] {true|false}
[and set EXTERNAL_SORT [{to|=}] {true|false}
[and set COMPRESS_DATA_TRANSFER [{to|=}] {true|false}
[and set PRIORITY [{to|=}] {highest | high | normal | low}
[and set desc [{to|=}] “description”

To alter the column options in a job comparison:

alter job job_name
alter comparison comparison_name
with column option
set column_name [{to|=}] {literal | column_hash | row_hash}
[set column_name [{to|=}] {literal | column_hash | row_hash}]
 […]

To change the job options:

alter job job_name 
[[set MAX_CONCURRENT_COMPARISONS [{to|=}] number_of_max_concurrent_comparisons
[and set desc [{to|=}] description]]

To change all the comparison options:

alter job job_name
alter  comparison option
set comparison_param [{to|=}] value

To enable a comparison:

alter job job_name enable comparison comparison_name
[ and comparison_name2[…]]

To disable a comparison:

alter job job_name disable comparison comparison_name
[ and comparison_name2[…]]

To add or alter the scheduling options:

alter job job_name
{add | alter} schedule schedule_name 
[set TYPE [{to|=}] once | cron | every_days | every_weeks | every_months
[and set EVERY [{to|=}] n
[and set DATE [{to|=}] date_value
[and set TIME [{to|=}] time_value
[and set KEEP [{to|=}] keep_value
[and set KEEP_UNIT [{to|=}] days | weeks | months | forever
[and set CRON [{to|=}] cron_value
[and set desc [{to|=}] description]
]]]]]]]]]
	[and schedule schedule_name2
[set TYPE [{to|=}] once | cron | every_days | every_weeks | every_months
[and set EVERY [{to|=}] n
[and set DATE [{to|=}] date_value
[and set TIME [{to|=}] time_value
[and set KEEP [{to|=}] keep_value
[and set KEEP_UNIT [{to|=}] days | weeks | months | forever
[and set CRON [{to|=}] cron_value
[and set desc [{to|=}] description]
]]]]]]]]]……]}

To drop the scheduling option:

alter job job_name 
drop schedule schedule_name[and schedule_name2[……]]

Parameters

job_name – the name of the job.
comparison_name – the name of the comparison to be added to the job.
compareset_name – the name of the compareset to be added into the comparison.
schedule_name – the name of the schedule to be added.
max_concurrent_comparisons – the number of the comparisons that can be run concurrently with a job.
description – description of the agent. Use double quotes if you are using a reserved word or blank spaces.
comparison_param – defines various comparison options for the job, all of which are optional.

value – defines a value for the comparison_param.

Comparison Options
`comparison_param`	`value`
ABORT_DIFF_MAX	Aborts row comparison if the difference count exceeds the specified value. Valid values: 1 to 9223372036854775807. Default value:1000.
ABORT_DIFF_ROW_COUNT	Determines whether to abort row comparison if table row counts do not match. Valid values: true or false. Default value: false.
AUTO_RECONCILE	Indicates whether to automatically apply the reconciliation script. Valid values: true or false. Default value: false. Note: To enable AUTO_RECONCILE, set CREATE_COL_LOG to true.
COMPARE_MODE	Specifies the row comparison mode. `row_compare` – compares all table rows. `key_compare` – compares the primary key columns. `row_count` – compress row count. Default value: row_compare.
COMPRESS_DATA_TRANSFER	Compresses the row data between the agent and the server. Valid values: true or false. Default value: false.
CREATE_COL_LOG	Generates a column differences log, which lists all missing, orphaned, and inconsistent row values (keys and columns). Create a column log if you want to: Generate a reconciliation script Perform automatic reconciliation Generate a detailed report Valid values: true or false. Default value: false.
CREATE_RECON_SCRIPT	Generates a reconciliation script. To use this parameter, you must also set CREATE_COL_LOG to true. Valid values: true or false. Default value: false.
ENABLE_ROW_COUNT	Determines whether or not to count source and target table rows before they are compared. DA server uses the row count to estimate the comparison progress and end time. Note: DA server counts rows if COMPARE_MODE is row_count. Use ENABLE_ROW_COUNT only if COMPARE_MODE is a value other than row_count. Valid values: true or false. Default value: true.
EXTERNAL_SORT	Sorts rows on the agent, thereby reducing the impact of processing the ORDER BY clause in the databases. Valid values: true or false. Default value: false.
HASH_TYPE	Specifies the hash type for the comparison. `database_hash` – use the hash function provided by the database. `agent_hash` – use the hash function provided by Replication Server Data Assurance Option. Default value: database_hash.
RETRY_DIFF	Specifies the retry option. `never` – no recompare. `wait_and_retry` – run recompare based on RETRY_MAX and RETRY_DELAY_SEC settings. Default value: never.
RETRY_DELAY_SEC	Specifies the number of seconds delay for each recomparison. Valid values: 0 to 86400. Default value: 10.
RETRY_MAX	Specifies the total number of recomparison for rows that have differences resulting from a previous comparison. Valid values: 0 to 100. Default value: 3.

Column Comparison Option
Column Option	Value
COMPARE_MODE	Specifies for how each column is compared. column_hash – compares using column hash value. row_hash – compares all columns with this option together with a whole hash value. literal – compares using column literal value.

schedule_param – defines the scheduling options for the job.

value – defines a value for schedule_param.

Scheduling Option Parameters
`schedule_param`	`value`
date_value	Specifies a date in the scheduler.
time_value	Specifies a time in the scheduler.
month_day_value	Specifies the day of the month in the scheduler.
cron_value	Specifies the cron option value in the scheduler.

Examples

Example 1 – alters “myjob_1” to remove its comparison:

alter job myjob_1
drop comparison mycomparison_1
go

The returned result is:

Job “myjob_1” was altered successfully.

Example 2 – alters “myjob_1” to add comparison:

alter job myjob_1
add comparison mycomparison_2 
set compareset=mycompareset_2
and set priority = high
go

The returned result is:

Job “myjob_1” was altered successfully.