Sequential Key Generation

[login to unmask email]

Sequential Key Generation
Hello everyone,

The question of whether it is necessary to have a separate table for
sequential key generation or can it just be generated from the main table
it will be used in, has come to my attention and I'm wanting to gather
pro's and con's. The following are thoughts that I have come up with. I'm
sure there are more.

If separate table has multiple rows then in both cases:
- a select of the max(key) with ur would need to be done to determine the
next value.
- a descending index would be needed for efficiency, however, this will be
extra overhead for the main table.
- retry logic in case of duplicate.
- possible holes in key generation.

If the separate table only holds one row with the max value then:
- straight select of key value from separate table.
- no extra descending index needed on main table.
- possible contention issues updating the key value in separate table.
- no holes in key generation.

So, to put it in table format, as I see it:

Table Pro Con
Separate
single value - no extra index on main table -
possible contention
- no holes - another table to maintain


multi value - no extra index on main table - another
table to maintain
- holes

Main - no extra table to maintain - overhead of extra
index
- holes


Anymore thoughts? What am I missing?

Thanks in advance,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 878-3525, Tie Line 8-427-3525
-----------------------------------------------------
Happiness is not around the corner.
Happiness is the corner.
- BMW
-----------------------------------------------------



Tim Lowe

Re: Sequential Key Generation
(in response to damcon2@US.IBM.COM)
Jay,
As I am sure that you have heard, IBM will be putting in a feature in DB2
version 6 called "identity columns" that will be able to automatically increment
to build sequential keys. I am looking forward to this to solve some of these
types of problems.
(in March of 2000?)

But, assuming DB2 version 5 on OS/390 today:

In the first process that you listed,
I am concerned that the "retry logic in case of duplicate" will cause the
transactions to be single-threaded. I do not think any "holes" will result
since the transactions will wait on the insert of the duplicate key and will
only receive a return code indicating a duplicate exists after the "other
transaction" does a commit. (or it could timeout waiting for the "other
transaction" to commit.)

Therefore, if I had to choose between the two options that you mentioned, then I
would have the single row table rather than an extra index on a larger table.

However, I would prefer to have a multi-row table that could assign "ranges" of
numbers to different "groups" of people. But, this would not create a real
sequential key, only a sequential key within a group.

I hope this helps.

Thanks,
Tim







[login to unmask email] on 12/10/99 11:44:01 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>








To: [login to unmask email]

cc: (bcc: Tim Lowe/sfm/spc)



Subject: Sequential Key Generation








Hello everyone,

The question of whether it is necessary to have a separate table for
sequential key generation or can it just be generated from the main table
it will be used in, has come to my attention and I'm wanting to gather
pro's and con's. The following are thoughts that I have come up with. I'm
sure there are more.

If separate table has multiple rows then in both cases:
- a select of the max(key) with ur would need to be done to determine the
next value.
- a descending index would be needed for efficiency, however, this will be
extra overhead for the main table.
- retry logic in case of duplicate.
- possible holes in key generation.

If the separate table only holds one row with the max value then:
- straight select of key value from separate table.
- no extra descending index needed on main table.
- possible contention issues updating the key value in separate table.
- no holes in key generation.

So, to put it in table format, as I see it:

Table Pro Con
Separate
single value - no extra index on main table -
possible contention
- no holes - another table to maintain


multi value - no extra index on main table - another
table to maintain
- holes

Main - no extra table to maintain - overhead of extra
index
- holes


Anymore thoughts? What am I missing?

Thanks in advance,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 878-3525, Tie Line 8-427-3525
-----------------------------------------------------
Happiness is not around the corner.
Happiness is the corner.
- BMW
-----------------------------------------------------








Robert Lawrence

Re: Sequential Key Generation
(in response to Tim Lowe)
Depending on how big the field is, will the value ever reach the max value
and wrap around?
If it wraps around how to keep from cycling through all of the possible
values(i.e. all values being used)?
These are problems that we ran into. We also used a 1 record VSAM file to
hold the next key value and updated that
HTH
Bob Lawrence
DBA
BOSCOV'S Dept Stores

