SQL Anywhere database - global autoincrement

Author

global autoincrement

Brent Williams

2005-04-25, 8:24 pm

I'm running ASA V9.0.2.3044.

I have a table with a primary key (ID) set as default global
autoincrement. The global database id is set to 1. When I try to
insert into the table without specifying an ID I get an error: "ID
cannot be NULL". Thanks

Reg Domaratzki $iAnywhere Solutions$

2005-04-25, 8:24 pm

Is it possible that you've used all the values in your available range? The
following code will overflow the default range of 65536 values on an integer
column and give the error "Column pkey in table t5 cannot be NULL".

begin
declare i integer;
create table t5 ( pkey integer default global autoincrement primary key );
set i = 0;
while i < 65537 loop
insert into t5 values (default);
set i = i + 1;
end loop;
end

--
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 "Platform Preview" and "Time Frame" to ALL

"Brent Williams" < brentwilliams@bcbsal
.org> wrote in message
news:426d5288$1@foru
ms-2-dub...
> I'm running ASA V9.0.2.3044.
>
> I have a table with a primary key (ID) set as default global
> autoincrement. The global database id is set to 1. When I try to
> insert into the table without specifying an ID I get an error: "ID
> cannot be NULL". Thanks

Brent Williams

2005-04-25, 8:24 pm

Reg Domaratzki (iAnywhere Solutions) wrote:
> Is it possible that you've used all the values in your available range? The
> following code will overflow the default range of 65536 values on an integer
> column and give the error "Column pkey in table t5 cannot be NULL".
>
> begin
> declare i integer;
> create table t5 ( pkey integer default global autoincrement primary key );
> set i = 0;
> while i < 65537 loop
> insert into t5 values (default);
> set i = i + 1;
> end loop;
> end
>
>
That was it. What is the best way to determine when you are close to
the max range? What about bumping the number up?

Thanks

Martin Baur

2005-04-25, 8:24 pm

> That was it. What is the best way to determine when you are close to
> the max range? What about bumping the number up?

Changing the datatype to bigint or unsigned bigint 2^64 ...

We used unsigned bigint for global autoincrmeent and we did partition the numbers to 2^32 .. so, 2^32 global ids each having 2^32 primary keys?

If you need more PKs, partition it to, say, 2^16 partitions with 2^48 each .... I assume this will be sufficient .. :-)

Martin

Brent Williams

2005-04-25, 8:24 pm

Martin Baur wrote:
>
>
> Changing the datatype to bigint or unsigned bigint 2^64 ...
>
> We used unsigned bigint for global autoincrmeent and we did partition the numbers to 2^32 .. so, 2^32 global ids each having 2^32 primary keys?
>
> If you need more PKs, partition it to, say, 2^16 partitions with 2^48 each .... I assume this will be sufficient .. :-)
>
> Martin

The datatype is already unsigned bigint so that is not a problem. We
have the column defined as: default global autoincrement (1000). We
have to do this because the ultralite clients will be adding records and
we don't want the range to be to high so we don't lose to many keys. We
will also have a batch program that inserts into the same table so I
guess we will need to check the usage during inserts. How do you check
usage of a range? Are there any tricks to make this task easier? We
could be doing thousands at a time so this could be a pain.

Thanks

Reg Domaratzki $iAnywhere Solutions$

2005-04-26, 9:23 am

There is a database system event called GlobalAutoIncrement that can be used
to warn you when you are getting near to the end of your range.

ASA Database Administration Guide
Automating Tasks Using Schedules and Events
Understanding system events
Choosing a system event

GlobalAutoIncrement
When the number of remaining values for a column defined with GLOBAL
AUTOINCREMENT is less than a percentage of its range, the
GlobalAutoIncrement event fires. This can be used to request a new value for
the GLOBAL_DATABASE_ID option based on the table and number of remaining
values that are supplied as parameters to this event. You can use the
event_condition function with RemainingValues as an argument for this event
type.

