cancel
Showing results for 
Search instead for 
Did you mean: 

Replicating ASE to ASE for History Database excluding 'delete rows' on primary Site

Former Member
0 Kudos
244

We are installing a solution to dynamically replicate  all 'insert' and 'update' rows from our 'Production Server' to the 'Archive|History Server';

Our goal is to eliminate the actual 'By Night' Batch Operations.

Three tables on three different Databases are affected by that operation.

On each archiving Database (Replicate), the Tables Definitions are a little different...

For each, we have create a Replication Definition and a Subscription.

Because it is an Archiving Solution, any 'delete row' on Primary Tables should absolutely never be replicated.

We did not find how to replicate just 'insert|update rows' ???

former_member182259
Contributor
0 Kudos

Actually, dsi_command_convert works without HVAR  - although it is often used with it, it is not a requirement .....so Denis should simply do a dsi_command_convert of d2none.....

The other way that is better when you need to differentiate between OLTP deletes and archiving is to have the archive in a stored proc, mark the proc for replication but just don't have the destination subscribe to it (or even create a repdef for it).    No need to do all the stuff you were referring to - simply marking the proc for replication is enough.

Remember, eliminating archive deletes may still require allowing deletes from the app to go through.

Former Member
0 Kudos

Hi Jeff,,

Hi Mark,

thank for that Help

In found that URL on another Forum...  refering to  . . .'function string that simply throws the delete away' from Mark explaination .

Thi is specifically resolving my problem

Thank so much

http://groups.google.com/group/comp.databases.sybase/browse_thread/thread/5ad1a3687902ff83/20507eba3...william....@reuters.com

You need to create a new function string class and rewrite the delete

function strings for all your tables to do nothing.  You didn't specify
which

version of Replication Server you are using.  In version 11.5 it is easier

because you only need to redefine the rs_delete functions and inherit

everything else from the default function string class.  In 11.0 you must

redefine the rs_update and rs_insert function strings to be what they are in

the default function string class.  To do this in 11.5, create a new
function

string class no_delete_function_class.

1> create function string class no_delete_function_class

2> set parent to rs_default_function_class

3> go

Each table contains an rs_delete function string and you must override
the

default for each table where you do not want to replicate a delete.  To
alter

the delete function string for mytable with replication definition

mytable_repdef to not replicate:

1> create function string mytable_repdef.rs_delete

2> for no_delete_function_class

3> output language ''

4> go

To enable the no_delete_function class for the DSI connection to your

replicate database 'mydb' on server 'myserver', first suspend the connection:

1> suspend connection to myserver.mydb

2> go

Now alter the connection to use the new function string class:

1> alter connection to myserver.mydb

2> set function string class no_delete_function_class

3> go

Now resume the connection

1> resume connection to myserver.mydb

2> go

If you are using 11.0, you must redine the rs_insert and rs_delete
function

strings to the default.  You need not know what the defaults are, just
omit

the output clause as follows:

1> create function string mytable_repdef.rs_insert

2> for no_delete_function_class

3> go

1> create function string mytable_repdef.rs_update

2> for no_delete_function_class

3> go

former_member182259
Contributor
0 Kudos

While that works - and is something Mark was pointing out.....The information is extremely old and therefore doesn't show you that newer releases make this a whole lot easier.   dsi_command_convert is a whole lot less work.

Former Member
0 Kudos

Hi Mark,as you can see, the example (see my preceding reply) found on another Forum is reproducing (my understanding) one of your  suggestions.

Of course, i do not have to exclude all delete operations in the replicated databases.

More importantly, the same TABLE will be eventually part of a MSA Environment where all rows (including delete) will need to be replicated onto another Database.

Best,

Denis

Former Member
0 Kudos

Hi Jeff,

i think the 'dsi_command_convert' look too restrictive because the same 'database..table' will also have to be replicated into another (MSA) Database where all rows will have to be repelicated ?

Is it right ?

former_member182259
Contributor
0 Kudos

Not true - there is no relationship between MSA, HVAR or anything at all with respect to dsi_command_convert.   It is completely 100% independent.    You could have this:

create replication definition my_table_repdef

with primary at oltp.dbname

with all tables named <tabname>

....

create subscription my_table_subscr for my_table_repdef

with replicate at myreportdb.dbname

where some_cond = <value>

alter connection myreportdb.dbname

for replicate table named <tabname>

set dsi_command_convert to 'd2none'

....so ...1 repdef, 1 subscription and dsi_command_convert in entire RS setup.  No MSA, no HVAR no other requirements.  Unfortunately, too many confusingly think one or the other are necessary as functionality was added to make life easier for those other features, but dsi_command_convert doesn't need them (arguably, it is the other way around).

former_member182259
Contributor
0 Kudos

create database replication definition primary_dbrd

    with primary at primary_oltp.dbname

    not replicate DDL

    not replicate transactions in (do_not_rep,dba_maint)

go

create subscription primary_2_standby_sub

     for database replication definition primary_dbrd

     with primary at primary_oltp.dbname

     with replicate at standby_oltp.dbname

     without materialization

     subscribe to truncate table

go

create subscription primary_2_reporting_sub

     for database replication definition primary_dbrd

     with primary at primary_oltp.dbname

     with replicate at reporting_db.dbname

     without materialization

     subscribe to truncate table

go

alter connection to reporting_db.dbname set dsi_command_convert to 'd2none'

go

*****  DONE  ****

That's one MSA setup plus a reporting setup that doesn't get deletes.

Former Member
0 Kudos

Hi Jeff,

i tried to apply your example, but my implementation does not work.

The 'delete row' is still replicated !!!

Could you audit it ?

SYBDEV08:    use DBO_DEV_DB77

             create
table DBO_DEV_DB77..DBO_DEV_TB_90 (col1 int primary key, col2 varchar(100))

SYBDEV07:    use DBO_ARC_DB77

             create
table DBO_ARC_DB77..DBO_ARC_TB_90
(col1 int primary key, col2 varchar(100))

SYBDEV08:    select * from DBO_DEV_DB77..DBO_DEV_TB_90

col1        col2

----------- ---------------------

SYBDEV07:    select * from DBO_ARC_DB77..DBO_ARC_TB_90

col1        col2

----------- ---------------------

SRSSTA01:

create replication definition trd_DBO_DEV_TB_90_001

with primary at SYBDEV08.DBO_DEV_DB77

with primary table named 'DBO_DEV_TB_90'

with replicate table named 'DBO_ARC_TB_90'

(col1
int, col2 varchar(100))

primary key (col1)

replicate all columns

create subscription tsn_DBO_DEV_TB_90_001 for
trd_DBO_DEV_TB_90_001

with replicate at SYBDEV07.DBO_ARC_DB77

SRSSTA01:    alter connection to SYBDEV07.DBO_ARC_DB77

for replicate
table named dbo.
DBO_DEV_TB_90

set dsi_command_convert
to 'd2none'

suspend
connection to SYBDEV07.DBO_ARC_DB77

resume connection
to SYBDEV07.DBO_ARC_DB77

SYBDEV08:    exec
DBO_DEV_DB77..sp_setreptable DBO_DEV_TB_90, 'true'

insert
into DBO_DEV_DB77..DBO_DEV_TB_90 (col1,col2) values (10, "---")

SYBDEV08:    select * from DBO_DEV_DB77..DBO_DEV_TB_90

col1        col2

-----------
------------------------------------------------

        
10 ---

SYBDEV07:    select * from DBO_ARC_DB77..DBO_ARC_TB_90

col1        col2

-----------
------------------------------------------------

        
10 ---

SYBDEV08:   

delete
DBO_DEV_DB77..DBO_DEV_TB_90 where col1=10

SYBDEV08:    select * from DBO_DEV_DB77..DBO_DEV_TB_90

col1        col2

-----------
--------------------------------------------------

SYBDEV07:    select * from DBO_ARC_DB77..DBO_ARC_TB_90

col1        col2

-----------
--------------------------------------------------

kimon_moschandreou
Contributor
0 Kudos

very good answer, we have a similar setup where in an MSA environment we implemented non-replication of deletes using function strings on table level just like Mark proposed and the google group mentioned. dsi_command_convert is much simpler and clearer.

PS However since HVAR or RTL require a special license this is for "special " customers.

former_member182259
Contributor
0 Kudos

The problem is you used:

alter connection....

for replicate table named dbo.tablename

....instead of

alter connection ...

for replicate table named tablename

Not sure why you used the dbo - you never marked it with owner on and the repdef also didn't have the owner - but regardless, one of the current (bug filed) restrictions of dsi_command_convert is that it doesn't pick up the owner clause (in this case the owner of the replicate table vs. the primary).

former_member182259
Contributor
0 Kudos

...oh - and you may have to bounce RS to clear the cache of rs_tbconfig (where table level configs are stored ala dsi_command_convert, etc.).

Former Member
0 Kudos

Hi Jeff,

Now it work fine !

I will now test that into a Work Load Environment.

Thank so much for your Help.

Best Regards,

Denis

former_member182259
Contributor
0 Kudos

Kimon - If you read my earlier answer, dsi_command_convert does not need the ASO license as it is unrelated to HVAR/RTL.   This is a common misconception.   It is also the way to get "autocorrection" when only using database repdef/subscription ala MSA as autocorrection is simply "i2di,u2di" in dsi_command_convert - and consequently since it is the autocorrection method for MSA, it cannot be limited to ASO licenses.

kimon_moschandreou
Contributor
0 Kudos

Thank you for noticing this detail

Accepted Solutions (0)

Answers (1)

Answers (1)

0 Kudos

I used to work in an environment with the similar requirement like yours. Instead of implementing changes in repserver, we implement a solution at the archive ASE. We enbale the trigger for replicated ASE and created a delete trigger for each objects. In the trigger , we explicitly issue "rollback transaction" when the user who issue the delete is the maintenance user. It does require a bit of coding if you have lots of tables. Just another method like to share.