Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

global autoincrement function

5 posts in General Discussion Last posting was on 2011-12-22 18:56:45.0Z
TomL Posted on 2011-12-21 17:33:49.0Z
Sender: 2103.4ef21518.1804289383@sybase.com
From: TomL
Newsgroups: sybase.public.ase.general
Subject: global autoincrement function
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ef2187d.21d0.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Dec 2011 09:33:49 -0800
X-Trace: forums-1-dub 1324488829 172.20.134.41 (21 Dec 2011 09:33:49 -0800)
X-Original-Trace: 21 Dec 2011 09:33:49 -0800, 172.20.134.41
Lines: 8
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30785
Article PK: 73676

I am working on a mobile application using SQL Anywhere and
ASE 15 (central db). There is a Sybase white paper titled
Primary Keys in a Distributed Database Environment
discussing unique keys across all databases using global
autoincrement function. Page 4 discusses preparing the
central database but the SQL provided does not work on ASE
15. Can anyone confirm this global autoincrement solution
does or does not work for ASE 15?


"Mark A. Parsons" <iron_horse Posted on 2011-12-21 19:06:02.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: global autoincrement function
References: <4ef2187d.21d0.1681692777@sybase.com>
In-Reply-To: <4ef2187d.21d0.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ef22e1a@forums-1-dub>
Date: 21 Dec 2011 11:06:02 -0800
X-Trace: forums-1-dub 1324494362 10.22.241.152 (21 Dec 2011 11:06:02 -0800)
X-Original-Trace: 21 Dec 2011 11:06:02 -0800, vip152.sybase.com
Lines: 11
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30786
Article PK: 73677

You might want to provide a link to said document and/or a cut-n-paste of the code in question.

On 12/21/2011 12:33, TomL wrote:
> I am working on a mobile application using SQL Anywhere and
> ASE 15 (central db). There is a Sybase white paper titled
> Primary Keys in a Distributed Database Environment
> discussing unique keys across all databases using global
> autoincrement function. Page 4 discusses preparing the
> central database but the SQL provided does not work on ASE
> 15. Can anyone confirm this global autoincrement solution
> does or does not work for ASE 15?


TomL Posted on 2011-12-21 19:31:47.0Z
Sender: 2103.4ef21518.1804289383@sybase.com
From: TomL
Newsgroups: sybase.public.ase.general
Subject: Re: global autoincrement function
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ef23423.2770.1681692777@sybase.com>
References: <4ef22e1a@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 21 Dec 2011 11:31:47 -0800
X-Trace: forums-1-dub 1324495907 172.20.134.41 (21 Dec 2011 11:31:47 -0800)
X-Original-Trace: 21 Dec 2011 11:31:47 -0800, 172.20.134.41
Lines: 16
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30787
Article PK: 73678

thanks for the suggestion.
http://www.sybase.com/detail?id=1055953

> You might want to provide a link to said document and/or a
> cut-n-paste of the code in question.
>
> On 12/21/2011 12:33, TomL wrote:
> > I am working on a mobile application using SQL Anywhere
> > and ASE 15 (central db). There is a Sybase white paper
> > titled Primary Keys in a Distributed Database
> > Environment discussing unique keys across all databases
> > using global autoincrement function. Page 4 discusses
> > preparing the central database but the SQL provided does
> > not work on ASE 15. Can anyone confirm this global
> > autoincrement solution does or does not work for ASE 15?


"Mark A. Parsons" <iron_horse Posted on 2011-12-21 20:44:48.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: global autoincrement function
References: <4ef22e1a@forums-1-dub> <4ef23423.2770.1681692777@sybase.com>
In-Reply-To: <4ef23423.2770.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ef24540@forums-1-dub>
Date: 21 Dec 2011 12:44:48 -0800
X-Trace: forums-1-dub 1324500288 10.22.241.152 (21 Dec 2011 12:44:48 -0800)
X-Original-Trace: 21 Dec 2011 12:44:48 -0800, vip152.sybase.com
Lines: 43
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30789
Article PK: 73680

The author of the document must have been smokin' something ...

Unless it's a very well kept secret (and the ASE folks aren't known for their documentation skills) ASE doesn't support
a global autoincrement functionality.

You can certainly simulate this functionality but it'll require some upfront design on your behalf and some ongoing
monitoring to insure the functionality is maintained.

Some ideas:

- use a master key table to keep track of the 'next id'; assign a different range of numbers to each database based on
some sort of database name/id/some-other-unique-attribute; there are a handful of ways to implement a master key table
to address concurrency and scalability issues

- use identity columns as your PK, then manually set the next identity value in each database so that follow-on numbers
are part of a unique range assigned to that specific database; you'll also want to redefine the identity gap so that a
single database's identity values don't expand so far as to overrun another database's assigned identity value ranges;
you'll also need to monitor this situation to make sure someone doesn't accidentally reset/redefine the identity values
outside of their allotted range

- design the PK to be a combination of database/site id + a sequential key (eg, identity); this could mean a 2-column
PK, or a PK made up of the concatenated values

- think about using the newid() function to generate a random, universally unique UUID/GUID

On 12/21/2011 14:31, TomL wrote:
> thanks for the suggestion.
> http://www.sybase.com/detail?id=1055953
>
>
>> You might want to provide a link to said document and/or a
>> cut-n-paste of the code in question.
>>
>> On 12/21/2011 12:33, TomL wrote:
>>> I am working on a mobile application using SQL Anywhere
>>> and ASE 15 (central db). There is a Sybase white paper
>>> titled Primary Keys in a Distributed Database
>>> Environment discussing unique keys across all databases
>>> using global autoincrement function. Page 4 discusses
>>> preparing the central database but the SQL provided does
>>> not work on ASE 15. Can anyone confirm this global
>>> autoincrement solution does or does not work for ASE 15?