DB2 - ALTER table ADD column with unique default value (UUID)

Question

I need to update an existing table by adding a new column with default value as UUID and datatype as VARCHAR(255).

I tried to achieved it by writing a function as:

CREATE FUNCTION UUID_FUNC()
     RETURNS VARCHAR(255)
     LANGUAGE SQL 
     BEGIN ATOMIC
     DECLARE UUID VARCHAR(4000);
     SET UUID = (SELECT TRIM(CHAR(HEX(GENERATE_UNIQUE()))) from sysibm.sysdummy1);
     RETURN UUID;
END

And used it in the query as:

ALTER TABLE <Table-name> 
    ADD ID_VALUE VARCHAR(255) NOT NULL DEFAULT (UUID_FUNC())

Got following error when executing the above query:

SQL Error [42601]: An unexpected token "DEFAULT" was found following "ARCHAR(255) NOT NULL".  
Expected tokens may include:  "CHECK".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.59.81

What is the correct format for calling custom defined functions in ALTER query or any suggestions to achieve the above requirement is appreciated.

Thanks in advance.

Answer 1

I was able to achieve it in following way:

ALTER TABLE <Table-name> ADD ID_VALUE VARCHAR(255) NOT NULL DEFAULT '0';

UPDATE <Table-name> SET ID_VALUE=(hex(GENERATE_UNIQUE())) WHERE ID_VALUE='0';

Answer 2

You would need to do this via a trigger, rather than as a generated expression. Given the DDL:

create or replace function your.uuid_func()
   returns char(26)
   language sql
   not deterministic
   return values(hex(generate_unique()));

create table your.table (
   c1 int not null,
   c2 char(26) not null
);

You can create the trigger:

create trigger set_uuid
   before insert on your.table
   referencing new as n
   for each row
   when (n.id_value is null)
      set n.id_value = your.uuid_func();

Then the insert:

—- You can insert a normal string:
insert into your.table (c1, c2)
   values (1, ‘anything’);


—- Or, if you don’t provide a value for c2, it will be set
—- to the value of UUID_FUNC():

insert into your.table (c1) 
    values (2);

Results:

select * from your.table;

C1          C2                        
----------- --------------------------
          1 anything                     
          2 20200111154913255440000000