SQL Anywhere Mobile - Mobilink client reliance on database log file.

Author

Mobilink client reliance on database log file.

Peter Brooks

2005-10-27, 8:20 am

We are trying to develop a solution where a remote client could need
to synchronise with multiple consolidated databases:

Scenario:
A bank has a nation wide consolidated settlement booking system that
uses ASA 9.02. All entries added to their consolidated databases are
synchronised down to different remote databases (also ASA 9.02)
depending on which of their remote "Agents" are responsible for the
settlement. We now have a second bank that will be setting up their
own consolidated database (also ASA 9.02) and would also like to
synchronise their entries with remote "Agents". The remote Agents used
by both banks could be the same remote database.

Are we able to have the same remote database synchronise with
different consolidated systems.

It would be great if we did not need to rely on the remote database
transaction log files and we could then handle the logic to identify
entries to be uploaded to the consolidated for sync.

Thanks,

Peter Brooks

Breck Carter [TeamSybase]

2005-10-27, 8:20 am

Script based uploads will be introduced in Jasper, the next version of
SQL Anywhere. You can read about it at
http://www.ianywhere.com/promos/sqlanybeta/index.html

What you need to do violates one of the basic design assumptions of
MobiLink: the relationship among databases is hierarchical with a
single consolidated database synchronizing with one or more remote
databases. That means to do what you need to do will involve some
fancy footwork in your implementation. This is true with version 9,
and may still be true in Jasper.

One brute-force approach might be to use remote database triggers to
keep a second "slave" proxy database up to date, and use that proxy
database as remote for the other consolidated. Two remotes, two log
files, two consolidated databases... but only one remote database as
far as your application is concerned.

It is also possible to do this with a single remote database but the
solution depends on the details of your schema and requirements.

Breck

On 8 Oct 2005 20:48:09 -0700, Peter Brooks <admin@Infobase.com.au>
wrote:

>We are trying to develop a solution where a remote client could need
>to synchronise with multiple consolidated databases:
>
>Scenario:
>A bank has a nation wide consolidated settlement booking system that
>uses ASA 9.02. All entries added to their consolidated databases are
>synchronised down to different remote databases (also ASA 9.02)
>depending on which of their remote "Agents" are responsible for the
>settlement. We now have a second bank that will be setting up their
>own consolidated database (also ASA 9.02) and would also like to
>synchronise their entries with remote "Agents". The remote Agents used
>by both banks could be the same remote database.
>
>Are we able to have the same remote database synchronise with
>different consolidated systems.
>
>It would be great if we did not need to rely on the remote database
>transaction log files and we could then handle the logic to identify
>entries to be uploaded to the consolidated for sync.
>
>Thanks,
>
>Peter Brooks

--
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

Daigo Moriwaki

2005-10-27, 8:20 am

Peter Brooks wrote:
> It would be great if we did not need to rely on the remote database
> transaction log files and we could then handle the logic to identify
> entries to be uploaded to the consolidated for sync.

As of now transaction logs are required. As Breck said, what you hope is Jasper's new
feature.

> Are we able to have the same remote database synchronise with
> different consolidated systems.

You can if you define multiple publications for each consolidated db. Offset values
to upload rows belong to publications (subscriptions). Rows downloaded from bank 2
could override current rows from bank 1. Making a column for bank id avoids this issue.

For example,

CREATE TABLE "Booking" (
"pk" integer NOT NULL,
"bankid" integer NOT NULL,
"booking_at" "datetime" NULL DEFAULT timestamp,
PRIMARY KEY ( "pk", "bankid" )
);

CREATE PUBLICATION "pub1" (
TABLE "Booking" WHERE bankid = 1
);

CREATE SYNCHRONIZATION SUBSCRIPTION
TO "pub1"
FOR "mluser"
TYPE 'TCPIP' ADDRESS 'host=host1';

CREATE PUBLICATION "pub2" (
TABLE "Booking" WHERE bankid = 2
);

CREATE SYNCHRONIZATION SUBSCRIPTION
TO "pub2"
FOR "mluser"
TYPE 'TCPIP' ADDRESS 'host=host2';

Then, dbmlsync -c "dsn=remote" -n pub1 communicates with host1 for bank 1 and
dbmlsync -c "dsn=remote" -n pub1 does with host2 for bank2.

Thanks,
Daigo

--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki@ianywhere.com

Daigo Moriwaki

2005-10-27, 8:20 am

Daigo Moriwaki wrote:
> Then, dbmlsync -c "dsn=remote" -n pub1 communicates with host1 for bank 1 and
> dbmlsync -c "dsn=remote" -n pub1 does with host2 for bank2.