I really need to ask about the decision to use a default of global
autoincrement(1000) on a BIGINT. This allows you to define
18446744073709550 remote databases in your system. With 18 quintillion
possible values in a BIGINT, why the concern about "losing keys". By
default, a BIGINT will have 2^32 values allowed on 2^32 different remote
sites, so I'm confused why you felt you needed to change this default.

--
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 "Platform Preview" and "Time Frame" to ALL

"Brent Williams" < brentwilliams@bcbsal
.org> wrote in message
news:426d620e$1@foru
ms-2-dub...
> Reg Domaratzki (iAnywhere Solutions) wrote:
The[color=darkred]
integer[color=darkre
d]
key );[color=darkred]
> That was it. What is the best way to determine when you are close to
> the max range? What about bumping the number up?
>
> Thanks

Greg Fenton

2005-04-26, 9:23 am

Brent Williams wrote:
> That was it. What is the best way to determine when you are close to
> the max range? What about bumping the number up?
>

Hi Brent,

I looked up "global autoincrement - algorithm" in the index of the ASA
9.x online docs and found:

MobiLink Administration Guide
Synchronization Techniques
Maintaining unique primary keys
Maintaining unique primary keys using global autoincrement
- How default values are chosen

"The range of default values for a particular database is pn + 1 to p(n
+ 1), where p is the partition size and n is the value of the public
option GLOBAL_DATABASE_ID. For example, if the partition size is 1000
and GLOBAL_DATABASE_ID is set to 3, then the range is from 3001 to 4000."

If you are using Global Autoincrement, I *strongly* advise that you
specify a partition size on each column's default:

id UNSIGNED INT DEFAULT GLOBAL AUTOINCREMENT(10000)

[where 10000 is the partition size for the "id" column]. You should
choose a partition size that is appropriate for this column of your
application. For some columns, "10" is sufficient for the lifetime of
the application. For others, "100000" would only suffice for a few
months (or even days).

The other thing to note is that when a range becomes exhausted, you need
to change the GLOBAL_DATABASE_ID for that particular remote.

The above page talks about an EVENT that you can have in an ASA database
to notify you of range exhaustion.

For UltraLite, you can use the ULGlobalAutoincUsage
() function
periodically (e.g. just before a synchronization) to determine whether
you are nearing range exhaustion for any column. In the ASA 9.x online
docs see:

MobiLink Clients
UltraLite Clients
Maintaining primary key uniqueness
- Detecting the number of available default values

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Greg Fenton

2005-04-26, 9:23 am

Reg Domaratzki (iAnywhere Solutions) wrote:
>
> I really need to ask about the decision to use a default of global
> autoincrement(1000) on a BIGINT. This allows you to define
> 18446744073709550 remote databases in your system.

Depending on the requirements for the particular column, 1000 may be
enough for all of eternity. So I don't see a problem with setting a
partition range to that value *unless* frequent exhausting of the range
becomes a problem.

The real problem isn't with [UNSIGNED] BIGINT columns, but columns of
other data types [e.g. INT, SMALLINT, TINYINT]. The default partition
and range for these datatypes is too small for most applications, even
if the overall number of values is quite sufficient. So this is why I
strongly encourage all developers to specify a partition size for *all*
columns...even if the value you are specifying is the default size. At
the very least, the application developer has to *think* about what they
are telling the database that its partition size is [I'm very much
against defaults in almost every aspect of coding...think Y2K, think
16-bit/32-bit, etc...]

If using [UNSIGNED] BIGINT for every Global Autoinc column is a viable
approach, then the defaults are probably (probably!) alright. However,
BIGINT is usually overkill for many types of data (e.g. code tables).
Also it is often that a MobiLink solution has to tie into an existing
legacy schema that does not support BIGINT for various columns.

In any of these situations, your application/synchronization solution
will have to deal with range exhaustion. Once you have a mechanism in
place for handing out new GLOBAL_DATABASE_IDs,
then the only thing to
worry about w.r.t. partition size is making sure it is large enough that
you aren't forcing users to synchronize too frequently [to get a new ID
after exhaustion].

Just my random thoughts :-)
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Brent Williams

