By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,697 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

GENERATED ALWAYS AS IDENTITY clause

P: n/a
I have a table defined as follows:

CREATE TABLE "LOGINS" (
-- UID
"PK_NUMBER" INT GENERATED ALWAYS AS IDENTITY (START WITH 1),
-- Login Date
"DATE" DATE NOT NULL,
...

After doing some inserts with JDBC I have this sequence for PK_NUMBER:

1,2,21,22,23,24,41,42

As far as I understand the docs, the sequence is unique this table and
should be 1,2,3,4,5,...

DB2 OS/2

D:\DB2DATA\$SCRIPTS>db2level
DB21085I Exemplar "DB2" verwendet DB2-Codefreigabe "SQL07026" mit
Aktualitäts-ID "03070105" und den Information-Tokens "DB2 v7.1.0.72",
"n021110" und "WR21312".

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 12 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
Ian
Bernd Hohmann wrote:
I have a table defined as follows:

CREATE TABLE "LOGINS" (
-- UID
"PK_NUMBER" INT GENERATED ALWAYS AS IDENTITY (START WITH 1),
-- Login Date
"DATE" DATE NOT NULL,
...

After doing some inserts with JDBC I have this sequence for PK_NUMBER:

1,2,21,22,23,24,41,42

As far as I understand the docs, the sequence is unique this table and
should be 1,2,3,4,5,...


By default DB2 caches 20 values for the identity for performance
reasons. When the database is deactivated this cache is lost, thus
the break in sequence.

Nov 12 '05 #2

P: n/a
Ian wrote:
As far as I understand the docs, the sequence is unique this table and
should be 1,2,3,4,5,...


By default DB2 caches 20 values for the identity for performance
reasons. When the database is deactivated this cache is lost, thus
the break in sequence.


Any idea to avoid this caching? This table isn't time critical but the
sequence mission critical.

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 12 '05 #3

P: n/a
Bernd Hohmann wrote:
Ian wrote:
As far as I understand the docs, the sequence is unique this table
and should be 1,2,3,4,5,...

By default DB2 caches 20 values for the identity for performance
reasons. When the database is deactivated this cache is lost, thus
the break in sequence.

Any idea to avoid this caching? This table isn't time critical but the
sequence mission critical.

Use the NO CACHE option.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
AK
> sequence mission critical.

even with the NO CACHE, if you insert, then rollback - you've just got
a gap, correct? Is it acceptable in your situation?

Nov 12 '05 #5

P: n/a
just FYI:
If you use NO CACHE option the performance can degrade , as each
generation of new identity value will force writing to the log.

PS. What if smbdy delete some rows? rollback? crash recovery? If you
really want to prevent gaps and have it sequential write your own
function which will serialize generation (until you commit nobody else
can generate new value) -- it will be very slow, but you can get what
you want.

Nov 12 '05 #6

P: n/a
Artur wrote:
PS. What if smbdy delete some rows? rollback? crash recovery?
Never. Its just a logfile for userlogins and I need the sequence for a
rownumber emulation for web-browsing through the records.
If you really want to prevent gaps and have it sequential write your own
function which will serialize generation (until you commit nobody else
can generate new value) -- it will be very slow, but you can get what
you want.


Define "very slow". Usually I use a 20 digit timestamp (similar to
GENERATE_UNIQUE()) for this which is fast, accurate and collision free.
And for heavier tasks I have a great number generator which can handle
this task too.

But why wake the mice if the DB2 dinosaur is ready to go? I like to to
do the hard way <g>

Bernd

--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 12 '05 #7

P: n/a
Ian
Bernd Hohmann wrote:
Artur wrote:
PS. What if smbdy delete some rows? rollback? crash recovery?


Never. Its just a logfile for userlogins and I need the sequence for a
rownumber emulation for web-browsing through the records.


So why are you using an identity column to just get a row number?

select user_id, login_timestamp, ..., rownumber() over (order by
login_timestamp)
from table...
Nov 12 '05 #8

P: n/a
Ian wrote:
So why are you using an identity column to just get a row number?

select user_id, login_timestamp, ..., rownumber() over (order by
login_timestamp)


because I like to scroll through the table in blocks (10 or 20 rows) so
I need a sequence for positioning. Everything else like a WHERE-clause
with the last displayed row as begin marker needs more programming.

Bernd
--
"Ja, alles meine Herren" sprach Fürst Lichnowsky. "Ooch det roochen?"
"Ja, auch das Rauchen." "Ooch im Tiergarten?" "Ja, auch im Tiergarten
darf geraucht werden, meine Herren." Und so endeten die Barrikadenkämpfe
des 18. März in Berlin
Nov 12 '05 #9

P: n/a
Ian
Bernd Hohmann wrote:
Ian wrote:
So why are you using an identity column to just get a row number?

select user_id, login_timestamp, ..., rownumber() over (order by
login_timestamp)


because I like to scroll through the table in blocks (10 or 20 rows) so
I need a sequence for positioning. Everything else like a WHERE-clause
with the last displayed row as begin marker needs more programming.


I understand what you are trying to do. Sorry if I was terse. You app
would execute a statement like:

select * from
(select
...,
rownumber() over (order by login_timestamp) as rn
from ...
) as a
where rn between 10 and 30

Understand? This also gives you the ability to sort data by different
columns.
Nov 12 '05 #10

P: n/a
Ian wrote:
select * from
(select
...,
rownumber() over (order by login_timestamp) as rn
from ...
) as a
where rn between 10 and 30


Well, sounds good. But I have no idea what is the cost of rownumber()
and the subselect if the table grows.

Or better: does it cost more time to create the identity value on every
login (not very often, about 1-2 times per minute) or does it cost more
time to create a subselect on a table with 50.000 entries to fetch 10
rows at the end.

Bernd
Nov 12 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.