Generating universally unique identifiers (UUID)

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.
2. 
   

   点击(此处)折叠或打开

   1. CREATE TABLE emp(empid INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
   2. name VARCHAR(20),
   3. salary DECIMAL(10, 2));
   4. INSERT INTO emp VALUES(DEFAULT, 'Jones', 20000);
   5. VALUES IDENTITY_VAL_LOCAL();
   6. 1
   7. ---------------------------------
   8. 1.
   10. 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.
3. 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,
4. 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.

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
3. compile the program from your shell
4. Produce a jar file
5. Time to fire up DB2
6. Connect to the database
7. Register the jar file with the database 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:
9. Test the function 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. 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.
12.  Now let's tie this all up with an example usage.

That was easy :-)