GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY scalar functions

The GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY functions return a value that is unique, compared to any other execution of the same function.

The schema is SYSIBM.

Start of change The GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY functions are defined as not deterministic. Although the functions have no arguments, the empty parentheses must be specified when the functions are invoked. End of change

Start of change The result of either of the functions 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. End of change

Start of change The data type of the result depends on which function was invoked. GENERATE_UNIQUE returns a bit data character string 13 bytes long (CHAR(13) FOR BIT DATA). GENERATE_UNIQUE_BINARY returns a BINARY(16) value. End of change

The result cannot be null.

Start of change The results of these functions can be used to provide unique values in a table. The sequence is based on the time when the function was executed. End of change

Start of change These functions differ 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. End of change

Start of change The timestamp value that is part of the result of either of these functions can be determined using the TIMESTAMP function with the result of GENERATE_UNIQUE or GENERATE_UNIQUE_BINARY as an argument. End of change

Example: Create a table that includes a column that is unique for each row. Populate this column using the GENERATE_UNIQUE function. Notice that the UNIQUE_ID column is defined as FOR BIT DATA to identify the column as a bit data character string.

   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();

With this trigger, the previous INSERT statements that were used to populate the table could be issued without specifying a value for the UNIQUE_ID column:

   INSERT INTO EMP_UPDATE (EMPNO,TEXT) VALUES ('000020','Update entry 1...');
   INSERT INTO EMP_UPDATE (EMPNO,TEXT) VALUES ('000050','Update entry 2...');

The timestamp (in UTC) for when a row was added to EMP_UPDATE can be returned using:

   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.