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.
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.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/725820/viewspace-2125523/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Mysql資料庫Slow_log中的lock_Time和Query_timeMySql資料庫
- ntpdate會導致mysql slow query log出現很大的query timeMySql
- 排除表和query查詢條件的expdp、impdp
- 查詢real-time apply、real-time query的檢視APP
- 詳解MySQL慢日誌（上）query_time\start_time\lock_time 的坑MySql
- 11g 新特性 real time apply ＋real time queryAPP
- 級聯slave的延遲計算和query event exe time獲取方法
- DB2頁大小、表大小和表空間大小限制DB2
- 【DG】Real-time query實時查詢操作
- mysql 配置 General Query Log和# Slow Query LogMySql
- db2表空間DB2
- db2表掛起DB2
- PyTorch Geometric Temporal 介紹 —— 資料結構和RGCN的概念PyTorch資料結構GC
- flashback version query和 flashback transaction query簡單應用
- HttpWebRequest的timeout和ReadWriteTimeoutHTTPWeb
- db2大表統計DB2
- DB2 批量刪除表DB2
- DB2表空間增加DB2
- DB2表的匯入與匯出DB2
- DB2 恢復誤刪除的表DB2
- 閃回之 Flashback Query （dml表、過程、函式、包等）、Flashback version Query函式
- 【MySQL】慢日誌大量生成 long-query-time “失效”問題MySql
- TIMESTAMP和TIMESTAMP WITH TIME ZONE之間的總結
- db2檢視錶空間和增加表空間容量DB2
- DB2中結構化型別和型別化表的管理 (轉)DB2型別
- 6、TIMESTAMP WITH TIME ZONE和TIMESTAMP WITH LOCAL TIME ZONE的實踐理解
- oracle 10g flashback version query 和 flashback transaction query實驗Oracle 10g
- python date 和 datetime 的取值範圍（對比 Mysql 的 datetime 和 timestamp）PythonMySql
- 【Lintcode】1891. Travel Plan
- [DB2]DB2備份和恢復DB2
- DB2匯出模式 MBS 下的所有表DB2模式
- [DB2]使用recover命令找回刪除的表DB2
- 【DB2學習】使用reorgchk 收集表的資訊DB2GC
- db2清空表釋放空間的快速方法DB2
- DB2匯出給定表DB2
- db2 表被誤刪（drop）DB2
- 檢視DB2表結構DB2
- 快速清空DB2表記錄DB2