Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

sql statement replication in a warm standby

9 posts in General Discussion Last posting was on 2012-10-09 08:40:39.0Z
Pieter Coene Posted on 2012-09-26 14:09:30.0Z
Sender: 55c9.506308ae.1804289383@sybase.com
From: Pieter Coene
Newsgroups: sybase.public.rep-server
Subject: sql statement replication in a warm standby
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50630c9a.5664.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 26 Sep 2012 07:09:30 -0700
X-Trace: forums-1-dub 1348668570 172.20.134.41 (26 Sep 2012 07:09:30 -0700)
X-Original-Trace: 26 Sep 2012 07:09:30 -0700, 172.20.134.41
Lines: 37
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9256
Article PK: 841885

Hi,

i'm trying to test the sql statement replication in our Warm
standby environment, but so far i'm not very succesful. (RS
15.7.1 and ASE 15.5)

Following the documentation, i enabled the functionality on
my logical connection:

>>> alter logical connection to LDS.lisdb set
ws_sqldml_replication to "on"

I did not change the thresholds (sybase docs say '50' as a
default)

Then in a session:

set repmode on 'D'
go
set rowcount 100
delete from test where id < ...
go

> 100 Rows affected

When i look into my Inbound Queue Data i see following:

delete from dbo.test where id = 1
delete from dbo.test where id = 2
delete from dbo.test where id = 3
...

So i guess that the statement replication did not work and
it replicates all the individual statements...

Any ideas?
Pieter


Pieter Coene Posted on 2012-09-26 15:14:00.0Z
Sender: 589e.50631ae6.1804289383@sybase.com
From: Pieter Coene
Newsgroups: sybase.public.rep-server
Subject: Re: sql statement replication in a warm standby
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50631bb8.58c2.1681692777@sybase.com>
References: <50630c9a.5664.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 26 Sep 2012 08:14:00 -0700
X-Trace: forums-1-dub 1348672440 172.20.134.41 (26 Sep 2012 08:14:00 -0700)
X-Original-Trace: 26 Sep 2012 08:14:00 -0700, 172.20.134.41
Lines: 48
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9257
Article PK: 870415

ok,

in the "exception" list, you can't do 'set rowcount x' where
x is greater than '0'.
So i tested with:

set rowcount 0
delete from test where id < ...

Nothing changes...still got the traditional replication

> Hi,
>
> i'm trying to test the sql statement replication in our
> Warm standby environment, but so far i'm not very
> succesful. (RS 15.7.1 and ASE 15.5)
>
> Following the documentation, i enabled the functionality
> on my logical connection:
>
> >>> alter logical connection to LDS.lisdb set
> ws_sqldml_replication to "on"
>
> I did not change the thresholds (sybase docs say '50' as a
> default)
>
> Then in a session:
>
> set repmode on 'D'
> go
> set rowcount 100
> delete from test where id < ...
> go
>
> > 100 Rows affected
>
> When i look into my Inbound Queue Data i see following:
>
> delete from dbo.test where id = 1
> delete from dbo.test where id = 2
> delete from dbo.test where id = 3
> ...
>
> So i guess that the statement replication did not work and
> it replicates all the individual statements...
>
> Any ideas?
> Pieter


"Mark A. Parsons" <iron_horse Posted on 2012-09-27 01:02:17.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.rep-server
Subject: Re: sql statement replication in a warm standby
References: <50630c9a.5664.1681692777@sybase.com> <50631bb8.58c2.1681692777@sybase.com>
In-Reply-To: <50631bb8.58c2.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120916-1, 09/16/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <5063a599$1@forums-1-dub>
Date: 26 Sep 2012 18:02:17 -0700
X-Trace: forums-1-dub 1348707737 172.20.134.152 (26 Sep 2012 18:02:17 -0700)
X-Original-Trace: 26 Sep 2012 18:02:17 -0700, vip152.sybase.com
Lines: 61
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9259
Article PK: 870416

The repagent is going to send the single DELETE statement as well as all of the individual DELETE statements since,
generally speaking, it doesn't know if the repserver will be able to use the single DELETE or have to use the individual
DELETEs.

