DB2中的标识列（IDENTITY）设计

参考－如何用代理键实现 DB2 UDB 主键

http://www.ibm.com/developerworks/cn/data/library/techarticles/dm-0407zhang/index.html

标识列(IDENTITY COLUMN)是 DB2 数据库提供的一种可自动为表中的每一行生成唯一数值的方法。比如用来表示一些定单号、流水号、文件编码等信息。一个表中只能有一个列被定义为标识列。

在DB2中提供了两种标识列值

√GENERATED ALWAYS AS IDENTITY --始终生成

√GENERATED BY DEFAULT AS IDENTITY --缺省情况下生成

说明：

1）定义为始终生成的标识列不允许在 SQL 语句中覆盖值。它的值总是由 DB22 数据库管理器生成；不允许应用程序提供
显式的值。但是DB2不保证"始终生成"列中的值是唯一的。要保证该列中的值是唯一的，应该对该列定义唯一索引。

2）定义为在缺省情况下生成的标识列向应用程序提供了显式地为标识列提供一个值的方法。若未给出任何值，则 DB2 将
生成一个值，但在此情况下，也不能保证该值的唯一性。“在缺省情况下生成”用于数据传播（复制现有表的内容），或用
于一个表的卸装和重新装入。

3）在DB2的内部实际上系统自动为该标识列创建了一个SEQTYPE='I'的序列

标识列特别适合用于生成唯一主键值。应用程序可使用标识列来避免当一个应用程序在数据库外部生成它自己的唯一计数
器时可能会导致的并发性和性能问题。例如，一种常见的应用程序级实施是维护一个只有一行的表，它包含一个计数器。
每个事务都锁定此表，增大该数字，然后落实；即，每次只有一个事务可以增大计数器。相反，若通过标识列维护该计数
器，因为事务不锁定该计数器，所以可以获得较高级别的并发性。一个未落实的已增大该计数器的事务不会阻止后续事务
也增大该计数器。

标识列的计数器的增大（或减小）独立于事务。若给定的事务两次增大一个标识计数器，则该事务可能会在生成的两个数
字之间看到一个间隔，因为可能有其他事务正在增大同一标识计数器（即，将行插入同一个表中）。若一个应用程序必须
要有连续范围的数字，则该应用程序应对带有标识列的表进行互斥锁定。因为会造成丢失并发性，所以必须对此决定作权
衡。此外，有可能因为生成标识列的值的事务已回滚，或因为高速缓存值序列的数据库在指定所有高速缓存的值之前被取
消激活，从而导致给定的标识列出现数字之间生成间隔的情况。

标识列生成的顺序数字具有下列附加属性：

■值可以是任何小数位为零的精确数字数据类型；即，小数位为零的 SMALLINT、INTEGER、BIGINT 或 DECIMAL。（单精度和双精度浮点类型被认为是近似数字数据类型。）

■连续值之间可以有任何指定的整数增量。缺省增量是 1。

■标识列的计数器值是可恢复的。若发生故障，则从日志重新构造计数器值，因此可以保证继续生成唯一的值。

■可以将标识列值存入高速缓存，以获得更好的性能。

定义一个具有标识列的表

D:DB2BIN>db2 -td@

SQL> create table test_idl1
SQL> ( no int GENERATED BY DEFAULT AS IDENTITY,  --缺省情况下生成的标识列
SQL>   name char(8)
SQL> )@
DB20000I  SQL命令成功完成。
SQL>
SQL> create table test_idl2
SQL> ( no int GENERATED ALWAYS AS IDENTITY,      --始终生成的标识列
SQL>   name char(8)
SQL> )@

通过数据字典，我们看一下内部的标识列是怎么通过sequence定义的

继续以上面的两个测试表为例,看一下DB2内部是如何实现这两个标识列的.
首先,我们应该有一个基本概念,没看到前面介绍的同志,要清楚,在DB2中,标识列实际上在系统中定义了一个对应的sequence来实现的.
SQL> SELECT tabschema,tabname,colname,identity,generated FROM SYSCAT.COLUMNS where tabname in ('TEST_IDL1','TEST_IDL2')@

TABSCHEMA     TABNAME      COLNAME     IDENTITY  GENERATED
------------- ------------ ----------- --------- ---------
ZHANGRP       TEST_IDL1    NO          Y         D
ZHANGRP       TEST_IDL1    NAME        N         
ZHANGRP       TEST_IDL2    NO          Y         A
ZHANGRP       TEST_IDL2    NAME        N

  4 条记录已选择。
