DB2中产生唯一值三种方式

一.Genearate_unique函数

[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_gu(custno char(13) for bit data,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$  db2 "insert into xifenfei.t_gu values
> (generate_unique(),'www.xifenfei.com'),(generate_unique(),'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_gu"

CUSTNO                        CUSTNAME        
----------------------------- ----------------
x'20120403054630527862000000' www.xifenfei.com
x'20120403054630527940000000' xifenfei        

  2 record(s) selected.

generate_unique是按照国际标准时间(UTC)生成的当前时间戳加上当前数据库分区号，包含13个字节的字符串。如果调整了系统时间可能会出现重复

二.序列(Sequence)

[db2inst1@xifenfei ~]$ db2 "create sequence xifenfei.xff_seq
> as bigint
> start with 1
> increment by 1
> no maxvalue
> cycle
> cache 10"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_seq(xff_id bigint,
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_seq values(nextval 
> for xifenfei.xff_seq,'www.xifenfei.com'), (nextval for xifenfei.xff_seq,'xifenfei')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_seq"

XFF_ID               CUSTNAME        
-------------------- ----------------
                   1 www.xifenfei.com
                   2 xifenfei        

  2 record(s) selected.

和ORACLE的sequence基本相同，只是在oracle中是sequence.nextval这里改为了nextnvl for seqence

三.自增字段

--1.generated always as identity方式(不能人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_a(xff_id bigint not null generated always 
> as identity(start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入失败
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_a(xff_id,custname) values(11,'XIFENFEI')"
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0798N  A value cannot be specified for column "XFF_ID" which is defined as 
GENERATED ALWAYS.  SQLSTATE=428C9
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_a"

XFF_ID               CUSTNAME        
-------------------- ----------------
                  10 www.xifenfei.com

  1 record(s) selected.

--1.generated by default as identity方式(可以人工干预插入数值)
[db2inst1@xifenfei ~]$ db2 "create table xifenfei.t_ind_d(xff_id bigint not null generated by default 
> as identity (start with 10,increment by 1,minvalue 10,maxvalue 1000000,no cycle,nocache,no order),
> custname varchar(16))"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(custname) values('www.xifenfei.com')"
DB20000I  The SQL command completed successfully.
--指定值插入成功
[db2inst1@xifenfei ~]$ db2 "insert into xifenfei.t_ind_d(xff_id,custname) values(11,'XIFENFEI')"
DB20000I  The SQL command completed successfully.
[db2inst1@xifenfei ~]$ db2 "select * from xifenfei.t_ind_d"

XFF_ID               CUSTNAME        
-------------------- ----------------
                  10 www.xifenfei.com
                  11 XIFENFEI        

  2 record(s) selected.

这个功能和sql server/mysql的自增长列很相似，给出了两种方式选择，使得比它们更加灵活