7.1 Prepare Source and Target Databases

Both Source and Target Databases must be prepared for replication before running any replication processes. The “prepare” database command is used to configure databases. The command must be executed on both Source and Target Databases.

7.1.1 Prepare MS SQL Server Database as Source Database

The “prepare” database command is used to configure MS SQL Server database in order to use it as a Source. It enables CDC and creates the Database Objects necessary for any Capture Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","source"],
       ["dbtype","mssql"],
       ["server","host_name"],
       ["port","port_number"],
       ["dbname","database_name"],
       ["user","db_user_name"],
       ["password","db_user_password"],
       ["housekeeping","0|1|2"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the only possible value is source – to insert Capture Process objects and enable CDC
• dbtype – Type of the Source Database, the acceptable value is mssql – MS SQL Server database
• server – Host name or IP address of the database server
• port – Database port number
• dbname – Database name to be connected to
• user – Database User name
• password – Database User password
• housekeeping – This parameter determines how the CDC data will be cleaned up. The possible values are:
  • 0 – data will be cleaned up by an SQL Server job only,
  • 1 – install Repstance job to clean up data only after extraction and keep SQL Server cleanup job, which is a time based CDC setting,
  • 2 – install Repstance job and remove SQL Server cleanup job, data will be cleaned up only by the Repstance job.

7.1.2 Prepare Oracle Database as Source Database

The “prepare” database command is used to configure Oracle database in order to use it as a Source. It enables the necessary level of supplemental logging and creates the database objects necessary for any Capture Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

There are two possible connection types – EZCONNECT and TNS. Depending on which one is chosen, there will be a different set of possible parameters.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","source"],
       ["dbtype","oracle"],
       ["connectiontype","tns|ezconnect"],
       ["tnsname","tns_alias"],
       ["server","host_name"],
       ["port","port_number"],
       ["servicename","service_or_SID"],
       ["dbname","database_name"],
       ["user","db_user_name"],
       ["password","db_user_password"],
       ["createlogdirs","0|1"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the possible value is source – to insert Capture Process Objects
• dbtype – Type of RDBMS, the only possible value is oracle
• connectiontype – Specifies the Oracle connection method, the possible values are:
• tns – Local Naming Method to be used
• ezconnect – EZCONNECT to be used
• tnsname – Name of the TNS alias, valid only if connectiontype=tns
• server – Host name or IP address of the database server, valid only if connectiontype=ezconnect
• port – Database port number, valid only if connectiontype=ezconnect
• servicename – Database service name or SID, valid only if connectiontype=ezconnect
• dbname – Name of either container or pluggable database, valid only if database=source and for any Oracle versions 12c-19c but excluding RDS instances
• user – Database User name
• password – Database User password
• createlogdirs – The parameter determines if the “ONLINELOG_DIR” and “ARCHIVELOG_DIR” Oracle directories need to be created. The possible values are:
  • 0 – do not run create the directories (default value),
  • 1 – create the directories.

Note – The directories are used if the Capture Process is configured to extract changes in the “DirectLog” mode (see chapter 7.4.2 Prepare Capture Process for Oracle Database for the details).

7.1.3 Prepare MS SQL Server Database as Target Database

The “prepare” database command is used to configure MS SQL Server database as a Target. The command must be executed before running Apply Process. It creates the necessary database objects for any Apply Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","target"],
       ["dbtype","mssql"],
       ["server","host_name"],
       ["port","port_number"],
       ["dbname","database_name"],
       ["user","db_user_name"],
       ["password","db_user_password"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the only possible value is target – to insert Apply Process Objects
• dbtype – Type of RDBMS, the only appropriate value is mssql
• server – Host name or IP address of the database server
• port – Database port number
• dbname – Database name to be connected to
• user – Database User name
• password – Database User password

7.1.4 Prepare Oracle Database as Target Database

The “prepare” database command is used to configure Oracle database as a Target. The command must be executed before running Apply Process. It creates the necessary database objects for any Apply Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

There are two possible connection types – EZCONNECT and TNS. Depending on which one is chosen, there will be a different set of possible parameters.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","target"],
       ["dbtype","oracle"],
       ["connectiontype","tns|ezconnect"],
       ["tnsname","tns_alias"],
       ["server","host_name"],
       ["port","port_number"],
       ["servicename","service_or_SID"],
       ["user","db_user_name"],
       ["password","db_user_password"],
       ["tablespace","user_tablespace"]
    ]
  }

Server response:

• HTTP Status – status of the command, the possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the only possible value is target – to insert Apply Process Objects
• dbtype – Type of RDBMS, the only possible value is oracle
• connectiontype – Specifies the Oracle connection method, the possible values are:
• tns – Local Naming Method to be used
• ezconnect – EZCONNECT to be used
• tnsname – Name of the TNS alias, valid only if connectiontype=tns
• server – Host name or IP address of the database server, valid only if connectiontype=ezconnect
• port – Database port number, valid only if connectiontype=ezconnect
• servicename – Database service name or SID, valid only if connectiontype=ezconnect
• user – Database User name
• password – Database User password
• tablespace – Name of the tablespace that the Repstance’s objects are to be installed in. The default is the USERS tablespace if no alternative has been specified.

7.1.5 Prepare PostgreSQL and Aurora PostgreSQL Databases as Target Database

The “prepare” database command is used to configure PostgreSQL and Aurora PostgreSQL databases as a Target. The command must be executed before running Apply Process. It creates the necessary database objects for any Apply Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","target"],
       ["dbtype","postgresql"],
       ["server","host_name"],
       ["port","port_number"],
       ["dbname","database_name"],
       ["user","db_user_name"],
       ["password","db_user_password"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the only possible value is target – to insert Apply Process Objects
• dbtype – Type of RDBMS, the only possible value is postgresql
• server – Host name or IP address of the database server
• port – Database port number
• dbname – Database name to be connected to
• user – Database User name
• password – Database User password

7.1.6 Prepare MySQL and Aurora MySQL Databases as Target Database

The “prepare” database command is used to configure MySQL, MariaDB and Aurora MySQL databases as a Target. The command must be executed before running Apply Process. It creates the necessary database objects for any Apply Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","target"],
       ["dbtype","mysql"],
       ["server","host_name"],
       ["port","port_number"],
       ["dbname","database_name"],
       ["user","db_user_name"],
       ["password","db_user_password"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the possible value is target – to insert Apply Process Objects
• dbtype – Type of RDBMS, the possible value is mysql
• server – Host name or IP address of the database server
• port – Database port number
• dbname – Database name to be connected to
• user – Database User name
• password – Database User password

7.1.7 Prepare Snowflake as Target Database

The “prepare” database command is used to configure Snowflake database as a Target. The command must be executed before running Apply Process. It creates the necessary database objects for any Apply Processes.

Note – In order to use the “prepare” database command the database user must have sufficient privileges.

REST API:

• Endpoint: https://repstance_url/configure/database
• Method: POST
• Header:
  • Content-Type: application/json
  • X-Token: token
• Body:
  {
    "command":"prepare",
    "parameters":[
       ["database","target"],
       ["dbtype","snowflake"],
       ["account","account"],
       ["region","region"],
       ["warehouse","warehouse"],
       ["user","username"],
       ["password","password"],
       ["dbname","databaseName"]
    ]
  }

Server response:

• HTTP Status – status of the command, possible codes are:
  • 200 – if no error occurs
  • 422 – if error occurs
• Body:
  {
   "Status":"{Completed|Failed}",
   "Message":"The command execution details"
  }

CLI Syntax:

The input parameters are:

• database – Database role, the only possible value is target – to insert Apply Process Objects
• dbtype – Type of RDBMS, the correct value is snowflake
• account – Snowflake Account name
• region – Snowflake Region name
• warehouse – Warehouse name
• dbname – Database name to be connected to
• user – Database User name
• password – Database User password

Note – Account name and Region can be easily taken from the Snowflake URL: