How to skip "deletes" to replicated databases

Ev

unread,

Jun 14, 2004, 1:27:01 PM6/14/04

to

I need to replicate from active database to replicated only inserts and
updates. is it possible to change function string
to ignore "deletes"

Rob Verschoor

unread,

Jun 14, 2004, 10:19:07 PM6/14/04

to

"Ev" <E...@passport.com> wrote in message news:40cde043$1@forums-2-dub...

> I need to replicate from active database to replicated only inserts and
> updates. is it possible to change function string
> to ignore "deletes"
>
>

By default, the standby connection uses rs_default_function_class, which you
cannot modify.
There is no supported way of changing the function string class for a
standby connection (an undocumented+unsupported command actually exists, but
there's probably a good reason why it isn't official -- so don't consider
using it).

So the answer is no...
As an alternative, you can just run 'set replication off' before running a
delete in the active DB. WHen finished, do 'set replication on'. Basically,
anything executed in a primary (or active) DB while 'set replication off'
is in effect will not be picked up by the RepAgent, so it won't be
replicated.

If you're on RS 12.6, you can also use database repdefs, which are quite
similar to a warm standby in many ways. When combining database repdefs with
table repdefs, you can actually modify the function strings for those
repdefs.

HTH,

Rob
-------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 12.5/12.0/11.5/11.0
and Replication Server 12.5

Author of various Sybase books. New book (coming soon): "The
Complete Sybase Replication Server Quick Reference Guide"
Online orders accepted at http://www.sypron.nl/shop

mailto:r...@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., P.O.Box 10695, 2501HR Den Haag, The Netherlands
-------------------------------------------------------------

Mark A. Parsons

unread,

Jun 15, 2004, 2:10:11 PM6/15/04

to

One quick option would be to create a delete trigger on the replicate table
which issues a 'rollback trigger'. (Make sure you DO NOT issue a 'rollback
transaction'!!!!)

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

As for a RS option ...

You mention 'active database' (which would imply that you're using the warm
standby option) and 'replicated' (which could imply 'normal' replication
between primary and replicate databases).

First off ... are you working with a warm standby setup (active/standby) or
'normal' setup (primary/replicate)?

If warm standby ... see Rob's reply.

If 'normal' replication you can modify the rs_delete function string to do
what you want ... though this will depend on your version of RS ... as well
as what RDBMS you're replicating to/from (ASE->ASE, ASE->Oracle,
Oracle->UDB, ?????) ...

So, assuming ASE->ASE and RS 12+:

- RS supplies a *base* function string class called
rs_default_function_class; you cannot change the function string
definitions for this class

- RS also supplies a *derived* function string class called
rs_sqlserver_function_class (derived from rs_default_function_class); this
is the default function string class utilized by RS in a 'normal'
replication setup; the function strings for this class can be modified

- assuming a repdef called my_repdef, you could alter the rs_delete
function string as follows:

alter function string my_repdef.rs_delete
for rs_sqlserver_function_class
output language ''
or
alter function string my_repdef.rs_delete
for rs_sqlserver_function_class
output none

I've used the {output language ''} version before ... not sure about the
{output none} version (and I don't have access to RS at the moment).

--
Mark A. Parsons

Iron Horse, Inc.
iron_...@NOSPAM.compuserve.com

Michael Peppler

unread,

Jun 15, 2004, 3:00:06 PM6/15/04

to

On Tue, 15 Jun 2004 11:10:11 -0700, Mark A. Parsons wrote:

> Ev wrote:
>>
>> I need to replicate from active database to replicated only inserts and
>> updates. is it possible to change function string to ignore "deletes"
>
> One quick option would be to create a delete trigger on the replicate
> table which issues a 'rollback trigger'. (Make sure you DO NOT issue a
> 'rollback transaction'!!!!)

But that wouldn't work in a WS setup, as triggers don't fire on the
standby side...

Michael
--
Michael Peppler Data Migrations, Inc.
mpep...@peppler.org http://www.peppler.org/
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.peppler.org/resume.html

Rob Verschoor

unread,

Jun 16, 2004, 7:56:00 AM6/16/04

to

> - assuming a repdef called my_repdef, you could alter the rs_delete
> function string as follows:
>
> alter function string my_repdef.rs_delete
> for rs_sqlserver_function_class
> output language ''
> or
> alter function string my_repdef.rs_delete
> for rs_sqlserver_function_class
> output none
>

Just to clarify: you must use "output language '' " . "output language
none" is only for rs_writetext.

Rob V.

Ev

unread,

Jul 7, 2004, 11:16:13 AM7/7/04

to

We have active/warmstandby (ASE 12.5) and replication to Oracle database. I
needed to skip deletes for Oracle connection.
I modified function delete function :

create function string ORA_table_user.rs_delete
for rs_oracle_function_class
output language ''

"Mark A. Parsons" <iron_...@NOSPAM.compuserve.com> wrote in message
news:40CF3AC8...@NOSPAM.compuserve.com...

Jeff Tallman

unread,

Jul 7, 2004, 3:01:47 PM7/7/04

to

Careful - it looks like you put a null ('') string vs. a space - it used
to be (think it still is) that setting an fstring to '' vs ' ' resulted
in it using the default fstring definition instead of sending a whitespace.