DB2隱藏列

DB2從9.5以後引入了隱藏列，就是你用select * from table的方式查詢不到這個列，但是可以顯式的指定列名來看該列的值。
CREATE TABLE CUSTOMER
(
CUSTNO INTEGER NOT NULL,
CUST_NAME VARCHAR(50),
CUST_INFOCHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED BY DEFAULT FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP
)

插入資料的時候，不用指定隱藏列
INSERT INTO CUSTOMER VALUES(1,'111')
INSERT INTO CUSTOMER VALUES(2,'222')
INSERT INTO CUSTOMER VALUES(3,'333')
INSERT INTO CUSTOMER VALUES(4,'444')

隱藏列的值在這裡是插入的時間戳，如果你更新一行，那麼這個時間戳也是相應改變的。

describe 表是可以看到這個隱藏列的
db2 => describe table customer

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
CUSTNO                          SYSIBM    INTEGER                      4     0 No    
CUST_NAME                       SYSIBM    VARCHAR                     50     0 Yes   
CUST_INFOCHANGE                 SYSIBM    TIMESTAMP                   10     6 No    

  3 record(s) selected.


可以顯式的檢視隱藏列
db2 => select CUSTNO,CUST_NAME,CUST_INFOCHANGE from customer

CUSTNO      CUST_NAME                                          CUST_INFOCHANGE           
----------- -------------------------------------------------- --------------------------
          1 111                                                2015-11-26-07.41.52.610475
          2 222                                                2015-11-26-07.41.52.623278
          3 333                                                2015-11-26-07.41.52.898431
          4 444                                                2015-11-26-07.41.53.674208

  4 record(s) selected.

但是使用select *就看不到這個列，這就是為什麼叫隱藏列的原因吧，因為這種情況下看不到
db2 => select * from customer

CUSTNO      CUST_NAME                                         
----------- --------------------------------------------------
          1 111                                               
          2 222                                               
          3 333                                               
          4 444                                               

  4 record(s) selected.

匯出匯入資料實驗

export to customer.del of del modified by implicitlyhiddeninclude select * from customer
load from customer.del of del modified by implicitlyhiddeninclude identityoverride replace into customer nonrecoverable

但是這裡有個問題，還是需要繼續查一下，如果在定義此列的時候使用的是ALWAYS的方式
CUST_INFOCHANGE TIMESTAMP NOT NULL IMPLICITLY HIDDEN GENERATED ALWAYS FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP

在執行load命令的時候會出現問題。
db2 => load from customer.del of del modified by implicitlyhiddeninclude identityoverride replace into customer nonrecoverable
SQL3551W  The table contains at least one GENERATED ALWAYS column that the
utility will override.

SQL3109N  The utility is beginning to load data from file
"/home/db2inst1/customer.del".

SQL3500W  The utility is beginning the "LOAD" phase at time "11/26/2015
07:39:05.270055".

SQL3519W  Begin Load Consistency Point. Input record count = "0".

SQL3520W  Load Consistency Point was successful.

SQL3550W  The field value in row "F0-1" and column "3" is not NULL, but the
target column has been defined as GENERATED ALWAYS.

SQL3185W  The previous error occurred while processing data from row "F0-1" of
the input file.

SQL3550W  The field value in row "F0-2" and column "3" is not NULL, but the
target column has been defined as GENERATED ALWAYS.

SQL3185W  The previous error occurred while processing data from row "F0-2" of
the input file.

SQL3550W  The field value in row "F0-3" and column "3" is not NULL, but the
target column has been defined as GENERATED ALWAYS.

SQL3185W  The previous error occurred while processing data from row "F0-3" of
the input file.

SQL3550W  The field value in row "F0-4" and column "3" is not NULL, but the
target column has been defined as GENERATED ALWAYS.

SQL3185W  The previous error occurred while processing data from row "F0-4" of
the input file.

SQL3227W  Record token "F0-1" refers to user record number "1".

SQL3227W  Record token "F0-2" refers to user record number "2".

SQL3227W  Record token "F0-3" refers to user record number "3".

SQL3227W  Record token "F0-4" refers to user record number "4".

SQL3110N  The utility has completed processing.  "4" rows were read from the
input file.

SQL3519W  Begin Load Consistency Point. Input record count = "4".

SQL3520W  Load Consistency Point was successful.

SQL3515W  The utility has finished the "LOAD" phase at time "11/26/2015
07:39:05.317399".

SQL3107W  At least one warning message was encountered during LOAD processing.


Number of rows read         = 4
Number of rows skipped      = 0
Number of rows loaded       = 0
Number of rows rejected     = 4
Number of rows deleted      = 0
Number of rows committed    = 4