Generating universally unique identifiers (UUID)

Posted by: Serge Rielau

DB2 provides several means to generate unique identifiers depending on ones need.
For example:

1. IDENTITY column
   That's a column of a table which is generated automatically, typically in an ever increasing fashion.

   CREATE TABLE emp(empid  INTEGER        NOT NULL GENERATED ALWAYS AS IDENTITY, 
                    name   VARCHAR(20), 
                    salary DECIMAL(10, 2));
   INSERT INTO emp VALUES(DEFAULT, 'Jones', 20000);
   VALUES IDENTITY_VAL_LOCAL();
   1
   ---------------------------------
                                  1.
    
     1 record(s) selected.

   An identity column is typically unique within the table unless you allow it to cycle, you reset it or allow overriding of the generation by LOAD or INSERT.
2. SEQUENCE object
   A sequence is like an identity column, but without being attached to a table. Typically a sequence generates unique numbers within the database unless you allow it to cycle or reset it,

   CREATE SEQUENCE seq1 AS INTEGER;
   VALUES NEXT VALUE FOR seq1;
   1
   -----------
             1
    
     1 record(s) selected.

3. GENERATE_UNIQUE()
   A function that generates a unique CHAR(13) FOR BIT DATA (binary) string based on the current time and information about nodes in a DB2 cluster. The result is unique across the database as long as the system clock is not reset.

   VALUES GENERATE_UNIQUE();
   1
   -----------------------------
   x'20101110052559155343000000'
    
     1 record(s) selected.

None of these methods provide values which are unique across multiple DB2 databases or even across machines and geographies.

To produce universally unique identifiers (UUID) various well defined algorithms are available which use a combination of time or random number generation and machine unique information such as MAC addresses to produce binary strings that have a very, very low likelihood of colliding.

DB2 does not natively support  UUID, but Java does. So here I provide a sample implementation of UUID based on Java.

Let's get started:

1. Ensure javac the java compiler is on your path.
   It's normally on sqllib\java\jdk\bin
   
   
2. Create a java file named UUIDUDF.java

   import java.util.UUID;       // for UUID class
    
   public class UUIDUDF
   {
     public static String randomUUID()
     {
       return UUID.randomUUID().toString();
     }
   }

3. compile the program from your shell

   javac UUIDUDF.java

4. Produce a jar file

   jar cvf UUIDUDF.jar UUIDUDF.class

5. Time to fire up DB2

   db2 -t

6. Connect to the database

   connect to <dbname>

7. Register the jar file with the database

   call sqlj.install_jar('file:.\UUIDUDF.jar', 'UUIDUDFJAR')

   You may want to move the JAR file to a safe place and adjust the path above accordingly.
   in a multi-member environment, make sure the file is accessible from all members.
   
   
8. Create the function:

   CREATE OR REPLACE FUNCTION RANDOMUUID()
     RETURNS VARCHAR(36)
     LANGUAGE JAVA
     PARAMETER STYLE JAVA
     NOT DETERMINISTIC NO EXTERNAL ACTION NO SQL
     EXTERNAL NAME 'UUIDUDFJAR:UUIDUDF.randomUUID' ;

9. Test the function

   VALUES RANDOMUUID();
   1
   ------------------------------------
   aa0058ed-bcca-4ead-a22f-b648ccb66787
    
     1 record(s) selected.

   What you see is the generally accepted pretty-printed form of UUIDs.
10. db2 provides two functions VARCHAR_BIT_FORMAT() and VARCHAR_FORMAT_BIT() to convert the pretty printed version to a binary string and vice versa.

    VALUES VARCHAR_BIT_FORMAT(RANDOMUUID(), 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');
    1
    -------------------------------------------------------------------------------------
    x'57E71F89F093428DB696A9FEB6785853'
     

      1 record(s) selected.

    This is a 16 byte long VARCHAR FOR BIT DATA.
    
11. To make things nice and tight let's produce another function  that gets us the binary string right away as a CHAR(16) FOR BIT DATA. We pick SYS_GUID() as a name to please the Oracle crowd.

    CREATE OR REPLACE FUNCTION SYS_GUID()
      RETURNS CHAR(16) FOR BIT DATA
      CONTAINS SQL NOT DETERMINISTIC NO EXTERNAL ACTION
      RETURN VARCHAR_BIT_FORMAT(RANDOMUUID(), 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');
    
    VALUES SYS_GUID();
     
    1
    -----------------------------------
    x'A6F0F1FFF93B4CF8A9C4E990774D9B70'
     
      1 record(s) selected.

12. Now let's tie this all up with an example usage.

    CREATE TABLE license(key          CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY, 
                         customername VARCHAR(20), 
                         product      VARCHAR(20));
    CREATE OR REPLACE TRIGGER license_bi BEFORE INSERT ON license
      REFERENCING NEW AS n FOR EACH ROW
      SET n.key = SYS_GUID();
     
    SELECT key FROM NEW TABLE(INSERT INTO license(customername, product) VALUES('Oracle', 'DB2'));
    KEY
    -----------------------------------
    x'A944FBAA5611459EBB9C7FCEC7F65FDE'
     
      1 record(s) selected.

That was easy :-)
#Db2