the SQL scalar GENERATE_UUID() ensures uniqueness of values
There is no SQL scalar function named GENERATE_UUID().
The GENERATE_UNIQUE() scalar function generates a unique value and
guarantees uniqueness.
It does not only include the UTC (Universal Time Coordinated / Greenwich
Time) but also the serial number of your machine.
Downside with using the GENERATE_UNIQUE function is, the information is
returned as 13 Byte bit character string (= CHAR(13) FOR BIT DATA).
The UTC itself enclosed within the GENERATE_UNIQUE value may not be unique.
The UTC included in a value generated with GENERATE_UNIQUE() can be
determined by using the SQL Scalar Function TIMESTAMP.
To get the timestamp in your time zone you may add the CURRENT_TIMEZONE
special register to the determined UTC.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
CRPence
Gesendet: Friday, 17.4 2015 00:56
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: TimeStamp Problem
On 16-Apr-2015 16:56 -0500, broehmer wrote:
On 16-Apr-2015 16:48 -0500, Alan Campin wrote:
On 16-Apr-2015 16:16 -0500, broehmer wrote:
All right. I'm going to ask one of those "stupid" questions.
Why does the time stamp only use the first 3 positions of the
millisecond? We just tried to find unique records for a file based
on time stamp and got several results returned all with the last 3
positions 0. In fact, all our files are like that.
Is this by design? Are we doing something wrong when we write them
out?
Depends what release you are. The more current releases have more
precision. Why are you using a timestamp for a unique value. Why not
use an identity key?
We're on 6.1 hopefully moving to 7.1 before end of summer.
To answer your question....it's an old file that is more or less for
historical purposes. Not a real problem yet but we were trying to
solve a "last record" for a group problem and ran into duplicates
using SQL.
Not a show stopper but close.
If the values of a column of the file storing the values [as TIMESTAMP or
otherwise] were supposed to be maintained as unique, then probably there
should be a PRIMARY KEY or UNIQUE constraint [or Unique Keyed Access Path of
the non-SQL variety] to enforce that requirement.
Any recipient of a duplicate key exception need only drop into a
retry-until-success loop [with an optional escape for a "something must be
seriously wrong" condition and\or delays to prevent a /hard-loop/].?
Just to reiterate, the topic origin was apparently /RPG timestamp/
values, but a clarification was made by someone that the DB2 for i TIMESTAMP
data type defaults to a larger precision; yet those too can exhibit
duplicate values, so even with more precision the constraint is a general
requirement to ensure uniqueness, especially per newer\faster hardware being
more likely to generate duplicates with [or even without] concurrency.
While the SQL Special Register CURRENT_TIMESTAMP does not ensure uniqueness
of values, the TIMESTAMP value from the SQL scalar
GENERATE_UUID() ensures uniqueness of values; I do not know if the newer ROW
CHANGE TIMESTAMP feature might, or not.
--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
