how does it works "ADD VERSIONING" and "USE HISTORY TABLE" to take trace of every changes on a table. This looks like magic!


This looks like magic!
The operating system, in a totally automatic way, keeps track of every change in the data.

This simple technique allows you to find the value of a field on a certain date and time.



use STRSQL


Step 1)
CREATE TABLE CUSTOMER
(CUSTOMER_ID BIGINT NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH +1 INCREMENT BY +1 )
,CUSTOMER_NAME VARCHAR(10)
,ADDRESS VARCHAR(20))


Step 2)
alter table customer
add system_start timestamp(12)
generated always as row begin not null
add system_end timestamp(12)
generated always as row end not null
add tx_start timestamp(12)
generated always as transaction start id implicitly hidden
add period system_time (system_start, system_end)


Step 3)
create table customer_h like customer


Step 4)
alter table customer add versioning use history table customer_h



Step 5)
Now use command line:

CRTJRNRCV JRNRCV(VDOTEST1/JRNRCVNAME) THRESHOLD(150) TEXT('Journal receiver')


Step 6)
CRTJRN JRN(VDOTEST1/jrnname) JRNRCV(VDOTEST1/jrnrcvname) MNGRCV(*SYSTEM) DLTRCV(*YES) TEXT('Journal for table customer')


Step 7)
STRJRNPF FILE(VDOTEST1/customer) JRN(VDOTEST1/jrnname) OMTJRNE(*OPNCLO)


Step 8)
STRJRNPF FILE(VDOTEST1/customer_h) JRN(VDOTEST1/jrnname) OMTJRNE(*OPNCLO)

...and the magic comes true...

Try to fill the data. Use STRSQL again:


insert into customer (customer_name,Address) values ('Philip N.' ,'My Home')


select * from customer










update customer set address = 'My Place' where customer_id = 1

select * from customer











SELECT * FROM customer FOR SYSTEM_TIME as of ' 2000-01-01-00.00.00.000000000000'




That's right, at 2000-01-01 customer was empty!



SELECT * FROM customer FOR SYSTEM_TIME as of '2021-04-20-15.13.21.385678000243'

This is Customer just a moment before the update:




SELECT * FROM customer FOR SYSTEM_TIME as of '2021-04-20-15.13.21.385678000244'

This is Customer just a moment after the update:






As well as specifying a point in time on individual queries, it is also possible in DB2 to set a special register after which all subsquent queries are processed at that particular point in time - SET CURRENT TEMPORAL SYSTEM_TIME = <timestamp>. This special register lasts for the lifetime of a connection.

Let’s try:

SET CURRENT TEMPORAL SYSTEM_TIME = '2021-04-20-15.13.21.385678000243'

SELECT * FROM customer








To unset TEMPORAL SYSTEM_TIME close SQL session.





When a deletion takes place the row is moved to customer_h and the system end time is set to the deletion time.



Let’s try:

delete from customer where customer_id = 1

SELECT * FROM customer










And now, let's see the magic of spells:

select * from customer for system_time between current timestamp -1 day and current timestamp where customer_id = 1 order by system_start







Here is the entiere history of Customer id=1




That's it!

Comments

Post a Comment

Popular Posts