Chinaunix首页 | 论坛 | 博客
  • 博客访问: 76122
  • 博文数量: 6
  • 博客积分: 205
  • 博客等级: 入伍新兵
  • 技术积分: 1304
  • 用 户 组: 普通用户
  • 注册时间: 2012-05-25 13:37
文章分类
文章存档

2012年(6)

我的朋友

分类: DB2/Informix

2012-05-29 14:09:25

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.
    9. 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,

    点击(此处)折叠或打开

    1. CREATE SEQUENCE seq1 AS INTEGER;
    2. VALUES NEXT VALUE FOR seq1;
    3. 1
    4. -----------
    5. 1
    6. 1 record(s) selected.

  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.

    点击(此处)折叠或打开

    1. VALUES GENERATE_UNIQUE();
    2. 1
    3. -----------------------------
    4. x'20101110052559155343000000'
    5. 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

    点击(此处)折叠或打开

    1. import java.util.UUID; // for UUID class
    2. public class UUIDUDF
    3. {
    4. public static String randomUUID()
    5. {
    6. return UUID.randomUUID().toString();
    7. }
    8. }

  3. compile the program from your shell

    点击(此处)折叠或打开

    1. javac UUIDUDF.java
  4. Produce a jar file

    点击(此处)折叠或打开

    1. jar cvf UUIDUDF.jar UUIDUDF.class
  5. Time to fire up DB2

    点击(此处)折叠或打开

    1. db2 -t
  6. Connect to the database

    点击(此处)折叠或打开

    1. connect to
  7. Register the jar file with the database

    点击(此处)折叠或打开

    1. 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:

    点击(此处)折叠或打开

    1. CREATE OR REPLACE FUNCTION RANDOMUUID()
    2. RETURNS VARCHAR(36)
    3. LANGUAGE JAVA
    4. PARAMETER STYLE JAVA
    5. NOT DETERMINISTIC NO EXTERNAL ACTION NO SQL
    6. EXTERNAL NAME 'UUIDUDFJAR:UUIDUDF.randomUUID' ;
  9. Test the function

    点击(此处)折叠或打开

    1. VALUES RANDOMUUID();
    2. 1
    3. ------------------------------------
    4. aa0058ed-bcca-4ead-a22f-b648ccb66787
    5. 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.

    点击(此处)折叠或打开

    1. VALUES VARCHAR_BIT_FORMAT(RANDOMUUID(), 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');
    2. 1
    3. -------------------------------------------------------------------------------------
    4. x'57E71F89F093428DB696A9FEB6785853'
    5. 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.

    点击(此处)折叠或打开

    1. CREATE OR REPLACE FUNCTION SYS_GUID()
    2. RETURNS CHAR(16) FOR BIT DATA
    3. CONTAINS SQL NOT DETERMINISTIC NO EXTERNAL ACTION
    4. RETURN VARCHAR_BIT_FORMAT(RANDOMUUID(), 'xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx');
    5. VALUES SYS_GUID();
    6. 1
    7. -----------------------------------
    8. x'A6F0F1FFF93B4CF8A9C4E990774D9B70'
    9. 1 record(s) selected.
  12.  Now let's tie this all up with an example usage.

    点击(此处)折叠或打开

    1. CREATE TABLE license(key CHAR(16) FOR BIT DATA NOT NULL PRIMARY KEY,
    2. customername VARCHAR(20),
    3. product VARCHAR(20));
    4. CREATE OR REPLACE TRIGGER license_bi BEFORE INSERT ON license
    5. REFERENCING NEW AS n FOR EACH ROW
    6. SET n.key = SYS_GUID();
    7. SELECT key FROM NEW TABLE(INSERT INTO license(customername, product) VALUES('Oracle', 'DB2'));
    8. KEY
    9. -----------------------------------
    10. x'A944FBAA5611459EBB9C7FCEC7F65FDE'
    11. 1 record(s) selected.
That was easy :-)
阅读(3450) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册