Replication Jobs Management

You are looking at an older version of the documentation. The latest version is found here.

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`
`description`	string	Description 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'
))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`
`description`	string	Description 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'
);;

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`
`description`	string	Description for 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
`SQLStatment`	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`
`description`	string	Description for the job to be changed