-----Original Message-----
From: [login to unmask email] [SMTP:[login to unmask email]
Sent: Friday, December 10, 1999 12:44 PM
To: [login to unmask email]
Subject: Sequential Key Generation

Hello everyone,

The question of whether it is necessary to have a separate table for
sequential key generation or can it just be generated from the main table
it will be used in, has come to my attention and I'm wanting to gather
pro's and con's. The following are thoughts that I have come up with. I'm
sure there are more.

If separate table has multiple rows then in both cases:
- a select of the max(key) with ur would need to be done to determine the
next value.
- a descending index would be needed for efficiency, however, this will be
extra overhead for the main table.
- retry logic in case of duplicate.
- possible holes in key generation.

If the separate table only holds one row with the max value then:
- straight select of key value from separate table.
- no extra descending index needed on main table.
- possible contention issues updating the key value in separate table.
- no holes in key generation.

So, to put it in table format, as I see it:

Table Pro Con
Separate
single value - no extra index on main table -
possible contention
- no holes - another table to maintain


multi value - no extra index on main table - another
table to maintain
- holes

Main - no extra table to maintain - overhead of extra
index
- holes


Anymore thoughts? What am I missing?

Thanks in advance,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 878-3525, Tie Line 8-427-3525
-----------------------------------------------------
Happiness is not around the corner.
Happiness is the corner.
- BMW
-----------------------------------------------------








Jim Drewe

Re: Sequential Key Generation
(in response to Robert Lawrence)
Jay

As a matter of routine for OS/390 DB2 (OLTP), I attempt to avoid
generated numbers -- either on a single table or a separate table.
Perhaps there is another solution.

One consideration that you have to keep in mind if you use the number
generator is the select max(key) with ur. You avoid locking, but you may
increase the integrity exposure if you have a fairly active system. That
is, you may find duplicate generated numbers (assuming that you are
incrementing the max(key) by one) because your uncommitted read
may not truly be getting the max(key).

If you select with a share lock, then all processing is funneled through
this generated key process. This can be especially problematic on
an active system when the key generation is at the beginning of the
logical unit of work (which has been typical in my experience). You
are holding the lock for the duration of your unit of work. You are
providing nicely for the possibility that the task may abend and not
ruin the sequence of the number generation, but you are still single
threading. If the number generation is toward the end of the unit of
work and you have long and short running tasks, then the number
generation for the start time of the tasks is not accurate.

Instead of generated numbers, I use a timestamp. The timestamp
will cluster very well and performance is good (assuming you use
Type 2 indexes). The down side is that the ten bytes (internal) for
a timestamp will probably use more disk space than a generated
number. However, I am a DBA, not a storage administrator, and
I am less concerned about that.

Jim Drewe
Data Base Administrator

=========


Date: Fri, 10 Dec 1999 12:44:01 -0500
From: [login to unmask email]
Subject: Sequential Key Generation

Hello everyone,

The question of whether it is necessary to have a separate table for
sequential key generation or can it just be generated from the main table
it will be used in, has come to my attention and I'm wanting to gather
pro's and con's. The following are thoughts that I have come up with. I'm
sure there are more.

If separate table has multiple rows then in both cases:
- a select of the max(key) with ur would need to be done to determine the
next value.
- a descending index would be needed for efficiency, however, this will be
extra overhead for the main table.
- retry logic in case of duplicate.
- possible holes in key generation.

If the separate table only holds one row with the max value then:
- straight select of key value from separate table.
- no extra descending index needed on main table.
- possible contention issues updating the key value in separate table.
- no holes in key generation.

So, to put it in table format, as I see it:

Table Pro Con
Separate
single value - no extra index on main table -
possible contention
- no holes - another table to maintain


multi value - no extra index on main table - another
table to maintain
- holes

Main - no extra table to maintain - overhead of extra
index
- holes


Anymore thoughts? What am I missing?

Thanks in advance,
Jay


Jay Reavill
[login to unmask email]
IBM Global Services
Tampa, Florida
Tel: (813) 878-3525, Tie Line 8-427-3525



Mark McCormack

Sequential Key Generation
(in response to Jim Drewe)
To Tim Lowe,

Last Friday you posted a reply on this thread that included:

As I am sure that you have heard, IBM will be putting in a feature in DB2
version 6 called "identity columns" that will be able to
automatically increment
to build sequential keys. I am looking forward to this to solve
some of these
types of problems.
(in March of 2000?)

Can you elaborate on this, please? I have looked at the DB2 UDB for
OS/390 v6 manuals on the IBM web site, and I could find nothing about
this. Is this something which IBM will be adding to v6 that was not
included in the GA release in mid-1999?

If anyone else can provide information on this, please do so.

Mark McCormack
State Street Bank



Roger Miller

Re: Sequential Key Generation
(in response to Mark McCormack)
The What's New? for Version 6 was updated in November and indicates
the key content for V6 improvements after GA, as best we know it.

The identity column APAR PQ30652 is closed now, so the closing text
has a page or two describing the new function, even though the PTF
is not yet available.

Roger Miller