It's then up to the repserver to decide if/when to submit the single DELETE to the replicate vs when to send the
individual DELETEs to the replicate.

While you could try to capture what's being sent by the DSI, I prefer to watch the contents of monSysSQLText in the RDS
to see what was actually sent to the RDB ... a single DELETE or the individual DELETEs?

Are you seeing the individual DELETEs in the RDS's monSysSQLText?

On 09/26/2012 09:14, Pieter Coene wrote:
> ok,
>
> in the "exception" list, you can't do 'set rowcount x' where
> x is greater than '0'.
> So i tested with:
>
> set rowcount 0
> delete from test where id< ...
>
> Nothing changes...still got the traditional replication
>
>> Hi,
>>
>> i'm trying to test the sql statement replication in our
>> Warm standby environment, but so far i'm not very
>> succesful. (RS 15.7.1 and ASE 15.5)
>>
>> Following the documentation, i enabled the functionality
>> on my logical connection:
>>
>>>>> alter logical connection to LDS.lisdb set
>> ws_sqldml_replication to "on"
>>
>> I did not change the thresholds (sybase docs say '50' as a
>> default)
>>
>> Then in a session:
>>
>> set repmode on 'D'
>> go
>> set rowcount 100
>> delete from test where id< ...
>> go
>>
>>> 100 Rows affected
>>
>> When i look into my Inbound Queue Data i see following:
>>
>> delete from dbo.test where id = 1
>> delete from dbo.test where id = 2
>> delete from dbo.test where id = 3
>> ...
>>
>> So i guess that the statement replication did not work and
>> it replicates all the individual statements...
>>
>> Any ideas?
>> Pieter


"Mark A. Parsons" <iron_horse Posted on 2012-09-27 12:35:06.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.rep-server
Subject: Re: sql statement replication in a warm standby
References: <5063a599$1@forums-1-dub> <506422f5.d5f.1681692777@sybase.com>
In-Reply-To: <506422f5.d5f.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 120916-1, 09/16/2012), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <506447fa@forums-1-dub>
Date: 27 Sep 2012 05:35:06 -0700
X-Trace: forums-1-dub 1348749306 172.20.134.152 (27 Sep 2012 05:35:06 -0700)
X-Original-Trace: 27 Sep 2012 05:35:06 -0700, vip152.sybase.com
Lines: 108
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9261
Article PK: 870417

Keep in mind ...

- the repagent has no idea how the repserver has been configured (eg, table-level rep, warm standby, MSA)
- the repagent determines what to send to the repserver based on table/database settings and repagent configurations

... so the repagent sends both the single DELETE statement and the individual DELETE statements.

If you want to cut down on the volume of activity sent to the repserver (eg, large data purges) I'd recommend you put
the purge code in a stored proc and mark the proc for replication. The repagent will send the stored proc invocation
and skip sending the DELETE statements.

