Is the best way to syncronize two tables in the same database creating triggers in the origin table or there is another way to do it? We have about 50 tables that need to be syncronized to another set of tables but the destination set is a simplified version of the tables. We are using SQL Anywhere 12.

asked 26 Sep '12, 10:58

MarcosCunhaLima's gravatar image

MarcosCunhaLima
3067918
accept rate: 0%

retagged 26 Sep '12, 11:20

RussC_FromSAP's gravatar image

RussC_FromSAP
1.3k11030


SQL Anywhere synchronization is done either via MobiLink (synchronous replication) or SQL Remote (asynchronous replication). Both of these only work with tables in different databases. Doing anything with the same database will involve trigges and such. I'll see if this question can be tagged a better way.

My first thought is that what you want is views.

  • Russ
permanent link

answered 26 Sep '12, 11:18

RussC_FromSAP's gravatar image

RussC_FromSAP
1.3k11030
accept rate: 20%

I agree with Russ that views sound like what you want.

(26 Sep '12, 11:26) Reg Domaratzki
Comment Text Removed

We designed the synchronization is two steps:

1) The tables is the hot database would be synchronized with another set of tables in the same database. 2) Those tables then would be synchronized with another database.

That way, we could completely isolate the changes between both databases.

Can a view be used with Mobilink in a synchronization?

(27 Sep '12, 08:52) MarcosCunhaLima
Replies hidden
1

Views can be used in a MobiLink environment in the consolidated database, as long as the view is updated-able. The view you describe that uses a subset of columns from a single table should be update-able. Views cannot be used at a remote site, where dbmlsync runs. Why? The MobiLink Server relies on an ODBC connection to execute queries you write (or you let our product write for you) to keep data in synch, but the on the remote side, dbnlsync scans the transaction log for changes, and only changes to base tables are written to the log, so views can't be used at the remote.

(27 Sep '12, 09:13) Reg Domaratzki

Those tables then would be synchronized with another database.

Are you relating to MobiLink/SQL Remote, or is this "just a export data to another database", as doable with remote data access/proxy tables?

That way, we could completely isolate the changes between both databases.

Could you tell why you like to somehow "separate" to access of the "hot tables" and the "synchronized ones"? - FWIW, IMHO views would not help here as then the synchronization would still need to query the underlying base tables.

But possibly I have just completely missed the point of your reuqirements...

(27 Sep '12, 10:05) Volker Barth

Views can be used for scripted uploads, I believe, but that can get quite complex to do robustly.

(27 Sep '12, 16:27) RussC_FromSAP

Volker It's just an export data to another database. It could be done through proxy tables but I had a terrible experience with it (with SQL Anywhere 5). The option of isolated tables is because we need to control how and when the data would be synchronized between both databases. Hence, we should control when and how the about-to-be synchronized tables (in the same database) will be updated. Afterwards, through Mobilink/SQL Remote, the other database would be automaticaly synchronized.

Moreover, the hot tables should keep all the records of each table but the about-to-be synchronized tables need just a small set of records (just the last-month records).

(28 Sep '12, 15:09) MarcosCunhaLima

Three remarks:

  • As to the bad experience with proxy tables in v5: I can't comment on that, other that my experience with proxy tables under v5.5.05 has been fine - both with other SA databases or MS SQL Server remotes. And v5 is soooo old - I'd really give this a try with a current version. In particular v12 has seen remarkable performance optimizations w.r.t. proxy tables.

  • Synchronizing "just some records" from a huge table is no problem both for SQL Remote and MobiLink, as you can specify filters on what to synchronize when defining the publications.

  • If you need to somehow "control" when existing data is ready to be synchronized (say, by explicitly marking it as "ready") and that control logic cannot be expressed as a logical condition, then I would think that you really will have to use separate tables and can't use views. (One might also think to use manually-refreshened materialized views but I'm quite sure those cannot be part neither of a ML nor a SQL Remote setup.)

Just my 2 cents, apparently:)

(30 Sep '12, 11:33) Volker Barth
showing 2 of 7 show all flat view
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×90
×59
×25

question asked: 26 Sep '12, 10:58

question was seen: 1,029 times

last updated: 30 Sep '12, 11:33