create job

Creates a new job from one or more comparesets, schedules, and comparison options.

Syntax

create job job_name
[set MAX_CONCURRENT_COMPARISONS [{to|=}] number_of_max_concurrent_comparisons]
[and set DESC [{to|=}] description] 
add comparison comparison_name
set COMPARESET=compareset_name
[and set ABORT_DIFF_MAX [{to|=}] number_of_differences
[and set ABORT_DIFF_ROW_COUNT [{to|=}] {true|false}
[and set AUTO_RECONCILE [{to|=}] {true|false}
[and set COMPARE_MODE [{to|=}] {row_compare | key_compare | row_count} 
[and set COMPRESS_DATA_TRANSFER [{to|=}] {true|false}
[and set CREATE_COL_LOG [{to|=}] {true|false}
[and set CREATE_RECON_SCRIPT [{to|=}] {true|false}
[and set DESC [{to|=}] description
[and set ENABLE_ROW_COUNT [{to|=}] {true|false}
[and set EXTERNAL_SORT [{to|=}] {true|false}
[and set HASH_TYPE [{to|=}] {database_hash | agent_hash}
[and set NUM_PARTITIONS [{to|=}] number
[and set PRIORITY [{to|=}] {highest | high | normal | low}
[and set RETRY_DELAY_SEC [{to|=}] number_delay_second
[and set RETRY_DIFF [{to|=}] {never | wait_and_retry }
[and set RETRY_MAX [{to|=}] number_of_retries
]]]]]]]]]]]]]]]
[with column option
set column_name [{to|=}] {literal | column_hash | row_hash}
[set column_name [{to|=}] {literal | column_hash | row_hash}]
 […]]

[add schedule schedule_name
[set TYPE [{to|=}] {once | cron | every_day | every_week | every_month}
[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|=}] {day | week | month | forever}
[and set CRON [{to|=}] cron_value
[and set DESC [{to|=}] description
]]]]]]]]]

To clone an existing job:

create job job_name with exist_job_name

Note: When you clone a job with schedules, the new job includes the cloned schedules but they are active.

To create a job with a comparison for each compareset:

create job job_name  
   [set description [{to|=}] description
   [and set max_concurrent_comparisons [{to|=}]
      max_concurrent_comparisons
  ]]
  add comparison foreach compareset 
       [{include | exclude} compareset_name_pattern
       [and compareset_name_pattern]…]
       [and set ABORT_DIFF_MAX [{to|=}] number_of_differences
       [and set ABORT_DIFF_ROW_COUNT [{to|=}] {true|false}
       [and set AUTO_RECONCILE [{to|=}] {true|false}
       [and set COMPARE_MODE [{to|=}] {row_compare | key_compare | row_count} 
       [and set COMPRESS_DATA_TRANSFER [{to|=}] {true|false}
       [and set CREATE_COL_LOG [{to|=}] {true|false}
       [and set CREATE_RECON_SCRIPT [{to|=}] {true|false}
       [and set DESC [{to|=}] description
       [and set ENABLE_ROW_COUNT [{to|=}] {true|false}
       [and set EXTERNAL_SORT [{to|=}] {true|false}
        [and set HASH_TYPE [{to|=}] {database_hash | agent_hash}       
       [and set NUM_PARTITIONS [{to|=}] number
       [and set PRIORITY [{to|=}] {highest | high | normal | low}
       [and set RETRY_DELAY_SEC [{to|=}] number_delay_second
       [and set RETRY_DIFF [{to|=}] {never | wait_and_retry }
       [and set RETRY_MAX [{to|=}] number_of_retries
    ]]]]]]]]]]]]]]]

[add schedule schedule_name
[set TYPE [{to|=}] {once | cron | every_day | every_week | every_month}
[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|=}] {day | week | month | forever}
[and set CRON [{to|=}] cron_value
[and set DESC [{to|=}] description
]]]]]]]]]

This command creates multiple comparisons using all existing comparesets that match with the compareset name pattern.

