DB2的Temporal表和Time Travel Query

DB2的Temporal表和Time Travel Query

CREATE TABLE benefits (
PolicyID INT primary key not null,
coverage INT,
smoker CHAR(1),
dependents SMALLINT,
sys_start TIMESTAMP(12) GENERATED ALWAYS AS ROW BEGIN NOT NULL IMPLICITLY HIDDEN,
sys_end TIMESTAMP(12) GENERATED ALWAYS AS ROW END NOT NULL IMPLICITLY HIDDEN,
trans_start TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN,
PERIOD SYSTEM_TIME (sys_start, sys_end)
)

CREATE TABLE benefits_history like benefits
ALTER TABLE benefits ADD VERSIONING USE HISTORY TABLE benefits_history
==================================================================================================

INSERT INTO benefits VALUES (111, 50000, 'N',0)
===================================================================================================
db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------
        111       50000 N               0 2016-09-24-19.20.09.241023000000 9999-12-30-00.00.00.000000000000 -                               

  1 record(s) selected.

db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits_history

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------

  0 record(s) selected.

==================================================================================================
update benefits set coverage=75000,dependents=1
==================================================================================================
db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------
        111       75000 N               1 2016-09-24-19.21.17.803646000000 9999-12-30-00.00.00.000000000000 -                               

  1 record(s) selected.

db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits_history

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------
        111       50000 N               0 2016-09-24-19.20.09.241023000000 2016-09-24-19.21.17.803646000000 -                               

  1 record(s) selected.

================================================================================================
update benefits set coverage=100000,dependents=2
================================================================================================
db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------
        111      100000 N               2 2016-09-24-19.23.43.289667000000 9999-12-30-00.00.00.000000000000 -                               

  1 record(s) selected.

db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits_history

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------
        111       50000 N               0 2016-09-24-19.20.09.241023000000 2016-09-24-19.21.17.803646000000 -                               
        111       75000 N               1 2016-09-24-19.21.17.803646000000 2016-09-24-19.23.43.289667000000 -                               

  2 record(s) selected.

===============================================================================================
delete from benefits where POLICYID=111
===============================================================================================
db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------

  0 record(s) selected.

db2 => select POLICYID, COVERAGE, SMOKER, DEPENDENTS, SYS_START, SYS_END, TRANS_START from benefits_history

POLICYID    COVERAGE    SMOKER DEPENDENTS SYS_START                        SYS_END                          TRANS_START                     
----------- ----------- ------ ---------- -------------------------------- -------------------------------- --------------------------------
        111       50000 N               0 2016-09-24-19.20.09.241023000000 2016-09-24-19.21.17.803646000000 -                               
        111       75000 N               1 2016-09-24-19.21.17.803646000000 2016-09-24-19.23.43.289667000000 -                               
        111      100000 N               2 2016-09-24-19.23.43.289667000000 2016-09-24-19.25.50.290394000000 -                               

  3 record(s) selected.

===============================================================================================
####FOR SYSTEM_TIME AS OF ...:，某個時間點的資料
db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME AS OF '2016-09-24-19.21.17.803646000000' WHERE policyid = 111

COVERAGE   
-----------
      75000

  1 record(s) selected.

db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME AS OF '2016-09-24-19.20.09.241023000000' WHERE policyid = 111

COVERAGE   
-----------
      50000

  1 record(s) selected.

db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME AS OF '2016-09-24-19.23.43.289667100000' WHERE policyid = 111

COVERAGE   
-----------
     100000

  1 record(s) selected.

db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME AS OF '2016-09-24-19.25.50.290394000000' WHERE policyid = 111

COVERAGE   
-----------

  0 record(s) selected.

####FOR SYSTEM_TIME FROM ... TO ...:的查詢，包括FROM時間點的資料，但是不包括To的時間點的資料
db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME FROM '2016-09-24-19.20.09.241023000000' TO '2016-09-24-19.21.17.803646000000' WHERE policyid = 111

COVERAGE   
-----------
      50000

  1 record(s) selected.

db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME FROM '2016-09-24-19.20.09.241023000000' TO '2016-09-24-19.21.17.803646100000' WHERE policyid = 111

COVERAGE   
-----------
      50000
      75000

  2 record(s) selected.

####FOR SYSTEM_TIME BETWEEN ... AND ...:的查詢，包括BETWEEN時間點的資料，也包括AND的時間點的資料
db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME BETWEEN '2016-09-24-19.20.09.241023000000' AND '2016-09-24-19.21.17.803646000000' WHERE policyid = 111

COVERAGE   
-----------
      50000
      75000

  2 record(s) selected.

db2 => SELECT coverage FROM benefits FOR SYSTEM_TIME BETWEEN '2016-09-24-19.20.09.241023000000' AND '2016-09-24-19.21.16.803646000000' WHERE policyid = 111

COVERAGE   
-----------
      50000

  1 record(s) selected.