I have three theories:
1. The CURRENT TIMESTAMP is fixed for the duration of a statement
execution.
That is if you oinsert two rows in one statement INSERT INTO T VALUES
(<row1>), (<row2>), both will get the same value
2. DPF clock skew/concurrency
3. Most likely: granularity of the system clock. DB2 gets time from the OS.
Just because you get values down to teh micro-second doesn't mean that the
time between ticks is only one micro-second.

A better way is: generate_unique() in DPF (VARCHAR(13) FOR BIT DATA
or TIMESTAMP(generate_unique()) in non DPF.
generate_unqiue() remembers the last value generated and adds ticks to
assert strict monotonicity.
For DPF it also adds the node number.

In your case add:
CREATE TRIGGER ... BEFORE INSERT ON ... REFERENCING NEW AS N FOR EACH ROW
SET N.pk = TIMESTAMP(GENERATE_UNIQUE());

Cheers
Serge


"hsteiner" <db2udbdba-ezmlmshield-x12985749.[Email address protected] wrote
on 01/25/2006 01:51:50 PM:

> Dear Friends:
>
> I have always been under the impression that if you had a table with
> a column that was TIMESTAMP NOT NULL WITH DEFAULT - and you never
> provided a value for that column but rather took the default for all
> inserts, that you would be guaranteed uniqueness. I was told that
> such a column would be a perfect Primary Key if there were no
> "natural" primary key - because it was guaranteed unique. I am
> fairly sure I remember an instructor saying, only partly as a joke,
> that DB2 could *NEVER* insert two rows in the same millionth of a second.
>
> Now I am looking at a situation involving a 280,000,000 row
> production table with such a column. There are 3 situations on one
> day - within a 5 minute period - where I have 2 duplicates down to
> the microsecond, though the rest of the data is totally different -
> just the timestamps are the same.
>
> Does anyone have any theories how this could happen? I realize, of
> course, I could create a unique index to enforce uniqueness, but I
> am surprised this is necessary.
>
> Best, Hal
>
> PS: No replication is involved, no Data Partitioning either. This is
> a single instance - DB2 V8.2.3
>
>
>
> ---------------------------------------------------------------------
> PLEASE CLICK REPLY-ALL TO SEND A REPLY TO EVERYONE
> website: http://www.LazyDBA.com
> To unsubscribe: http://www.lazydba.com/unsubscribe.html
>


DB2 & UDB email list listserv db2-l LazyDBA home page