Home / Repstance User Guide / DATABASE CONFIGURATION COMMANDS / Prepare Target Database

Prepare Target Database

The prepare database=target command makes a database ready to be the Target of a replication. It creates the objects that the Apply Processes need: the checkpoint tables, the tables of the heartbeat and of the data comparison, and the procedures that write the checkpoints. It has to be run before the first Apply Process of the database is run. What is created, and the privileges every step needs, are listed in Database Privileges. The rules that all the database commands share and the messages are on DATABASE CONFIGURATION COMMANDS.

A Target Database can be Oracle, SQL Server, PostgreSQL, MySQL, Snowflake or Redshift. For AWS S3 and Apache Kafka there is nothing to prepare: the command does nothing (see AWS S3 as Target and Kafka as Target). The command can be run again: it creates what is missing and replaces the procedures. With generatesql=1 it creates nothing and returns the SQL script, so that an administrator can run it; for SQL Server, PostgreSQL, MySQL, Redshift and Snowflake no connection is made then.

Command line (repcli)

repcli prepare database=target dbtype={oracle|mssql|postgresql|mysql|snowflake|redshift} \
[connection parameters of the Target Database] \
generatesql={0|1}

Parameter	Description
database	target. Required.
dbtype	oracle, mssql, postgresql, mysql, snowflake, redshift or s3. Required.
generatesql	1 makes prepare return the SQL script and change nothing. The default is 0: prepare creates the objects. validate does not use the parameter, and remove removes the objects whatever its value.
server, port, user, password, dbname and the other connection parameters	How to reach the database. They depend on the database: Oracle as Target, SQL Server as Target, PostgreSQL as Target, MySQL as Target, Snowflake as Target, Redshift as Target, AWS S3 as Target and Kafka as Target.

Prepare a SQL Server database:

repcli prepare database=target dbtype=mssql server=db-tgt.example.com port=1433 dbname=testDB user=repuser password=Passw0rd123
[Completed]: Database [db-tgt.example.com:1433,testDB] has been prepared.

Return the script for a PostgreSQL database instead of running it. No connection is made, the script goes on with the rest of the tables and the functions:

repcli prepare database=target dbtype=postgresql server=pg-tgt.example.com port=5432 dbname=testDB user=repuser password=Passw0rd123 generatesql=1
[Completed]:
---- SQL ----
create schema if not exists repi;

create table if not exists repi.apply_ckpt(
        apply_id int primary key,
        file_sequence int not null,
        md_sequence int not null,
        position bigint not null,
        first_lsn char(22) not null,
        ckpt_lsn char(22) not null,
...

The command is refused when a required parameter is missing, for example the database of a PostgreSQL Target, or when the database type is missing:

repcli prepare database=target dbtype=postgresql server=pg-tgt.example.com port=5432 user=repuser password=Passw0rd123
[Failed]: No database is provided

repcli prepare database=target
[Failed]: Missed dbtype parameter
  Valid values: mssql | oracle | mysql | postgresql | redshift | snowflake | s3 | kafka

REST API

Endpoint: https://repstance_url/configure/database
Method: POST
Header: Content-Type: application/json and X-Token: token

The body of the request:

{
  "command": "prepare",
  "parameters": [
    ["database", "target"],
    ["dbtype", "mssql"],
    ["server", "db-tgt.example.com"],
    ["port", "1433"],
    ["user", "repuser"],
    ["password", "Passw0rd123"],
    ["dbname", "testDB"]
  ]
}

The body of the answer:

{
  "Status": "Completed",
  "Message": "Database [db-tgt.example.com:1433,testDB] has been prepared."
}

With generatesql=1 the Message is the script. For dbtype=s3 the answer is In Process and nothing is done:

{
  "Status": "In Process",
  "Message": "Command [PREPARE] has been sent. Use [validate] command to validate the state"
}

HTTP code	Meaning
200	The request is valid and the command has been carried out. The body has the status Completed or Failed (prepare and remove) or Valid or Invalid (validate), and the message. A command that cannot do its work, for example because the database cannot be reached, is also answered with 200 and the status Failed or Invalid: always read the status.
401	The token is invalid.
422	The request cannot be carried out: the JSON, the command or a parameter is wrong. The body has the status Failed and the message, for example Missed port parameter.

What happens

SQL Server. The command creates the schema repi with the tables apply_ckpt, apply_ckpt_history, hb_stat and ValidateTables and the procedures resetCkpt, updateCkpt, updateMDCkpt and updateLDCkpt. It creates no CDC, no jobs and no triggers.
Oracle. The command creates the locked user REPI (C##REPI on Oracle 11g and in the root of a container database) with the same tables, a sequence and the same procedures.
PostgreSQL, Redshift, MySQL and Snowflake. The command creates the schema repi (the database repi for MySQL, whatever dbname is; the schema REPI for Snowflake) with the tables and the functions or procedures. Snowflake gets the tables APPLY_CKPT, HB_STAT and ValidateTables and a file format for the bulk load.
AWS S3 and Apache Kafka. Nothing is created.
What exists is left as it is, and the tables that are replicated are not changed. The command creates no login and grants no privileges (see Database Privileges).

Web UI

Click “Add Apply” on the dashboard, enter the id, choose the Capture Process and click the logo of the database:

Fill in the form “Database Settings” (the fields are described on the pages of the databases) and click “Test connection”. The buttons “Prepare for Replication” and “Show Prepare SQL” appear. “Prepare for Replication” prepares the database, “Show Prepare SQL” shows the script instead, as it does for a Source Database (see Prepare Source Database). There are no such buttons for AWS S3 and Apache Kafka:

Web UI, Database Settings of a SQL Server Target: not prepared, Prepare for Replication and Show Prepare SQL highlighted

A confirmation window comes first. Click “Proceed with Installation”:

Web UI, Prepare Target Database confirmation with the Proceed with Installation button highlighted

For a SQL Server Target the window has the check box “Drop legacy replication objects”. It is ticked by default. When it is ticked the Web UI runs the command remove for the Target Database (see Remove Repstance Objects from Database) before it runs prepare, so the checkpoint tables are deleted and created again. Clear the check box when an Apply Process already uses the database: it loses its checkpoint.

The whole procedure, with the Apply Process around it, is in Setup First Replication.

PostgreSQL as Source

Oracle as Target