Note: You can use both add comparison foreach compareset and add comparison name in the same create job command.

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 – (optional) the number of the comparisons that can be run concurrently with a job. The default value is 5
description – (optional) description of the agent. Use double quotes if you are using a reserved word or blank spaces.

compareset_name_pattern – (optional) wildcard pattern of a compareset name must match to be included or excluded. If no comparesets match the compareset_name_pattern, no job is created and an error message is generated.

exist_job_name – the name of an existing job to be cloned.

Comparison Options
Parameter	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. Note: Set the hash type comparison option to `agent_hash` for heterogeneous comparison. The `database_hash` comparison option is used only for Adaptive Server-to-Adaptive Server comparisons. Default value: database_hash.
NUM_PARTITIONS	Specifies the number of partitions for a table.
PRIORITY	Specifies the job comparison order in the comparison queue. Valid values are: `highest` `high` `normal` `low` Default value: normal.
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.

Scheduling Options
Parameter	Value
date_value	Specifies a date in the scheduler.
time_value	Specifies a time in the scheduler.
keep_value	Specifies the number of keep units for which this schedule remains active.
cron_value	Specifies the cron option value in the scheduler.

Examples

Example 1 – creates a new job named “myjob_1”:

create job myjob_1
set max_concurrent_comparisons = 3
add comparison mycomparison_1 
  set compareset=mycompareset_1
  and set priority = high 
  with column option
  and set a = literal
  set b = hash
and comparison mycomparison_2 
  set compareset=mycomparset_2
  and set priority = normal 
  with schedule myschedule_1 
  set type=every_day
  and set every=2
  and set time=10:00
  and set keep=1
  and set keep_unit=month
  and set date=2011-05-05
go

Example 2 – clones “myjob_1” to a new job “myjob_2”:
```
create job myjob_2 with myjob_1 
go
```

Example 3 – creates a job named “job1” and sets the number of data partitions to 2:

create job job1 
set MAX_CONCURRENT_COMPARISONS = 2 
ADD comparison cmp1
  set COMPARESET=cs
  and set NUM_PARTITIONS to 2
  and set COMPARE_MODE to row_compare 
  and set ENABLE_ROW_COUNT to false
go

Example 4 – creates a job with a comparison for each existing compareset using all the default options:
```
create job myJob
    add comparison foreach compareset
  go
```
Example 5 – creates a job with a key compare comparison for each compareset, except for those with a name that begins with "a":
```
create job myJob
    add comparison foreach compareset exclude a*
      set compare_mode key_compare
  go
```
Example 6 – creates a job with a row count comparison for each compareset that have a name beginning with "a" or "b":
```
create job myJob
    add comparison foreach compareset include a* and b*
    set compare_mode to row_count
  go
```
Example 7 – creates a job with a row compare comparison including reconciliation for each compareset with a name that begins with "a" or "b", and a key compare comparison for each compareset with a name that begins with "c":
```
create job myJob
    add comparison foreach compareset include a* and b*
      set compare_mode = row_compare
      and set create_col_log = true
      and set create_recon_script = true
    and comparison foreach compareset include c*
      set compare_mode = key_compare
  go
```
Example 8 – creates a job with a key compare comparison for each compareset that begins with "a" or "b", and a row compare comparison for the rest:
```
create job myJob
    add comparison foreach compareset include a* and b*
      set compare_mode key_compare
    and comparison foreach compareset exclude a* and b*
      set compare_mode row_compare
  go
```
Example 9 – shows how conflicts occur when a create job statement contains two or more foreach constructs:
```
create job myJob
    add comparison foreach compareset include a*
      set compare_mode key_compare
    and comparison foreach compareset include *z
      set compare_mode row_count
  go
```
Any compareset with a name beginning with "a" and ending with "z" creates a conflict:
- When comparisons use the name of the compareset they operate on, this leads to add two comparisons with the same name.
- Data Assurance cannot drop one of the comparison definitions in favor of the other. As a result, an error occurs and job is not created.