IBMi (AS400) fans only : how does it works "ADD VERSIONING" and "USE HISTORY TABLE" to take trace of every changes on a table. This looks like magic!

#IBMiSample

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

Unleash the Magic:

Experience the true power of 'ADD VERSIONING' and 'USE HISTORY TABLE' as we delve into the depths of tracking changes and accessing historical data.

Conclusion:

Witnessing the seamless tracking of changes and accessing historical data feels nothing short of magical. It's a testament to the sophistication and efficiency of IBMi's capabilities.

That's it!

I appreciate all the comments made on this blog.