2005-04-26, 1:23 pm

Reg Domaratzki (iAnywhere Solutions) wrote:
> There is a database system event called GlobalAutoIncrement that can be used
> to warn you when you are getting near to the end of your range.
>
> ASA Database Administration Guide
> Automating Tasks Using Schedules and Events
> Understanding system events
> Choosing a system event
>
> GlobalAutoIncrement
> When the number of remaining values for a column defined with GLOBAL
> AUTOINCREMENT is less than a percentage of its range, the
> GlobalAutoIncrement event fires. This can be used to request a new value for
> the GLOBAL_DATABASE_ID option based on the table and number of remaining
> values that are supplied as parameters to this event. You can use the
> event_condition function with RemainingValues as an argument for this event
> type.
>
> I really need to ask about the decision to use a default of global
> autoincrement(1000) on a BIGINT. This allows you to define
> 18446744073709550 remote databases in your system. With 18 quintillion
> possible values in a BIGINT, why the concern about "losing keys". By
> default, a BIGINT will have 2^32 values allowed on 2^32 different remote
> sites, so I'm confused why you felt you needed to change this default.
>
>
>
Reg, thanks for the info. This is exactly what I needed.

To answer your question, we (Greg Fenton and a few others) did some math
and that is what we came up with. I don't have all the numbers in
front of me but here is the scenerio. We have thousands of physicians
with a palm. Each physician is writing 10 to 30 prescriptions per day.
We also have a batch process that adds filled prescriptions to the
same table as the claim is processed.

David Kerber

2005-04-26, 1:23 pm

In article <426e726e$1@forums-2-dub>, brentwilliams@bcbsal
.org says...
> Reg Domaratzki (iAnywhere Solutions) wrote:
> Reg, thanks for the info. This is exactly what I needed.
>
> To answer your question, we (Greg Fenton and a few others) did some math
> and that is what we came up with. I don't have all the numbers in
> front of me but here is the scenerio. We have thousands of physicians
> with a palm. Each physician is writing 10 to 30 prescriptions per day.
> We also have a batch process that adds filled prescriptions to the
> same table as the claim is processed.

With an UNSIGNED BIGINT, that would allow you about 4 billion physicians
each to write 4 billion prescriptions, which would probably be enough
<Grin>. You could partition that in other ways, to allow more
physicians each to write fewer scripts, or vice-verse. Any of these
alternatives should be sufficient for your needs.

--
Remove the ns_ from if replying by e-mail (but keep posts in the
newsgroups if possible).

Reg Domaratzki $iAnywhere Solutions$

2005-04-26, 1:23 pm

<opinion>
I guess we're into a philosophical debate now. IMHO, you can use BIGINT and
default autoincrement to ensure that you should never need to worry about
re-defining the global database ID on the remotes. You say you have
thousands of physicians writing upwards of 10-30 prescriptions per day.
Let's say that we're talking about 4 billion physicians (note that the
current estimated world population is about 6.5 billion) and they are each
writing 100,000 prescriptions per day. At this rate, using the default
ranges on a BIGINT column for global autoincrement, each of these 4 billion
physicians could use the application for 117 years without having to worry
about resetting the global database ID. If you're going to incur the cost
of using and storing a 64-bit integer as your primary key, you might as well
make sure you never need to reset the global database id.
</opinion>

--
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 "Platform Preview" and "Time Frame" to ALL

"Brent Williams" < brentwilliams@bcbsal
.org> wrote in message
news:426e726e$1@foru
ms-2-dub...
> Reg Domaratzki (iAnywhere Solutions) wrote:
used[color=darkred]
for[color=darkred]
event[color=darkred]

> Reg, thanks for the info. This is exactly what I needed.
>
> To answer your question, we (Greg Fenton and a few others) did some math
> and that is what we came up with. I don't have all the numbers in
> front of me but here is the scenerio. We have thousands of physicians
> with a palm. Each physician is writing 10 to 30 prescriptions per day.
> We also have a batch process that adds filled prescriptions to the
> same table as the claim is processed.

Sponsored Links