GENERATED ALWAYS AS IDENTITY clause

[Bernd Hohmann]

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

[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.

[Bernd Hohmann]

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

[Serge Rielau]

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

[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?

[Artur]

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.

[Bernd Hohmann]

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

[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...

[Bernd Hohmann]

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

[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.

[Bernd Hohmann]

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