Replicate ver 2.8 enhancement: Make dropped tables stay 'prepared'

Owned by Clive Otten (Unlicensed)
Jan 22, 2020
2 min read

Problem Description

Most useful for tables that are dropped & recreated on source frequently or CTAS tables. This enhancement can be used for replication configurations done at either the table or schema level.

Solution

I.  DDL replication must be enabled either via SETUP WIZARD or PREPARE command along with the below parameters set BEFORE running -all.sh (-all.bat on Windows) is run.

  1. If you want to use this, after running the setup wizard you just need to set the below by editing the config/*-onetime.ddc file BEFORE running the *-all.sh (or –all.bat Windows script)

    _MINE_AUTO_PREPARE_BY_NAME_STORE=YES _MINE_AUTO_PREPARE_BY_NAME_USE=YES

  2. You can get list of tables selected for such re-prepare using

    dbvrep> process autoprepare list

  3. For the docs: you need to understand implications of DDL replications.

    • If you use it, we will replicate the DROP and the CREATE TABLE;

    • If you don't, we won't. If not, you will still have old data on the target. You probably don't want that.

 

II. TABLE REPLICATION via Setup wizard or via PREPARE with these values set

_MINE_AUTO_PREPARE_BY_NAME_STORE=YES _MINE_AUTO_PREPARE_BY_NAME_USE=YES

 TABLE REPLICATION, Setup Wizard, DDL=Y

  1. Table replication: (DDL Yes) in SETUP wizard then drop & recreate tables with same & different # of columns. Table replicated successfully

  2. Table replication : (DDL YES) PREPARE additional table from SCOTT schema & rename table. Table replicated successfully

    TABLE REPLICATION, Setup Wizard, DDL=N

  3. Table replication : (DDL NO) in SETUP wizard then drop & recreate tables; Table was dropped on source & not on target per NO DDL. When recreated on SOURCE got a conflict. Table was not in autoprepare list. To resolve conflict had to recreate table on target & inserts flowed to target successfully

  4. Table replication : (DDL NO), Add table into replication via PREPARE method but NO DDL then renamed table on source

    PREPARE additional table from SCOTT schema(DDL NO) & renamed source table.

    Results since NODDL selected change was not replicated to target. This renamed table will not show up in autoprepare list, ie dbvrep> process autoprepare list

    SCHEMA REPLICATION via Setup wizard or via PREPARE with these values set

    Schema Replication, DDL -YES

  5. Schema replication: (DDL Yes) in SETUP wizard then drop & recreate tables with same & different # of columns. Results replicate the DROP and the CREATE TABLE successfully. Table remains in autoprepare list

    • 5b) PREPARE new schema (DDL YES) with running replication, then drop & recreate tables with same & different # of columns Results replicate the DROP and the CREATE TABLE successfully. Table remains in autoprepare list

    Schema Replication, DDL -NO

  6. PREPARE new schema (DDL=No) with running replication, then drop & recreate tables with same & different # of columns (DDL NO). Schema was dropped on source & not on target per NO DDL. Table was not in autoprepare list

    Results since NODDL selected change was not replicated to target. This renamed table will not show up in autoprepare list, ie dbvrep> process autoprepare list

 File attached as well for download.