DB2 Generate GUID/UUID

Question

Is it possible to generate to GUID/UUID string in DB2 whose contents need to be absolutely unique in the universe? similar to uniqueidentifier data type in SQL server. In DB2 we got GENERATE_UNIQUE function, that ensures uniqueness only across multiple execution of the same function. I think i can try Java Routines in DB2. In worst possible case I can generate GUID/UUID from my Java app and send it across to DB2.. are there any other alternatives to achieve the same within DB2 without using external components/routines. Please suggest.

Thank you..

Answer 1

Try This...

   SELECT  LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),8) CONCAT '-' CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4) CONCAT '-' CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4) CONCAT '-' CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000 )), 'abcdef123456789', '1234567890' ),4) CONCAT '-' CONCAT LEFT(TRANSLATE ( CHAR(BIGINT(RAND() * 10000000000000 )), 'abcdef123456789', '1234567890' ),12)  FROM YOURTABLE

Answer 2

Late to the game, here is how to generate a UUID version 1 (time-based). Several caveats apply:

• There is always a possibility of generating duplicate UUIDs
• The RAND() function is not as random as we think
• The third quad part of the UUID could use a SEQUENCE object instead of RAND()
• The "B" part of the UUID is supposed to come from the MAC address of the system, it should be used as a unique instance ID instead of RAND().

-- vvvvvvvv-uuuu-1ttt-ssss-mmmmmmmmmmmm
-- 1                 4 bit version
-- tttuuuuvvvvvvvv   60 bit timestamp
-- ssss              2 bit variant and 14 bit sequence from 8000-BFFF
-- mmmmmmmmmmmm      48 bit mac address
WITH UUIDMAKE(A,B) AS (
SELECT 
RIGHT(HEX(864000000000*BIGINT(DAYS(CURRENT TIMESTAMP - CURRENT TIMEZONE)-577736)
+10000000*BIGINT(MIDNIGHT_SECONDS(CURRENT TIMESTAMP - CURRENT TIMEZONE))
+BIGINT(10*MICROSECOND(CURRENT TIMESTAMP - CURRENT TIMEZONE)+10*RAND())),15)
,
'02'||RIGHT(HEX(RAND()),10)
FROM SYSIBM.SYSDUMMY1
)
SELECT 
SUBSTR(A,8,8)
||'-' || SUBSTR(A,4,4)
||'-1'|| SUBSTR(A,1,3) 
||'-' || RIGHT(HEX(INT(32768+RAND()*16384)),4)
||'-' || B
FROM UUIDMAKE