Last Updated on 1 Novembre 2020 by Guglielmo Maffeis
Di Stefano Tassi, IT & Application Services di Deda.Cloud
Mi hanno chiesto una modifica da un’applicazione che permettesse di verificare lo stato di un record ad una certa data. Per semplificare e creare un case study ipotizziamo una semplice tabella anagrafica personale dipendente.
L’entità dipendente è così disegnata
ID | Primary Key |
NAME | Nome |
SURNAME | Cognome |
DEPTN | Reparto |
NOTE | Note |
HIRING_DATE | Data Assunzione |
DISMISS_DATE | Data Dimissioni |
La tabella sul sistema viene quindi definita come
CREATE OR REPLACE TABLE HR ( ID INTEGER NOT NULL ,NAME CHAR(30) ,SURNAME CHAR(30) ,DEPTN CHAR(10) ,NOTE CHAR(30) ,HIRING_DATE FOR COLUMN HDATE DATE ,DISMISS_DATE FOR COLUMN DDATE DATE ,PRIMARY KEY (ID) ) |
Creo a questo punto un paio di righe d’esempio nella nostra tabella
INSERT INTO HR (“ID”, NAME, SURNAME, DEPTN, NOTE, HIRING_DATE, DISMISS_DATE) VALUES(1, ‘Giuseppe’, ‘Verdi’, ‘BIG’, ‘Nota 1’, ‘2020-01-01’, NULL); INSERT INTO HR (“ID”, NAME, SURNAME, DEPTN, NOTE, HIRING_DATE, DISMISS_DATE) VALUES(2, ‘Giacomo’, ‘Puccini’, ‘BIG’, ‘Nota 2’, ‘2020-02-01’, NULL) |
i dati presenti in tabella sono quindi
Tracciare le modifiche
I sistemi per gestire e tracciare le modifiche possono essere, per esempio
- applicativamente: per ogni modifica sulla tabella principale inserire in una tabella accessoria i dati prima della modifica; occorre un intervento in ogni programma che modifica/cancella un record di una routine per tracciare su un’altra tabella
- in maniera sistemica: utilizzare un trigger per effettuare la stessa operazione applicativa, intercettando tramite un trigger le modifiche. Il trigger stesso provvede a salvare le informazioni in una tabella accessoria
Dalla 7.3 esiste la possibilità di devolvere al sistema il tracciamento delle modifiche.
Per poter abilitare questa funzionalità occorre aggiungere alcune colonne di servizio alla Tabella originale; trattandosi di colonne di tipo particolare non si può intervenire tramite DDS ma solo tramite interfaccia SQL (Alter table).
Ricordiamo che anche se la tabella è old style (DDS) l’alter table può ugualmente essere utilizzata; si può presentare il problema del recordlevel (se la tabella viene letta in maniera nativa (RLA) da RPG o Cobol).
Esistono però sistemi molto semplici di rimappatura dei Files (Tabelle) e LF correlati che permettono di mantenere inalterato il record level, garantendo quindi il corretto funzionamento dei programmi esistenti.
L’aggiunta di campi di servizio si ottiene con
alter table HR add TT_START TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW BEGIN implicitly hidden; alter table HR add TT_END TIMESTAMP(12) NOT NULL GENERATED ALWAYS AS ROW END implicitly hidden; alter table HR add TT_ID TIMESTAMP(12) GENERATED ALWAYS AS TRANSACTION START ID implicitly hidden; alter table HR add TT_DCO CHAR(1) not null GENERATED ALWAYS as (data change operation) implicitly hidden; |
Notare che [implicitly hidden] nasconde la colonna in una SELECT * from a meno che non venga esplicitamente dichiarata nella select.
L’istruzione successiva definisce il “periodo” basato su SYSTEM_TIME nella tabella, che comprende data inizio e data fine validità della riga.
alter table HR add PERIOD SYSTEM_TIME (TT_START, TT_END); |
per attivare il tracciamento occorre innanzitutto avere una tabella identica (ma senza constraint, per questo si usa il create . . . like) alla tabella corrente:
CREATE TABLE HR_TT LIKE HR
poi
ALTER TABLE HR ADD VERSIONING USE HISTORY TABLE HR_TT;
notare che by design esiste un vincolo per il quale la tabella “HISTORY” deve risiedere nello stesso schema (Libreria) della tabella originale.
una volta attivato tutto continua a funzionare come prima: esiste solo una nuova tabella gestita dal sistema che traccia le modifiche.
Qui vediamo come occorre specificare le colonne [implicitly hidden] per poterle mostrare e come vengono inizializzate al momento dell’attivazione
Lettura dei dati
Se si utilizzano i metodi RLA (Record Level Access) ovvero le funzioni native RPG / COBOL (SETLL/READ/CHAIN…) si ha l’accesso alla tabella originale, senza poter accedere ai dati della tabella History.
Per poter analizzare/leggere i dati in maniera dipendente da una determinata data occorre forzatamente l’accesso tramite interfaccia SQL
Partiamo dall’istruzione SELECT:
SELECT * from HR
In questo caso si accede direttamente alla tabella originale
Effettuiamo ora qualche operazione sulla tabella:
Insert: notiamo la TT_START impostata col timestamp di inserimento
Update: notiamo la data di inizio validità dell’update
Delete
Esistono però estensioni alla SELECT che permettono di accedere ai dati storicizzati:
SELECT * FROM HR
FOR SYSTEM_TIME AS OF CURRENT TIMESTAMP – 1 DAY;
CURRENT TIMESTAMP – 1 DAY à significa che vedrò la situazione della tabella esattamente a ieri.
Posso inoltre specificare un timestamp (in questo caso viene “rescuscitata” la riga precedentemente cancellata):
Alternativamente, per esempio da rpg, si può utilizzare una host variable
SELECT * FROM HR
FOR SYSTEM_TIME AS OF CURRENT :MY_TS;
E’ inoltre possibile impostare un registro con un timestamp
posso impostare un registro di sistema
set current temporal SYSTEM_TIME = :MY_TS ;
select current temporal SYSTEM_TIME from sysibm.sysdummy1;
SELECT * FROM HR
FOR SYSTEM_TIME AS OF CURRENT temporal system_time
è possibile inoltre interagire con la query utilizzando periodi (da … a …)
In questo esempio abbiamo utilizzato l’ìnterfaccia SQL per interagire col DB: le interazioni possibili sono
RLA (RPG/CBL) | SQL | |
read tradizionale | Y | Y |
read con funzionalità TT | N | Y |
Insert/update/delete | Y | y |
L’accesso ad una TT con clausole legate all’inclusione su base temporale provocano l’accesso ad entrambe le tabelle; dipendentemente dalla numerosità delle righe in tabella può essere necessaria una politica di indicizzazione
Highlights
E’ importante notare che i timestamp utilizzati sono system dependent, non user dependent: questo preclude purtroppo l’utilizzo in tabelle come, per esempio, i listini con date di inizio validità. Probabilmente in futuro verrà introdotta una funzionalità legata a timestamp non di sistema (nel DB2 LUW esiste già un registro CURRENT TEMPORAL BUSINESS_TIME)
Le Temporal Tables e la Tabella che contiene le versioni devono essere sotto journal
Alcune operazioni non sono permesse (es. Truncate table sulla TT, verificare su Temporal Table Restrictions https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzahf/rzahftmprlrestrictions.htm)
Nel caso di tabelle TT con un alto numero di variazioni occorre ipotizzare un sistema di cleaning della TT-Versioned per rimuovere dati non più interessanti (operazione permessa e a cura dell’utente)
Deda.Cloud è Libertà, Cultura del dato e Sicurezza per la Continuità e la Crescita del Business. Aiutiamo le aziende con servizi modulari e flessibile, con un approccio hi-tech e hi-touch, per saper comprendere e rispondere al meglio alle tue esigenze.
Contattaci se hai bisogno di un partner IT