Hi Aleksey, thanks for your comments - it's been a while since I looked into this. A good implementation of the temporal tables feature can be found in db2 (in my view). Below some code for db2 I used for testing and showing what I would like to do. For me this is the way it should work - no need for any kind of workarounds :) Best, Robert -- https://www.ibm.com/support/pages/db2-version-115-linux-unix-and-windows-eng... -- https://www.ibm.com/docs/en/db2/11.5?topic=tables-history -- schema neu erzeugen -- drop schema robert restrict; -- create schema robert; -- History Tablespaces erzeugen -- BEGIN -- IF EXISTS (select * from SYSCAT.TABLESPACES where TBSPACE = 'HISTIDXSPACE_32KB') THEN -- PREPARE stmt FROM 'drop tablespace HISTIDXSPACE_32KB;'; -- EXECUTE stmt; -- END IF; -- END; -- CREATE LARGE TABLESPACE HISTIDXSPACE_32KB PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BUFFERP_32KB; -- BEGIN -- IF EXISTS (select * from SYSCAT.TABLESPACES where TBSPACE = 'HISTUSERSPACE_32KB') THEN -- PREPARE stmt FROM 'drop tablespace HISTUSERSPACE_32KB;'; -- EXECUTE stmt; -- END IF; -- END; -- CREATE LARGE TABLESPACE HISTUSERSPACE_32KB PAGESIZE 32 K MANAGED BY AUTOMATIC STORAGE EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BUFFERP_32KB; set schema robert; --alter table robert.kunde drop versioning; -- benötigt, da History Tabelle protected angelegt "WITH RESTRICT ON DROP" BEGIN IF EXISTS (SELECT * FROM sysibm.SYSTABLES WHERE CREATOR = 'ROBERT' AND NAME = 'KUNDE') THEN PREPARE stmt FROM 'alter table robert.kunde drop restrict on drop;'; EXECUTE stmt; END IF; END; BEGIN IF EXISTS (SELECT * FROM sysibm.SYSTABLES WHERE CREATOR = 'ROBERT_H' AND NAME = 'KUNDE') THEN PREPARE stmt FROM 'alter table robert_h.kunde drop restrict on drop;'; EXECUTE stmt; END IF; END; drop table if exists robert.kunde; -- drop auch implizit die History Tabelle, da diese nun nicht mehr "protected" ist. drop table if exists robert_h.kunde; -- drop auch implizit die History Tabelle, da diese nun nicht mehr "protected" ist. -- Tabelle Kunde erzeugen (in "normalem" Tablespace) CREATE TABLE robert.kunde ( OB_OID bigint PRIMARY KEY NOT NULL, KUNDENNR varchar(40), NAME varchar(40), ts TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN IMPLICITLY HIDDEN, te TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END IMPLICITLY HIDDEN, tx TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS TRANSACTION START ID IMPLICITLY HIDDEN, SESSION_USER_ID VARCHAR(128) WITH DEFAULT SESSION_USER implicitly hidden, CURRENT_USER_ID VARCHAR(128) WITH DEFAULT CURRENT_USER implicitly hidden, PERIOD SYSTEM_TIME (ts, te) ) IN USERSPACE_32KB INDEX IN IDXSPACE_32KB COMPRESS YES; -- Tabelle Kunde_h erzeugen (in history Tablespace) CREATE TABLE robert_h.kunde LIKE robert.kunde WITH RESTRICT ON DROP IN HISTUSERSPACE_32KB INDEX IN HISTIDXSPACE_32KB COMPRESS YES; -- History Tabelle kann bspw. auch einen Index erhalten... CREATE INDEX robert_h.idx_oB_oid_robert_h_kunde ON robert_h.kunde(OB_OID) ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COMPRESS YES; -- Mappe die Tabellen Kunde und Kunde_h (damit hat die Tabelle Kunde die Historisierungstabelle Kunde_h) ALTER TABLE robert.kunde ADD VERSIONING USE HISTORY TABLE robert_h.kunde; -- Neue Zeilen in der History Tabelle einfach immer am Ende anfügen ALTER TABLE robert_h.kunde APPEND ON; -- Reorg CALL SYSPROC.ADMIN_CMD('REORG TABLE robert.kunde'); -- Evtl. auf History Tabelle nicht nötig ? --CALL SYSPROC.ADMIN_CMD('REORG TABLE robert.kunde_h'); -- insert 2 Zeilen in Kunde insert into robert.kunde (OB_OID, KUNDENNR, NAME) values ('1','1234','Test 1'); insert into robert.kunde (OB_OID, KUNDENNR, NAME) values ('2','5678','Test 2'); insert into robert.kunde (OB_OID, KUNDENNR, NAME) values ('3','9898','Test 3'); -- update Name beider Kunden, damit Historie erzeugt wird. update robert.kunde set NAME = 'Update 1' where OB_OID = '1'; update robert.kunde set NAME = 'Update 2' where OB_OID = '2'; update robert.kunde set NAME = 'Update 3' where OB_OID = '3'; -- select * kunde select * from robert.kunde; -- select * kunde mit hidden ts cols select *, ts, te from robert.kunde; -- select * kunde_h select * from robert_h.kunde; -- select * kunde_h mit hidden ts cols select *, ts, te from robert_h.kunde; -- select kunde for set CURRENT TEMPORAL SYSTEM_TIME = CURRENT_TIMESTAMP; set CURRENT TEMPORAL SYSTEM_TIME = NULL; select * from robert.kunde --for system_time between '01.01.2023' and CURRENT_TIMESTAMP where OB_OID = 1 ; select * from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; -- select kunde for mit hidden ts cols select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; -- Pseudonomisierung (die Daten bleiben in kunde erhalten) update kunde set KUNDENNR ='Pseudonymisiert', NAME = 'Pseudonymisiert' where OB_OID = 1; -- select * kunde Pseudonymisiert select * from robert.kunde; -- kunde for nach update Pseudonymisiert select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; -- Daten des Kunden werden nun auch in der History Tabelle pseudonymisiert update robert_h.kunde set KUNDENNR ='Pseudonymisiert', NAME = 'Pseudonymisiert' where OB_OID = 1; -- kunde for nach update in kunde_h Pseudonymisiert select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP where OB_OID = 2; -- Anonymisierung update kunde set KUNDENNR ='Anonymisiert', NAME = 'Anonymisiert' where OB_OID = 2; delete from kunde where OB_OID = 2; -- select * kunde Anonymisiert select * from robert.kunde; -- kunde for nach update Anonymisiert select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; update robert_h.kunde set KUNDENNR ='Anonymisiert', NAME = 'Anonymisiert' where OB_OID = 2; -- kunde for nach update in kunde_h Anonymisiert select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; -- Finalisierung delete from robert_h.kunde where OB_OID = 2; -- select * kunde finalisiert select * from robert.kunde; -- select * kunde_h finalisiert select * from robert_h.kunde; -- kunde for nach Finalisierung delete select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; delete from robert_h.kunde where OB_OID = 2; -- kunde for nach delete in kunde_h select *, ts, te from robert.kunde for system_time between '01.01.2023' and CURRENT_TIMESTAMP; select * from SYSCAT.TABLES where TABNAME = 'KUNDE'; Zitat von Aleksey Midenkov <midenok@gmail.com>:
Hi Robert,
as of 10.11 there is @@system_versioning_insert_history, so the only thing you need is the ability to delete history rows. As UPDATE may be done as DELETE + INSERT. You should not have many such kind of operations so it would be performance-critical, should you?
If we can delete the whole history why not delete the specific rows? The original idea was to keep contiguity and consistency of all history, but surely the user requirements may be much more versatile. I personally vote for maximum power for users keeping the limitations as non-default minor utilities for specific areas like banking, government, etc. So, yes DELETE HISTORY .. WHERE is a nice idea and coincidentally I just thought about it yesterday when it was needed for implementation of System Versioning in Spider.
Why must the history be dropped when I disable it for a table again? Could it be kept?> By the way the "simple" drop of system versioning didn't work. Needed to drop ts, te cols, period and the system versioning in one alter.
So it is one more feature to drop just SYSTEM VERSIONING and it retains the old history but does not generate new history. Again, it is something that violates the original idea of history consistency and contiguity, but finally why not?
So if you've dropped just SYSTEM VERSIONING but hadn't dropped PERIOD history should not be generated but temporal queries should work. When dropped PERIOD system fields should become normal fields and history should become current data. I think that is the most user-friendly way, as you have the freedom to delete history or not. Before dropping PERIOD history may be deleted by DELETE HISTORY, after dropping PERIOD history may be deleted by the usual "DELETE .. WHERE row_end <" command.
Thanks, Aleksey
On Sun, Jan 15, 2023 at 6:34 PM Robert Palm <developer@robert-palm.de> wrote:
Dear devs,
I am reaching out to you, because I wanted to discuss a feature request for the history of system versioned temporal tables.
Is it possible to add a special setting so that I can update and delete the history?
Update, e.g. because sometimes it is necessary to pseudo-/anonymize former customer data.
Delete, e.g. because former customers demand all data must be deleted.
German dsgvo rules...
I know it opposes the idea of having a non changeable history.
But I think there are cases where it is justified and needed.
So I am asking for something like
system_versioning_delete_history system_versioning_update_history
Or does there exist some other workaround to do this?
Other ideas: - add a where clause to "delete history" - add update history with where clause - drop system versioning, do the update/ delete and enable system versioning again in one commit?
Why must the history be dropped when I disable it for a table again? Could it be kept?
By the way the "simple" drop of system versioning didn't work. Needed to drop ts, te cols, period and the system versioning in one alter.
Another question: How to migrate system versioned tables with SQL?
Thank you!
Kind regards
Robert _______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp