I have a dbremote application that uses a single table to maintain all the keypools for PK's I noticed for mobilink all the samples show these maintained at each table. Would not the same approach work for Mobilink as with dbremote by just chaning out the pub user to the ml_user?

asked 31 Mar '16, 12:36

TPS's gravatar image

TPS
196131320
accept rate: 0%


If your solutiion works for SQL Remote, it will probably work for MobiLink.

FWIW most folks are using DEFAULT GLOBAL AUTOINCREMENT rather than key pools... better performance, fewer problems.

permanent link

answered 31 Mar '16, 12:41

Breck%20Carter's gravatar image

Breck Carter
26.9k438609883
accept rate: 21%

Can you restrict which users get these? say I have just 10 users out of the 2000 that will be making any inserts to the tables.

Or even better can this be done at the table level where 10 users have inserts on most tables but all users on a handful of tables?

(31 Mar '16, 15:35) TPS
Replies hidden
1

DEFAULT GLOBAL AUTOINCREMENTs work by partioning the key range, so each database (consolidated and each remote) will create PK values which do not overlap with those of other databases (if the "global_database_id" option is set accordingly).

You can define key ranges of different sizes for different PKs (say, a partition size of 1000 for one table with few inserts and of 10000000 for another table with many inserts) but each remote database must use the same partition size for the same PK...

If you have a small key size (say, an INT) and require to support really many remotes and expect some to insert many values, you can still use a small partition size - if one remote exhausts its key range, it can easily take another one with the help of a GlobalAutoincrement event.

E.g. we have a SQL Remote setup with all PKs as INT (so limited to positive values from 1 .. 2^31, roundabout 2 billions) and used DEFAULT GLOBAL AUTOINCREMENT(200000) for all according PKs so we could support about 10 000 different partitions/remotes. If one would insert almost 200000 rows in any of the tables, it would reserve another "global_database_id" to get a new key range and could then insert the next 200000 rows, and so on. That has worked really well over the years, and it has not bothered me that some tables would have few inserts and others many more, and that some users would make many entries and others only a few...

(01 Apr '16, 02:55) Volker Barth

Those are two different questions: (1) avoiding primary key collisions caused by inserts on different remote databases, and (2) controlling insert permissions across different remote databases. DEFAULT GLOBAL AUTOINCREMENT is an answer to question 1, but it does NOTHING to answer question 2.

Custom code is most likely required to answer question 2; there is nothing built in to SQL Anywhere or MobiLink or SQL Remote to make this easier.

Since key pools are, by definition, custom code that controls the ability of each remote to do inserts to individual tables, that seems to be a good place to code the answer to question 2... which you may have already done... as well as providing an answer to question 1... which key pools do.

So, I live and learn: another reason to use key pools. It's not the only reason, but it shares this characteristic with other good reasons to use key pools: The application needs tight central control over the assignment of primary key values across all remote databases.

FWIW one important characteristic of MobiLink is its support for custom code in upload and download scripts, especially with scripts that are coded as calls to custom stored procedures (which can do anything) rather than single basic SQL statements like SELECT, INSERT and so on.

(02 Apr '16, 08:04) Breck Carter

(2) controlling insert permissions across different remote databases

Hm, I have understood that requirement differently, namely that only some users will make inserts, and others will not. - If that is a question of permissions, I guess I would try to solve that with different user roles and different privileges, not with key pools.

(03 Apr '16, 06:52) Volker Barth
Comment Text Removed
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:

×298

question asked: 31 Mar '16, 12:36

question was seen: 349 times

last updated: 03 Apr '16, 09:42