On 09/27/2012 03:57, Pieter Coene wrote:
> Ok,
>
> I think i'm not fully understanding the flow here. When you
> say 'the repagent is going to send', does this mean the
> statements go straight to the inbound queue, and the
> repserver decides then which one to send to the Standby (we
> only have a Warm standby environment) OR the repserver
> decides which one to store in the inbound queue...
>
> The reason i want to know this is we want to get rid of some
> bottlenecks:
> (the RS server is installed on one of the DB servers, we
> switch Active<> Standby every quarter of a year)
>
> When performing some heavily purging:
> 1. When the active DB is the one where the RS is
> installed: stable queues are temporary growing cause it has
> to send all these individual statements to our standby site,
> which is the bottleneck
> 2. When the active DB is the other one: the bottleneck is
> sending the 'data' to our stable queues over the network
>
> Basicly: with our Warm Standby configuration, when purging
> for example, we just don't want to send all this data over
> the network...
>
> Greetz
> Pieter
>
>> The repagent is going to send the single DELETE statement
>> as well as all of the individual DELETE statements since,
>> generally speaking, it doesn't know if the repserver will
>> be able to use the single DELETE or have to use the
>> individual DELETEs.
>>
>> It's then up to the repserver to decide if/when to submit
>> the single DELETE to the replicate vs when to send the
>> individual DELETEs to the replicate.
>>
>> While you could try to capture what's being sent by the
>> DSI, I prefer to watch the contents of monSysSQLText in
>> the RDS to see what was actually sent to the RDB ... a
>> single DELETE or the individual DELETEs?
>>
>> Are you seeing the individual DELETEs in the RDS's
>> monSysSQLText?
>>
>> On 09/26/2012 09:14, Pieter Coene wrote:
>>> ok,
>>>
>>> in the "exception" list, you can't do 'set rowcount x'
>>> where x is greater than '0'.
>>> So i tested with:
>>>
>>> set rowcount 0
>>> delete from test where id< ...
>>>
>>> Nothing changes...still got the traditional replication
>>>
>>>> Hi,
>>>>
>>>> i'm trying to test the sql statement replication in our
>>>> Warm standby environment, but so far i'm not very
>>>> succesful. (RS 15.7.1 and ASE 15.5)
>>>>
>>>> Following the documentation, i enabled the
>> functionality>> on my logical connection:
>>>>
>>>>>>> alter logical connection to LDS.lisdb set
>>>> ws_sqldml_replication to "on"
>>>>
>>>> I did not change the thresholds (sybase docs say '50'
>> as a>> default)
>>>>
>>>> Then in a session:
>>>>
>>>> set repmode on 'D'
>>>> go
>>>> set rowcount 100
>>>> delete from test where id< ...
>>>> go
>>>>
>>>>> 100 Rows affected
>>>>
>>>> When i look into my Inbound Queue Data i see following:
>>>>
>>>> delete from dbo.test where id = 1
>>>> delete from dbo.test where id = 2
>>>> delete from dbo.test where id = 3
>>>> ...
>>>>
>>>> So i guess that the statement replication did not work
>> and>> it replicates all the individual statements...
>>>>
>>>> Any ideas?
>>>> Pieter


useless replication Posted on 2012-09-26 20:24:19.0Z
Sender: 603f.5063633e.1804289383@sybase.com
From: useless replication
Newsgroups: sybase.public.rep-server
Subject: Re: sql statement replication in a warm standby
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <50636473.6064.1681692777@sybase.com>
References: <50631bb8.58c2.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 26 Sep 2012 13:24:19 -0700
X-Trace: forums-1-dub 1348691059 172.20.134.41 (26 Sep 2012 13:24:19 -0700)
X-Original-Trace: 26 Sep 2012 13:24:19 -0700, 172.20.134.41
Lines: 51
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.rep-server:9258
Article PK: 870418

set repmode on 'DS' instead of set repmode on 'D'

> ok,
>
> in the "exception" list, you can't do 'set rowcount x'
> where x is greater than '0'.
> So i tested with:
>
> set rowcount 0
> delete from test where id < ...
>
> Nothing changes...still got the traditional replication
>
> > Hi,
> >
> > i'm trying to test the sql statement replication in our
> > Warm standby environment, but so far i'm not very
> > succesful. (RS 15.7.1 and ASE 15.5)
> >
> > Following the documentation, i enabled the functionality
> > on my logical connection:
> >
> > >>> alter logical connection to LDS.lisdb set
> > ws_sqldml_replication to "on"
> >
> > I did not change the thresholds (sybase docs say '50' as
> > a default)
> >
> > Then in a session:
> >
> > set repmode on 'D'
> > go
> > set rowcount 100
> > delete from test where id < ...
> > go
> >
> > > 100 Rows affected
> >
> > When i look into my Inbound Queue Data i see following:
> >
> > delete from dbo.test where id = 1
> > delete from dbo.test where id = 2
> > delete from dbo.test where id = 3
> > ...
> >
> > So i guess that the statement replication did not work
> > and it replicates all the individual statements...
> >
> > Any ideas?
> > Pieter