on 2016 Mar 31 1:36 PM
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?
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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...
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.
(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.
User | Count |
---|---|
76 | |
30 | |
10 | |
8 | |
8 | |
7 | |
7 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.