|
Home > Archive > SQL Anywhere database replication > January 2006 > "Singlizing"
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
|
|
| Pavel Karady 2006-01-18, 7:23 am |
| Greetings replication wizards,
I'd like to know why if one processes an UPDATE statement that updates 8000
rows, the remote agent sends these updates one-by-one. Is it because they
have been written into the transaction log one by one?
If there's any documentation or another thread about this philosophy, I'll
accept pointing to it or to some part of the help as a sufficient answer.
Thanks very much
Pavel
| |
| Breck Carter [TeamSybase] 2006-01-18, 7:23 am |
| That's what it does, it replicates the actual changes made to the
database rows, it does not replicate the high-level statements
executed, because the result could be different.
For example, UPDATE X SET Y = Y + 1 might change a Y to one value on
one database but another value on a different database, for the same
row, because they originally had different values.
Breck
On 18 Jan 2006 02:32:04 -0800, "Pavel Karady"
<pavel_ns. ns_karady@ns_kogerus
a.com> wrote:
>Greetings replication wizards,
>
>I'd like to know why if one processes an UPDATE statement that updates 8000
>rows, the remote agent sends these updates one-by-one. Is it because they
>have been written into the transaction log one by one?
>
>If there's any documentation or another thread about this philosophy, I'll
>accept pointing to it or to some part of the help as a sufficient answer.
>
>Thanks very much
>Pavel
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Reg Domaratzki 2006-01-18, 7:23 am |
| It's mainly because they are written to the transaction log one by one, but
it's definitely by design. Imagine you have a remote database that holds a
subset of rows that exist on the consolidated. Assume only ten rows exist
on the remote, but 10,000,000 rows exist on the consolidated. If you
execute the following on the remote :
update t1 set c1=2
all 10 rows will be updated on the remote. Do you really want to send this
exact same update statement to the consolidated database where 10,000,000
rows will be updated?
--
Reg Domaratzki, Sybase iAnywhere Solutions
Certified SQL Anywhere Associate
Please reply only to the newsgroup
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL and click "GO"
"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43ce1924$1@foru
ms-1-dub...
> Greetings replication wizards,
>
> I'd like to know why if one processes an UPDATE statement that updates
8000
> rows, the remote agent sends these updates one-by-one. Is it because they
> have been written into the transaction log one by one?
>
> If there's any documentation or another thread about this philosophy, I'll
> accept pointing to it or to some part of the help as a sufficient answer.
>
> Thanks very much
> Pavel
>
>
| |
| Pavel Karady 2006-01-18, 9:23 am |
| Breck, Reg, thanks for your answers, my comment is below.
"Reg Domaratzki" <nospam.please@sympatico.ca> wrote in message
news:43ce41b3$1@foru
ms-1-dub...
>Assume only ten rows exist on the remote, but 10,000,000 rows exist on the
>consolidated. If you
> execute the following on the remote :
>
> update t1 set c1=2
>
> all 10 rows will be updated on the remote. Do you really want to send
> this
> exact same update statement to the consolidated database where 10,000,000
> rows will be updated?
That totally jarps my thoughts about replication logics. I thought
replication has to be synchronized on table level - that replicated tables
have to have the same rows in both (or more than two) databases. I didn't
think about any table put in replication which has different, and as you
say, *much* different count of rows that it's other "equivalent".
From my previous point of view, the necessity of one-by-one statements
seemed to be somewhat weird since I thought, if the tables have the same
rows, then only the order of processed statements is important.
It's still hard to believe that a table that exists in two databases and is
in replication, has 10 mil rows in the first db and 10 rows in the second.
Can you please provide me with any real world examples, why this should be
like that?
Thank you guys, my understanding of SQL Anywhere is mostly in your hands.
Pavel
| |
| Breck Carter [TeamSybase] 2006-01-18, 9:23 am |
| A better example than mine, a testimonial to your *deeper* deviance :)
The exception is how trigger actions are handled by default.
Breck
On 18 Jan 2006 05:25:07 -0800, "Reg Domaratzki"
<nospam.please@sympatico.ca> wrote:
>It's mainly because they are written to the transaction log one by one, but
>it's definitely by design. Imagine you have a remote database that holds a
>subset of rows that exist on the consolidated. Assume only ten rows exist
>on the remote, but 10,000,000 rows exist on the consolidated. If you
>execute the following on the remote :
>
>update t1 set c1=2
>
>all 10 rows will be updated on the remote. Do you really want to send this
>exact same update statement to the consolidated database where 10,000,000
>rows will be updated?
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Breck Carter [TeamSybase] 2006-01-18, 9:23 am |
| On 18 Jan 2006 06:21:12 -0800, "Pavel Karady"
<pavel_ns. ns_karady@ns_kogerus
a.com> wrote:
>It's still hard to believe that a table that exists in two databases and is
>in replication, has 10 mil rows in the first db and 10 rows in the second.
>Can you please provide me with any real world examples, why this should be
>like that?
That is the most common case: a large consolidated database consisting
of an entire company's data, plus several thousand smaller remote
databases, each one in the hands of a single employee and containing
only data pertinent to that employee.
For some tables, everyone will have every row. But for other tables,
each single remote will only have the rows that are pertinent.
SQL Remote replicates changes made to individual columns in individual
rows.
Of course, if the two databases are identical, SQL Remote will keep
them that way. But it isn't the most common case.
Breck
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Rob Waywell 2006-01-18, 9:23 am |
| Pavel,
It is usually the other way around. Can you provide a real world example
where you would want to replicate an entire 120GB database to a laptop or
handheld device? With the exception of failover systems it is very uncommon
to replicate the full database. Data partitioning is a normal part of
designing a replication or synchronization system.
If you have had the opportunity to take the "Replicating Data Using SQL
Remote" course yet, then I would strongly recommend it:
Replicating Data using SQL Remote 9.0
http://www.sybase.com/detail?id=1026093
This course covers many real world design and implementation
requirements and digs into how replication works including how SQL Remote
uses and relies on the transaction log. There are many places in the course
where we look at exactly what is going into the log file.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43ce4bdb$1@foru
ms-2-dub...
> Breck, Reg, thanks for your answers, my comment is below.
>
> "Reg Domaratzki" <nospam.please@sympatico.ca> wrote in message
> news:43ce41b3$1@foru
ms-1-dub...
>
> That totally jarps my thoughts about replication logics. I thought
> replication has to be synchronized on table level - that replicated tables
> have to have the same rows in both (or more than two) databases. I didn't
> think about any table put in replication which has different, and as you
> say, *much* different count of rows that it's other "equivalent".
>
> From my previous point of view, the necessity of one-by-one statements
> seemed to be somewhat weird since I thought, if the tables have the same
> rows, then only the order of processed statements is important.
>
> It's still hard to believe that a table that exists in two databases and
> is in replication, has 10 mil rows in the first db and 10 rows in the
> second. Can you please provide me with any real world examples, why this
> should be like that?
>
> Thank you guys, my understanding of SQL Anywhere is mostly in your hands.
>
> Pavel
>
| |
| Reg Domaratzki \(iAnywhere Solutions\) 2006-01-18, 11:23 am |
| You define what rows go to which remote databases using the SUBSCRIBE BY
clause in your publication definition and given a subscribe by value when
subscribing a remote user to a given publication. I'd suggest you read over
the following seciton of the docs to get a better idea how this is done.
SQL Remote User's Guide
SQL Remote Design for Adaptive Server Anywhere
Publishing data
Publishing whole tables
Publishing only some columns in a table
Publishing only some rows in a table
Publishing only some rows using a WHERE clause
Publishing only some rows using a subscription expression
There is a link in my footer to the product documentation.
As Breck points out, replicating (or synchronizing) a sub-set of the data
from the consolidated is the much more common than replicating the entire
database.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup
iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:8okss1l0nu3gh2a
39v4uu4m3k6cto1hprf@
4ax.com...
> On 18 Jan 2006 06:21:12 -0800, "Pavel Karady"
> <pavel_ns. ns_karady@ns_kogerus
a.com> wrote:
>
is[color=darkred]
second.[color=darkred]
be[color=darkred]
>
> That is the most common case: a large consolidated database consisting
> of an entire company's data, plus several thousand smaller remote
> databases, each one in the hands of a single employee and containing
> only data pertinent to that employee.
>
> For some tables, everyone will have every row. But for other tables,
> each single remote will only have the rows that are pertinent.
>
> SQL Remote replicates changes made to individual columns in individual
> rows.
>
> Of course, if the two databases are identical, SQL Remote will keep
> them that way. But it isn't the most common case.
>
> Breck
>
> --
> SQL Anywhere Studio 9 Developer's Guide
> Buy the book:
http://www.amazon.com/exec/obidos/A...7/risingroad-20
> bcarter@risingroad.com
> RisingRoad SQL Anywhere and MobiLink Professional Services
> www.risingroad.com
| |
| Pavel Karady 2006-01-24, 7:23 am |
| Thanks everyone for showing how does it work in real (other) world; that's
what I needed.
The "Replicating Data Using SQL Remote" course would be fine, if I'll have
the opportunity to come to United States ;) But if everything will go well,
I'll drop in for two weeks this year; I'll pass the course request to our
management and try to underline the importance of it :)
Would a request for some setting of how to replicate /a) one-by-one
statements or b) single, but mass-result statements/ be appropriate in the
product_futures_disc
ussion?
Pavel
| |
| Reg Domaratzki 2006-01-24, 7:23 am |
| You can use the passthrough statament if you really want to pass the actual
SQL statements that are executed via SQL Remote.
--
Reg Domaratzki, Sybase iAnywhere Solutions
Certified SQL Anywhere Associate
Please reply only to the newsgroup
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL and click "GO"
"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43d5fcb5$1@foru
ms-1-dub...
> Thanks everyone for showing how does it work in real (other) world; that's
> what I needed.
>
> The "Replicating Data Using SQL Remote" course would be fine, if I'll have
> the opportunity to come to United States ;) But if everything will go
well,
> I'll drop in for two weeks this year; I'll pass the course request to our
> management and try to underline the importance of it :)
>
> Would a request for some setting of how to replicate /a) one-by-one
> statements or b) single, but mass-result statements/ be appropriate in the
> product_futures_disc
ussion?
>
> Pavel
>
>
| |
| Pavel Karady 2006-01-24, 9:23 am |
| "Reg Domaratzki" <nospam.please@sympatico.ca> wrote in message
news:43d629c9$1@foru
ms-1-dub...
> You can use the passthrough statament if you really want to pass the
> actual
> SQL statements that are executed via SQL Remote.
That really solved it.
Thanks, hugs and kisses,
Pavel
| |
| Pavel Karady 2006-01-24, 9:23 am |
| Is there any book on the market dedicated solely to replication in SQL
Anywhere Studio?
Thank you
Pavel
| |
| Volker Barth 2006-01-25, 3:26 am |
| Hi Pavel,
while I don't know of any book about SQL Remote
(furthermore: I don't know of any current ASA book besides
the one Breck wrote ;-), Breck has a detailed tutorial for
SQL Remote replication on his former website:
http://www.bcarter.com/tip078.htm
It refers to SQL Anywhere 5.5 and Win16, so it is somehow
outdated, but I made it through these chapters once and
found a lot of useful information there, as Breck shows the
results of every replication step in detail.
And I guess the basic principles of SQL Remote haven't
changed that much in ASA 8 or 9 so it should still be
usable.
HTH
Volker
Pavel wrote:
> Is there any book on the market dedicated solely to
> replication in SQL Anywhere Studio?
>
> Thank you
> Pavel
>
>
| |
| Breck Carter [TeamSybase] 2006-01-26, 4:56 pm |
| SQL Remote was one of the big topics omitted from my book for reasons
of space; UltraLite was the other one.
Omitting UltraLite made me sad, but not SQL Remote... 99% of the folks
using SQL Remote have been doing so for a long time and understand all
its quirks. For you that doesn't help, but the GOOD NEWS is there are
a lot of folks who can answer your questions in the "replication"
newsgroup.
FWIW a lot of folks (not all, but a lot) regard MobiLink as a
replacement for SQL Remote, and that *is* covered in the book.
Breck
On 24 Jan 2006 07:32:03 -0800, "Pavel Karady"
<pavel_ns. ns_karady@ns_kogerus
a.com> wrote:
>Is there any book on the market dedicated solely to replication in SQL
>Anywhere Studio?
>
>Thank you
>Pavel
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Pavel Karady 2006-01-26, 4:56 pm |
| "Reg Domaratzki" <nospam.please@sympatico.ca> wrote in message
news:43d629c9$1@foru
ms-1-dub...
> You can use the passthrough statament if you really want to pass the
> actual
> SQL statements that are executed via SQL Remote.
The PASSTHROUGH statement was tested and it does exactly what we wanted to
achieve. But here's the shock (at least for us): it's connection dependent.
Thus, this renders it unusable for us.
If there could be a way, how to ensure that every connection, even through
dbisqlc only, has the PASSTHROUGH as the first statement processed, it would
be fine (1). Or, having a dbremote agent with a passthrough switch would be
fine (2).
If (1) is possible, then (2) is not necessary. Is there any way?
Thanks - thanks
Pavel
| |
| Rob Waywell 2006-01-27, 8:23 pm |
| Sounds like a good time to start a new thread with an example of what you
have tried.
--
-----------------------------------------------
Robert Waywell
Sybase Adaptive Server Anywhere Developer - Version 8
Sybase Certified Professional
Sybase's iAnywhere Solutions
Please respond ONLY to newsgroup
EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all
To Submit Bug Reports:
http://case-express.sybase.com/cx/c...sc?CASETYPE=Bug
SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Pavel Karady" <pavel_ns. ns_karady@ns_kogerus
a.com> wrote in message
news:43d8e66e@forums
-1-dub...
> "Reg Domaratzki" <nospam.please@sympatico.ca> wrote in message
> news:43d629c9$1@foru
ms-1-dub...
>
> The PASSTHROUGH statement was tested and it does exactly what we wanted to
> achieve. But here's the shock (at least for us): it's connection
> dependent. Thus, this renders it unusable for us.
>
> If there could be a way, how to ensure that every connection, even through
> dbisqlc only, has the PASSTHROUGH as the first statement processed, it
> would be fine (1). Or, having a dbremote agent with a passthrough switch
> would be fine (2).
>
> If (1) is possible, then (2) is not necessary. Is there any way?
>
> Thanks - thanks
> Pavel
>
| |
| Pavel Karady 2006-01-30, 7:23 am |
| "Rob Waywell" < rwaywell_no_spam_ple
ase@ianywhere.com> wrote in message
news:43da9b84$1@foru
ms-2-dub...
> Sounds like a good time to start a new thread with an example of what you
> have tried.
<soft>Ooooh yeah.</soft> Thanks.
Pavel
|
|
|
|
|