GENERATE_UNIQUE
The GENERATE_UNIQUE function returns a bit data character string that is unique, compared to any other execution of the same function.
The schema is SYSIBM.
The GENERATE_UNIQUE function returns a bit data character string 13 bytes long (CHAR(13) FOR BIT DATA) that is unique compared to any other execution of the same function. The function is defined as not deterministic. Although the function has no arguments, the empty parentheses must be specified when the function is invoked.
The result of the function is a unique value that includes the internal form of the Universal Time, Coordinated (UTC) and, if in a sysplex environment, the sysplex member where the function was processed.
The result cannot be null.
The result of this function can be used to provide unique values in a table. The sequence is based on the time when the function was executed.
This function differs from using the special register CURRENT TIMESTAMP in that a unique value is generated for each row of a multiple row insert statement, an insert statement with a fullselect, or an insert operation in a MERGE statement.
The timestamp value that is part of the result of this function can be determined using the TIMESTAMP function with the result of GENERATE_UNIQUE as an argument.
CREATE TABLE EMP_UPDATE
(UNIQUE_ID VARCHAR(13)FOR BIT DATA,
EMPNO CHAR(6),
TEXT VARCHAR(1000));
INSERT INTO EMP_UPDATE VALUES (GENERATE_UNIQUE(),'000020','Update entry 1...');
INSERT INTO EMP_UPDATE VALUES (GENERATE_UNIQUE(),'000050','Update entry 2...');
This
table will have a unique identifier for each row if GENERATE_UNIQUE is always used to set the value
the UNIQUE_ID column. You can create an insert trigger on the table to ensure that GENERATE_UNIQUE
is used to set the value:
CREATE TRIGGER EMP_UPDATE_UNIQUE
NO CASCADE BEFORE INSERT ON EMP_UPDATE
REFERENCING NEW AS NEW_UPD
FOR EACH ROW MODE DB2SQL
SET NEW_UPD.UNIQUE_ID = GENERATE_UNIQUE();
INSERT INTO EMP_UPDATE (EMPNO,TEXT) VALUES ('000020','Update entry 1...');
INSERT INTO EMP_UPDATE (EMPNO,TEXT) VALUES ('000050','Update entry 2...');
SELECT TIMESTAMP(UNIQUE_ID), EMPNO, TEXT FROM EMP_UPDATE;
Therefore,
the table does not need a timestamp column to record when a row is inserted.