My mistake. dbmlsync -c "dsn=remote" -n pub1 and dbmlsync -c "dsn=remote" -n pub2

Daigo

--
Daigo Moriwaki
iAnywhere Solutions K.K. [Tokyo]
daigo.moriwaki@ianywhere.com

Breck Carter [TeamSybase]

2005-10-27, 8:21 am

Daigo is correct, it *is* as easy as using two publications.

I had this "false memory" of some limitation against having the same
table in two different publications... I say the memory is false
because I can't find any mention of it going back to V7.

If I'm going to have false memories, I wish they were of *good* things
:)

Breck

On 8 Oct 2005 20:48:09 -0700, Peter Brooks <admin@Infobase.com.au>
wrote:

>We are trying to develop a solution where a remote client could need
>to synchronise with multiple consolidated databases:
>
>Scenario:
>A bank has a nation wide consolidated settlement booking system that
>uses ASA 9.02. All entries added to their consolidated databases are
>synchronised down to different remote databases (also ASA 9.02)
>depending on which of their remote "Agents" are responsible for the
>settlement. We now have a second bank that will be setting up their
>own consolidated database (also ASA 9.02) and would also like to
>synchronise their entries with remote "Agents". The remote Agents used
>by both banks could be the same remote database.
>
>Are we able to have the same remote database synchronise with
>different consolidated systems.
>
>It would be great if we did not need to rely on the remote database
>transaction log files and we could then handle the logic to identify
>entries to be uploaded to the consolidated for sync.
>
>Thanks,
>
>Peter Brooks

--
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

David Fishburn

2005-10-27, 8:21 am

"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
news:725qk158bibaok6
q1m4acqrhtu6ofrq9ru@
4ax.com of
sybase.public.sqlanywhere.mobilink:

BC> Daigo is correct, it *is* as easy as using two publications.
BC>
BC> I had this "false memory" of some limitation against having the same
BC> table in two different publications... I say the memory is false
BC> because I can't find any mention of it going back to V7.

I believe the issue you were thinking of is this.

Some people want to use an ASA remote to _passthrough_ changes from 1
consolidated to another.

For example, they want to synchronize changes from one ASA server to
another. So the picture looks like this:

ASA_cons1 ASA_cons2
\ /
\ /
ASA_remote

The issue with this setup is:
1. The ASA_remote download row 100 from ASA_cons1.
2. ASA_remote syncs against ASA_cons2.
- At this point some people _expect_ row 100 to be
uploaded to ASA_cons2. Any download rows are not
uploaded unless they were explicitly changed at
ASA_remote.
- This is necessary to prevent circular (read endless)
loops.

Jasper will be capable of this, but you will have to code the _smarts_
so that you do not end up with circular loops.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Breck Carter [TeamSybase]

2005-10-27, 8:21 am

Yes, that is frightening too, but my false memory was of an actual
MobiLink error message. Perhaps it is this one, reported today by
Alexey Panshin (how small is this world anyway? :)...

E. 10/11 16:15:06. Publications main and so have a column subset
mismatch on
table Partners.
E. 10/11 16:15:06. No synchronization can occur until this mismatch is
resolved.

Perhaps I was trying to sync different parts of the same table at
different times (blob versus non-blob).

Anyway, as you point out it is all *still* complex, from a business
and design point of view, even if MobiLink makes it look easy.

Breck

On 12 Oct 2005 08:11:24 -0700, David Fishburn
<fishburn_spam@off.ianywhere.com> wrote:

>"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
> news:725qk158bibaok6
q1m4acqrhtu6ofrq9ru@
4ax.com of
>sybase.public.sqlanywhere.mobilink:
>
>BC> Daigo is correct, it *is* as easy as using two publications.
>BC>
>BC> I had this "false memory" of some limitation against having the same
>BC> table in two different publications... I say the memory is false
>BC> because I can't find any mention of it going back to V7.
>
>I believe the issue you were thinking of is this.
>
>Some people want to use an ASA remote to _passthrough_ changes from 1
>consolidated to another.
>
>For example, they want to synchronize changes from one ASA server to
>another. So the picture looks like this:
>
>
> ASA_cons1 ASA_cons2
> \ /
> \ /
> ASA_remote
>
>
>
>The issue with this setup is:
>1. The ASA_remote download row 100 from ASA_cons1.
>2. ASA_remote syncs against ASA_cons2.
> - At this point some people _expect_ row 100 to be
> uploaded to ASA_cons2. Any download rows are not
> uploaded unless they were explicitly changed at
> ASA_remote.
> - This is necessary to prevent circular (read endless)
> loops.
>
>Jasper will be capable of this, but you will have to code the _smarts_
>so that you do not end up with circular loops.
>
>

--
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

Sponsored Links