SQL Reference

GENERATE_UNIQUE

>>-GENERATE_UNIQUE--(--)---------------------------------------><
 

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.

There are no arguments to this function (the empty parentheses must be specified).

The result of the function is a unique value that includes the internal form of the Universal Time, Coordinated (UTC) and the partition number 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. Each successive value will be greater than the previous value, providing a sequence that can be used within a table. The value includes the partition number where the function executed so that a table partitioned across multiple partitions also has unique values in some sequence. The sequence is based on the time 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 or an insert statement with a fullselect.

The timestamp value that is part of the result of this function can be determined using the TIMESTAMP scalar function with the result of GENERATE_UNIQUE as an argument.

Examples:

• 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 has "FOR BIT DATA" specified to identify the column as a bit data character string.

   CREATE TABLE EMP_UPDATE 
   (UNIQUE_ID CHAR(13) FOR BIT DATA,
    EMPNO CHAR(6),
    TEXT  VARCHAR(1000))
   
   INSERT INTO EMP_UPDATE 
             VALUES (GENERATE_UNIQUE(), '000020', 'Update entry...'),
                    (GENERATE_UNIQUE(), '000050', 'Update entry...') 
  

  This table will have a unique identifier for each row provided that the UNIQUE_ID column is always set using GENERATE_UNIQUE. This can be done by introducing a trigger on the table.

   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 defined, the previous INSERT statement could be issued without the first column as follows.

   INSERT INTO EMP_UPDATE (EMPNO, TEXT) 
              VALUES ('000020', 'Update entry 1...'),
                     ('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, there is no need to have a timestamp column in the table to record when a row is inserted.

⁴¹

The system clock is used to generate the internal Universal Time, Coordinated (UTC) timestamp along with the partition number on which the function executes. Adjustments that move the actual system clock backward could result in duplicate values.