04 - Excluding Columns

Sometimes not whole tables need to be replicated or you can have a column, that is not supported by DBVisit Replicate. Replicate offers a feature of excluding columns. 

There are 2 methods to exclude the columns:

1. Editing the resulting .dbvrep file after the setup wizard has completed and before the *all.sh or *all.bat script is run. 
2. Issuing the exclude column command in the console. 

In our example we have already replication set up, so we will use the second option.

For our example we don't want to replicate a PRODUCT_DESCRIPTION column in table REPOE.PRODUCT_INFORMATION. It is a large varchar2 value and we hope, that excluding this column from replication will save us some space and redo traffic while replicating.

We will exclude the column from the replicate. Lets run following command from the dbvrep console on mine side:

dbvrep> exclude column REPOE.PRODUCT_INFORMATION.PRODUCT_DESCRIPTION 

After issuing this command we have to restart the mine process.

dbvrep> shutdown mine
Dbvisit Replicate mine process shutting down.
./repoe-run-source.sh

Lets check, what this command exactly does. We will insert a testing row to the source database:

insert into repoe.product_information (product_id, product_name, product_description, category_id) 
values (10000, 'DBVisit Replicate', 'The best software for online replication...', 1000);
commit;

After veryfying that the row was successfully replicated in dbvrep console, we can check, that the column was really excluded. Issue following query to the target database:

set lines 200 pages 999
col product_description for a40
select product_id, product_name, product_description, category_id from repoe.product_information where product_id=10000;

We can see, that the PRODUCT_DESCRIPTION column is empty. If we would like to see how was the source DML transformed, we will create a conflict and than we will use list conflict command to see the query, that failed.

To create a conflict we will delete on target side the row we just created:

delete from repoe.product_information where product_id=10000;
commit;

On source database we will make an update of this row:

update repoe.product_information 
set product_name='DBVisit Standby', product_description='The best software for disaster recovery solution' 
where product_id=10000;
commit;

In the dbvrep console wait few seconds before the conflict appears and run list conflict command:

dbvrep> list conflict
Information for conflict 64010011254 (current conflict):
Table: REPOE.PRODUCT_INFORMATION at transaction 0009.007.0000064e at SCN 1331348
SQL text (with replaced bind values): update "REPOE"."PRODUCT_INFORMATION"
set "PRODUCT_NAME" = 'DBVisit Standby'
where (1=1)
and "PRODUCT_NAME" = 'DBVisit Replicate'
and "PRODUCT_ID" = 10000

From the output you can see, that even if we updated the PRODUCT_DESCRIPTION column there is no sign about it in the query, that should be run by apply to the target database.

Lets fix the conflict - insert to the target database the same row as we once inserted:

insert into repoe.product_information (product_id, product_name, product_description, category_id) 
values (10000, 'DBVisit Replicate', 'The best software for online replication...', 1000);
commit;

And resolve the previous conflict as retry:

dbvrep> resolve conflict 64010011254 as retry
Conflict resolution set.

For learning more about Excluding Columns read Excluding columns from replication on our online wiki.