Prayer

標識列的基本概念
標識列(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:\DB2\BIN>db2 [email=-td@SQL]-td@SQL[/email]> 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> )@
DB20000I  SQL命令成功完成。

體驗一下:
SQL> insert into test_idl1(name) values('one')@
DB20000I  SQL命令成功完成。
SQL> insert into test_idl1(name) values('two')@
DB20000I  SQL命令成功完成。
SQL> insert into test_idl1(no,name) values(3,'three')@

DB20000I  SQL命令成功完成。
SQL> select * from test_idl1@
NO          NAME
----------- --------
          1 one
          2 two
          3 three

  3 條記錄已選擇。

SQL> insert into test_idl2(name) values('one')@
DB20000I  SQL命令成功完成。
SQL> insert into test_idl2(name) values('two')@
DB20000I  SQL命令成功完成。
SQL> insert into test_idl2(no,name) values(3,'three')@
DB21034E  該命令被當作 SQL 語句來處理，因為它是無效的“命令行處理器”命令。在SQL 處理期間，它返回：SQL0798N
不能為定義為 GENERATED ALWAYS 的列 "NO" 指定值。  SQLSTATE=428C9
SQL>
SQL> select * from test_idl2@
NO          NAME
----------- --------
          1 one
          2 two
  2 條記錄已選擇。
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>