RE: AW: A SQL UDF GENSYSUUID in DB2 SQL -- MIDRANGE-L

Vern,
I agree. I use the _MATTOD procedure to generate a 20 digit unique unsigned integer (only 8 bytes) instead of a timestamp data type which serves as a unique id for logging transactions common to all programs system wide.
I tried to create and have never seen a duplicate key since 2005 using this method.

Peter

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg
Sent: Tuesday, 2 July 2013 12:25 a.m.
To: Midrange Systems Technical Discussion
Subject: Re: AW: A SQL UDF GENSYSUUID in DB2 SQL

GENERATE_UNIQUE looks very interesting - its doc says it uses internal timestamp and system serial number - I suspect the MATTOD MI is used - this is guaranteed to be unique. GENERATE_UNIQUE is intended to replace the TIMESTAMP() function, which is NOT guaranteed to be unique, even down to the microsecond - our machines are just too fast. But MATTOD reserves some bits for uniqueness.

Vern

----- Original Message -----
Not sure if the MI function UUID is used, but if you are looking for a uniquely generated value, you may try the GENERATE_UNIQUE scalar function.
http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/index.jsp?topic=%2Fdb2%2Frb
afzscagenerateunique.htm

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-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Vengoal Chang
Gesendet: Monday, 01.7 2013 09:05
An: Midrange Systems Technical Discussion
Betreff: A SQL UDF GENSYSUUID in DB2 SQL

DB2 does not natively support UUID, but OS400 does. So here I provide a sample UDF implementation of UUID based on RPGLE.

For your reference:
http://code.midrange.com/8767d64b98.html

http://blog.xuite.net/vengoal/as400/79759985

--
Best Regards,

Vengoal Chang
--
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.

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

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

#####################################################################################

This correspondence is for the named person's use only. It may contain confidential
or legally privileged information, or both. No confidentiality or privilege is waived
or lost by any mistransmission. If you receive this correspondence in error, please
immediately delete it from your system and notify the sender. You must not disclose,
copy or rely on any part of this correspondence if you are not the intended recipient.
Any views expressed in this message are those of the individual sender, except where
the sender expressly, and with authority, states them to be the views of Veda.
If you need assistance, please contact Veda on either :-
Australia 1300-762-207 or New Zealand +64 9 367 6200