SQL>
从这个信息,可以看的出来,表TEST_IDL1,TEST_IDL2上分别定义了一个缺省情况下生成和必须生成的标识列.关于表的信息,我们可以从如下的系统目录中获取:
SELECT * FROM SYSIBM.SYSTABLES where name in ('TEST_IDL1','TEST_IDL2');
　
继续往下周,通过下面的系统目录视图,我们可以进一步看到这两张表的两个标识列对应的序列ID
SQL> SELECT tabschema,tabname, colname,seqid 
SQL> FROM SYSCAT.COLIDENTATTRIBUTES 
SQL> where tabschema='ZHANGRP' and tabname in ('TEST_IDL1','TEST_IDL2');

TABSCHEMA   TABNAME    COLNAME    SEQID
----------- ---------- ---------- ------
ZHANGRP     TEST_IDL1  NO         5
ZHANGRP     TEST_IDL2  NO         6

  2 条记录已选择。

SQL>
从上面的查询可以知道,这两张表上的字段名均为NO的标识列对应的SEQID分别为5和6,有了这个信息，我们就可以知道到底系统为我们定义了哪两个序列来为标识列服务的了.
SQL> SELECT seqschema, seqname,owner,seqid,seqtype FROM SYSCAT.SEQUENCES where seqid in (5,6);

SEQSCHEMA   SEQNAME             OWNER        SEQID  SEQTYPE
----------- ------------------- ----------- ------  --------
ZHANGRP     SQL090227162615120  ZHANGRP          5  I
ZHANGRP     SQL090227162615870  ZHANGRP          6  I
  2 条记录已选择。
SQL>
将上面的两个语句合并一下:
SQL> SELECT s.seqschema,s.owner,c.tabname,c.colname,s.seqname,s.seqid,s.seqtype
SQL> FROM SYSCAT.SEQUENCES s, SYSCAT.COLIDENTATTRIBUTES c
SQL> where c.tabschema='ZHANGRP' and
SQL>       c.tabname in ('TEST_IDL1','TEST_IDL2') and
SQL>       c.tabschema=s.seqschema and
SQL>       c.seqid=s.seqid;

SEQSCHEMA  OWNER      TABNAME     COLNAME   SEQNAME            SEQID SEQTYPE
---------- ---------- ----------- --------- ------------------ ----- -------
ZHANGRP    ZHANGRP    TEST_IDL1   NO        SQL090227162615120 5     I
ZHANGRP    ZHANGRP    TEST_IDL2   NO        SQL090227162615870 6     I
  2 条记录已选择。

SQL>
还有一个有用的目录视图,可以直接看到标识列与表的依赖关系:
SQL> SELECT bname,bschema,btype,dname,dschema FROM SYSIBM.SYSDEPENDENCIES where dname in ('TEST_IDL1','TEST_IDL2');

BNAME               BSCHEMA    BTYPE     DNAME      DSCHEMA
------------------- ---------- --------- ---------- -------
SQL090227162615120  ZHANGRP    Q         TEST_IDL1  ZHANGRP
SQL090227162615870  ZHANGRP    Q         TEST_IDL2  ZHANGRP
  2 条记录已选择。

SQL>
IDENTITY 列和序列的对比
虽然在 IDENTITY 列和序列之间存在相似之处，但是也存在差别。当设计数据库和应用程序时可以使用其各自的特征。

标识列具有下列特征：
    ■仅当创建了表时，才可以将标识列定义为表的一部分。一旦创建了表，您就不能改变它来添加一个标识列。（然而，可以改变现有的标识列特征）
    ■标识列自动为单个表生成值。 
    ■当将标识列定义为 GENERATED ALWAYS 时，始终由数据库管理器生成所用的值。在修改表的内容期间，不允许应用程序来提供它们自己的值.
序列对象具有下列特征：
    ■序列对象是未与任何一个表关联的数据库对象。 
    ■序列对象生成可在任何 SQL 或 XQuery 语句中使用的顺序值。 
    ■由于任何应用程序可以使用序列对象，所以有两种表达式可用来控制如何检索指定序列中的下一个值和在正在执行的语句之前生成的值。对于当前会话中的先前语句，PREVIOUS VALUE 
表达式对指定序列返回最新生成的值。NEXT VALUE 表达式对指定序列返回下一个值。使用这些表达式允许在几个表内的几个 SQL 和 XQuery 语句中使用相同值。