Replication Jobs Management

In addition to the CopyOver() method, the Data Virtuality Server also allows using the CopyOver technique on data sources via two procedures:

createCopyOverSourceTableJob()
createCopyOverSQLJob()

For changing existing CopyOver jobs, two more procedures are available:

changeCopyOverSourceTableJob()
changeCopyOverSQLJob()

Please note that these procedures only create the respective jobs, and they have to be started manually afterwards.

createCopyOverSourceTableJob

This procedure creates a job that copies data from the given source table to the given target table and returns the jobID of the created job. It takes the following parameters:

Parameter	Type	Meaning
`sourceTable`	string	Source table the data will come from. `Format: {schemaname}.{tableName}`
`targetTable`	string	Target table the data will be put into. Format: `{schemaname}.{tableName}`
`cleanup_method`	string	Specifies the cleanup method. Valid values: `drop`, `delete`, `truncate`. This method can work only if the translator property `supportsNativeQueries=TRUE` is provided on the target data source
`description`	string	Description of the job to be created
`uuid`	string	Custom UUID of the job to be created

uuid parameter in SYSADMIN.createCopyOverSourceTableJob is available since v4.1

Examples

1. Creating a job from a source table using the DROP cleanup method:

SQL

SELECT jobID FROM (
CALL "SYSADMIN.createCopyOverSourceTableJob"(
    "sourceTable" => 'test_tables.test_a',
    "targetTable" => 'dwh.ttt',
    "cleanupMethod" => 'DROP',
    "description" => 'Description job 1',
	"uuid" => '3c25f20a-6682-11ee-8c99-0242ac120002'
))s;;

2. Creating a job from a source table using the DELETE cleanup method:

SQL

SELECT jobID FROM (
CALL "SYSADMIN.createCopyOverSourceTableJob"(
    "sourceTable" => 'test_tables.test_a',
    "targetTable" => 'dwh.ttt1',
    "cleanupMethod" => 'DELETE',
    "description" => 'Description job 2'
))s;;

createCopyOverSQLJob

This procedure creates a job that copies data from the given source table to the given target table and returns the jobID of the created job. It takes the following parameters:

Parameter	Type	Meaning
`sqlStatement`	string	SQL statement that queries the data to be put into a target table
`targetTable`	string	Target table the data will be put into. Format: `{schemaname}.{tableName}`
`cleanup_method`	string	Specifies the cleanup method. Valid values: `drop`, `delete`, `truncate`. This method can work only if the translator property `supportsNativeQueries=TRUE` is provided on the target data source
`description`	string	Description of the job to be created
`uuid`	string	Custom UUID of the job to be created

uuid parameter in SYSADMIN.createCopyOverSQLJob is available since v4.1

Examples

1. Creating a job via an SQL statement with the DROP cleanup method:

SQL

CALL "SYSADMIN.createCopyOverSQLJob"(
    "sqlStatement" => 'SELECT a, sum(b) AS sumOfColB FROM test_tables.test_a WHERE a > 1 GROUP BY a',
    "targetTable" => 'dwh.ttt2',
    "cleanupMethod" => 'DROP',
	"uuid" => '3c25f5ac-6682-11ee-8c99-0242ac120002'
);;

2. Creating a job via an SQL statement with the DELETE cleanup method:

SQL

CALL "SYSADMIN.createCopyOverSQLJob"(
    "sqlStatement" => 'SELECT a FROM test_tables.test_a',
    "targetTable" => 'dwh.ttt2',
    "cleanupMethod" => 'DELETE'
);;

changeCopyOverSourceTableJob

This procedure allows changing a CopyOverSourceTableJob. It takes the following parameters:

Parameter	Type	Meaning
`jobId`	biginteger	ID of the job to be changed
`sourceTable`	string	Source table the data will come from. Format: {schemaname}.{tableName}
`targetTable`	string	Target table the data will be put into. Format: {schemaname}.{tableName}
`cleanup_method`	string	Specifies the cleanup method. Valid values: `drop`, `delete`, `truncate`. This method can work only if the translator property `supportsNativeQueries=TRUE` is provided on the target data source
`description`	string	Description for the job to be changed
`refreshTarget`	boolean	Indicates if the target data source should be refreshed before running the job
`allowsAlterTableStatements`	boolean	Indicates if the target table should be altered in case the source has been altered
`jobName`	string	Job's unique identifier
`retryCounter`	integer	Number of retry attempts before the job is set to `FAILED` state
`retryDelay`	integer	Delay in seconds between the retry attempts
`runTimeout`	integer	Individual job timeout in minutes. If no `runTimeout` is set, configuration set via default value option `JOB_RUN_TIMEOUT` will be considered for the job; if `runTimeout` is reached before the job is finished, the job terminates with the `INTERRUPTED` state
`jobUuid`	string	UUID of the job to be changed

changeCopyOverSQLJob

This procedure allows changing a CopyOverSQLJob. It takes the following parameters:

Parameter	Type	Meaning
`jobId`	biginteger	ID of the job to be changed
`SQLStatement`	string	SQL statement that queries the data to be put into a target table
`targetTable`	string	Target table the data will be put into. Format: `{schemaname}.{tableName}`
`cleanup_method`	string	Specifies the cleanup method. Valid values: `drop`, `delete`, `truncate`. This method can work only if the translator property `supportsNativeQueries=TRUE` is provided on the target data source
`description`	string	Description for the job to be changed
`refreshTarget`	boolean	Indicates if the target data source should be refreshed before running the job or not
`allowsAlterTableStatements`	boolean	Indicates if the target table should be altered in case the source has been altered
`jobName`	string	Job's unique identifier
`retryCounter`	integer	Number of retry attempts before the job is set to `FAILED` state
`retryDelay`	integer	Delay in seconds between the retry attempts
`runTimeout`	integer	Individual job timeout in minutes. If no `runTimeout` is set, configuration set via default value option `JOB_RUN_TIMEOUT` will be considered for the job; if `runTimeout` is reached before the job is finished, the job terminates with the `INTERRUPTED` state
`jobUuid`	string	UUID of the job to be changed

uuid parameter in SYSADMIN.changeCopyOverSQLJob and SYSADMIN.changeCopyOverSourceTableJob is available since v4.4