Skip to main content
Skip table of contents

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}

targetTablestring

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate

description

string

Description of the job to be created

uuidstringCustom UUID of the job to be created

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

targetTablestring

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate

description

stringDescription of the job to be created
uuidstringCustom UUID of the job to be created

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}

targetTablestring

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate

description

stringDescription for the job to be changed
refreshTargetbooleanIndicates if the target data source should be refreshed before running the job
allowsAlterTableStatementsbooleanIndicates if the target table should be altered in case the source has been altered
jobNamestringJob's unique identifier
retryCounterintegerNumber of retry attempts before the job is set to FAILED state
retryDelayintegerDelay in seconds between the retry attempts
runTimeoutintegerIndividual 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
jobUuidstringUUID 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

targetTablestring

Target table the data will be put into. Format: {schemaname}.{tableName}

cleanup_method

string

Specifies the cleanup method. Valid values: drop, delete, truncate

description

stringDescription for the job to be changed
refreshTargetbooleanIndicates if the target data source should be refreshed before running the job or not
allowsAlterTableStatementsbooleanIndicates if the target table should be altered in case the source has been altered
jobNamestringJob's unique identifier
retryCounterintegerNumber of retry attempts before the job is set to FAILED state
retryDelayintegerDelay in seconds between the retry attempts
runTimeoutintegerIndividual 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
jobUuidstringUUID of the job to be changed
JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.