unique identifier generation

Hello

unique identifier generation
One of our programmer submit me a solution to generate an unique identifier :
it is the sum of :

Hour(current ts) * 3600 000 000 + minute (c. ts) * 60 000000 + second (c.
ts) * 1 000000 + microsecond(current ts)

(he computes the Timestamp in microseconds)

Is there another sure and better way to generate an unique identifier with
DECIMAL (11)

We have a production problem today , and i don't have time to handle his
request , but i feel that there is something wrong with his way.

We are in V7, maybe identity could do the job ?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mark Vickers

Re: unique identifier generation
(in response to Hello)
Identity Columns are strongly discouraged in our shop currently on z/Os v7
for the following reasons:
(1) Our maintenance software, Change Manager cannot handle changes made to
tables with identity columns. For tables, where the identity column is
defined as GENERATED ALWAYS, Change Manager will not even generate a
worklist. For tables where the identity column is defined as GENERATED BY
DEFAULT, Change Manager could generate an incomplete worklist. If a change
forces the table to be dropped and redefined, the current maximum value must
be identified and added to the DDL in the START WITH parameter.
(2) Unloading rows from one table and loading to another, is complicated.
To load a value for an identity column, it must be defined as GENERATED BY
DEFAULT. This allows inserts to be done with a value, but the value is not
reflected in the SYSSEQUENCES table where the current maximum value is
stored. When SYSSEQUENCES is out of sync with the actual values, duplicate
values can be generated.
(3) Partial Recoveries do not update the SYSSEQUENCES table, causing a
non-recoverable gap in the generated values.
(4) "LOAD INTO PART x" is not allowed if an identity column is part of the
partitioning index.
Use MAX(sequence_no_column) + 1 as an alternative. This should perform well
in DB2 V7 with an ascending or descending index. Programs must be coded to
minimize the time between the SELECT MAX() and the INSERT, to prevent other
applications from inserting duplicate rows. In the event that an INSERT
does fail due to duplicate rows, the program must be able to handle it,
possibly by bumping the value by 1 and performing INSERT again. In some
cases, it might be necessary to COMMIT before the end of the normal UOW or
even to lock the table between the SELECT and INSERT to prevent duplicate
rows. LOCK SQL should be used sparingly. It reduces concurrency and poses
an imminent risk of causing other applications to fail because of
unavailable resources. If LOCK SQL is required, the program must be written
to assure that the locks are held for very short periods of time (e.g.
COMMIT immediately after INSERT).
Another option is to use a TIMESTAMP. Timestamps are usually granular
enough that you do not have to worry about duplicate rows.

DB2 V8 will introduce another good alternative, Sequence objects. A
sequence object is a separate structure that generates sequential numbers.
It is not assigned to a single column like the identity property. A
sequence object is created using CREATE SEQUENCE. When the SEQUENCE is
created, it can be used by applications to "grab" a next sequential value
for use in a table. Sequences are efficient and can be utilized by many
users at the same time without causing performance problems. Multiple users
can concurrently and efficiently access SEQUENCE objects because DB2 does
not wait for a transaction to COMMIT before allowing the sequence to be
incremented again by another transaction. Like identity columns, sequence
objects also have parameters to control the starting point for the generated
values and the number by which the count is incremented. Additionally, you
can specify how the SEQUENCE should handle running out of values when the
maximum value is hit.

-----Original Message-----
From: Daniel Cremieux [mailto:[login to unmask email]
Sent: Thursday, December 23, 2004 10:34 AM
To: [login to unmask email]
Subject: unique identifier generation

One of our programmer submit me a solution to generate an unique identifier
:
it is the sum of :

Hour(current ts) * 3600 000 000 + minute (c. ts) * 60 000000 + second (c.
ts) * 1 000000 + microsecond(current ts)

(he computes the Timestamp in microseconds)

Is there another sure and better way to generate an unique identifier with
DECIMAL (11)

We have a production problem today , and i don't have time to handle his
request , but i feel that there is something wrong with his way.

We are in V7, maybe identity could do the job ?

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: unique identifier generation
(in response to Mark Vickers)
This will not generate a unique number. Even using timestamp down to
that level is not guaranteed to be unique. You could use IDENTITY, but
make sure you specify NOCYCLE and constrain the column with a unique
index to ensure uniqueness. This has the disadvantage of being able to
generate a unique number but the identity has to be a column of a table
(unlike a V8 sequence)

Version 8 has the GENERATE_UNIQUE function, but that generates a
CHAR(13) FOR BIT DATA value not a number

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Daniel Cremieux
Sent: 23 December 2004 16:34
To: [login to unmask email]
Subject: unique identifier generation

One of our programmer submit me a solution to generate an unique
identifier :
it is the sum of :

Hour(current ts) * 3600 000 000 + minute (c. ts) * 60 000000 + second
(c.
ts) * 1 000000 + microsecond(current ts)

(he computes the Timestamp in microseconds)

Is there another sure and better way to generate an unique identifier
with DECIMAL (11)

We have a production problem today , and i don't have time to handle his
request , but i feel that there is something wrong with his way.

We are in V7, maybe identity could do the job ?

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Isaac Yassin

Re: unique identifier generation
(in response to Phil Grainger)
Hi,

GENERATE_UNIQUE() has been retrofeeted to V7 by an APAR (actually two).


Isaac Yassin



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Grainger, Phil
Sent: Thursday, December 23, 2004 10:33 PM
To: [login to unmask email]
Subject: Re: unique identifier generation

This will not generate a unique number. Even using timestamp down to that level is not guaranteed to be unique. You could use
IDENTITY, but make sure you specify NOCYCLE and constrain the column with a unique index to ensure uniqueness. This has the
disadvantage of being able to generate a unique number but the identity has to be a column of a table (unlike a V8 sequence)

Version 8 has the GENERATE_UNIQUE function, but that generates a
CHAR(13) FOR BIT DATA value not a number

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Daniel Cremieux
Sent: 23 December 2004 16:34
To: [login to unmask email]
Subject: unique identifier generation

One of our programmer submit me a solution to generate an unique identifier :
it is the sum of :

Hour(current ts) * 3600 000 000 + minute (c. ts) * 60 000000 + second (c.
ts) * 1 000000 + microsecond(current ts)

(he computes the Timestamp in microseconds)

Is there another sure and better way to generate an unique identifier with DECIMAL (11)

We have a production problem today , and i don't have time to handle his request , but i feel that there is something wrong with his
way.

We are in V7, maybe identity could do the job ?

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be
reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html.
From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be
reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm