Equivalent of NEWGUID in DB2

by | Mar 6, 2008

If you have been using UNIQUE_IDENTIFIER as a data type and use newguid as a default in your SQL Server table definition, you can use a UDF called from a trigger to simulate same behavior in DB2. This work around is due to the fact that DB2 does not yet support EXPRESSIONS in default clause in table definition.

In Microsoft world, a GUID is nothing but a unique identifier which looks like if presented in hex form e.g.
3F2504E0-4F89-11D3-9A0C-0305E82C3301. What we need is a unique identifier that is guaranteed for each row and the UDF given below will generate a unique data for each row. 

CREATE FUNCTION newguid()
RETURNS CHAR(32)
NOT DETERMINSTIC
RETURN hex(generate_unique()) ||
 hex(CHR(CAST(RAND()*255 AS SMALLINT))) ||
 hex(CHR(CAST(RAND()*255 AS SMALLINT))) ||
 hex(CHR(CAST(RAND()*255 AS SMALLINT)))
;

After above UDF is created, you can create a trigger in DB2 to simulate NEWGUID.

For DB2 on LUW (Linux, Unix and Windows)

CREATE TRIGGER TRIG_TAB1 NO CASCADE BEFORE INSERT
ON SCH1.TAB1 REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
  SET col1 = DB2.NEWGUID();

For DB2 on Z/OS

CREATE TRIGGER TRIG_TAB1 NO CASCADE BEFORE INSERT
ON SCH1.TAB1 REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
  VALUES DB2.NEWGUID() INTO col1
;