Identity Column suddenly become 500000000000000
Marco Bartoletti
My database is an Adaptive Server running on Windows NT 4.0
I've this problem:
my application, written with Power++ using ODBC, inserts normally lines
into many tables, getting the keys with
select @@identity
and creating relationships within tables
the keys had quite small values because the tables are still almost
empty
now, suddenly, the value of the keys has been increased to
500000000000000 (15 chars) and is growing from this point on
....
345
346
347
500000000000348
500000000000349
500000000000350 .....
anyone knows this problem?
bye & thanx
--------------------------
Marco Bartoletti
ma...@genesys-informatica.com
GeneSYS Informatica
Genova (Italy)
Paul Dugas
We've had the same problem for years. Our first Sybase installation was
10.? and we discovered the symptom you described. It seems to happen after
stop/starting the server. Really screws up Client-lib apps doing "select
convert(int, @@identity)" after an insert. I don't know about your machines
but 15 decimal digits won't fit in an int on mine. :)
I read something about the server adding X to identity counters in certain
situations. There's a field in some system table that determines the value
of X. It's a kludge for some problem in the server.
The only way we've been able to get around it is to bcp_out, drop, create,
and bcp_in. Pretty drastic. We've been eliminating identity columns from
our apps. A real pain in the ass in some situations.
Hope this helps.
-Paul
--
Paul Dugas
GTC Systems
Atlanta, GA
Marco Bartoletti wrote in message
<34DB21B7...@genesys-informatica.com>...
Todd Boss
In article <34DB21B7...@genesys-informatica.com>,
Marco Bartoletti <ma...@genesys-informatica.com> wrote:
>
>now, suddenly, the value of the keys has been increased to
>500000000000000 (15 chars) and is growing from this point on
>
>....
>345
>346
>347
>500000000000348
>500000000000349
>500000000000350 .....
What you're experiencing is a known behavior of identities in Sybase.
See Question 7.9 of the Sybase FAQ (http://reality.sgi.com/pablo/Sybase_FAQ)
for reasons this might have occurred and ways to recover.
boss
DBell Inc
This can happen when you bounce the SQL Server. The next identity number is
incremented (by some value managed by sp_configure...can't remember off the top
of my head). Looks like your value is set really high.
You really can't rely on identify columns for smoothly incrementing keys. They
can really jump up in a hurry with a number of bounces.
Anyway, you might want to look at the doc and see if this is your issue.
Bummer...
D Bell
Eric Berg
Is it generally accepted that identity columns don't work as advertised? Has
this been fixed in later versions of SQL Server/ASE?
-Eric.
Michael Peppler
Eric Berg wrote:
>
> Is it generally accepted that identity columns don't work as advertised? Has
> this been fixed in later versions of SQL Server/ASE?
It's a question of setting the burn factor correctly with sp_config.
The identity number also does not jump when you shut down the
server gracefully.
Michael
>
> -Eric.
>
> dbel...@aol.com (DBell Inc) writes:
>
> > This can happen when you bounce the SQL Server. The next identity number is
> > incremented (by some value managed by sp_configure...can't remember off the top
> > of my head). Looks like your value is set really high.
> >
> > You really can't rely on identify columns for smoothly incrementing keys. They
> > can really jump up in a hurry with a number of bounces.
> >
> > Anyway, you might want to look at the doc and see if this is your issue.
> >
> > Bummer...
> >
> > D Bell
--
Michael Peppler -||- Data Migrations Inc.
mpep...@datamig.com -||- http://www.mbay.net/~mpeppler
Todd Boss
In article <8km67mk...@sybase.com>, Eric Berg <eb...@sybase.com> wrote:
Not at all. Identity columns work very well at what they're advertised
to do; provide a unique number for each row in a table. If your requirement
is as such, identities work very well. If your requirement is unique
numbers that increase sequentially and never have gaps then identities
are not for you. There was a bug in Sybase11 EBF 6717 and below that
caused these large identity gaps during "polite" shutdowns (impolite
shutdowns, such as shutdown with nowait or outright crashes will always
cause gaps to occur because of the way Identities work). However this
bug was fixed in System11 EBF 6886 and shouldn't be a problem in ASE.
see http://reality.sgi.com/pablo/Sybase_FAQ/Q7.9.html for much more
information on the topic. Most of the basics are covered involving
identities, what to look out for, and how to mimic them using home grown
procedures.
boss
>Is it generally accepted that identity columns don't work as advertised? Has
>this been fixed in later versions of SQL Server/ASE?
>
Manish Shah
I think most of the mis-understanding has been caused by the fact that
Sybase's obsession with performance caused them to default the identity
burning factor default to be 5000. This amounts to only .05 %. However,
most of the time the IDENTITY field is designed with a large precision
to
avoid running out of the numbers. These two factors together cause
the jumps to be large (in abnormal shutdown).
If the identity burning factor were to be set to 1, this problem will
goes away. Of course, this is supposed to cause a little performance
hit.
But how many living applications will have 1000s of clients inserting in
split second versus how many shops in 1) development stage 2) operating
in
small transaction volume ?
This is plain stupid.
--
------------------------------------
Manish I. Shah
------------------------------------
Espen Ekeroth
Eric Berg wrote:
> Is it generally accepted that identity columns don't work as advertised? Has
> this been fixed in later versions of SQL Server/ASE?
>
> -Eric.
>
> dbel...@aol.com (DBell Inc) writes:
>
> > This can happen when you bounce the SQL Server. The next identity number is
> > incremented (by some value managed by sp_configure...can't remember off the top
> > of my head). Looks like your value is set really high.
> >
> > You really can't rely on identify columns for smoothly incrementing keys. They
> > can really jump up in a hurry with a number of bounces.
> >
> > Anyway, you might want to look at the doc and see if this is your issue.
> >
> > Bummer...
> >
> > D Bell
If something happens (such as a stop of the dataserver) It will increase the
identity number. This may be adjustet with the parameter "identity burning factor".
Se the manuals.
Espen
--
Espen Ekeroth, Hand-EL Skandinavia AS
mailto:esp...@hand-el.no mailto:esp...@hotmail.com
69215581 / 92499076