06 - VarieRedazionali

Temporal Tables

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
SURNAMECognome
DEPTNReparto
NOTENote
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 tradizionaleYY
read con funzionalità TTNY
Insert/update/deleteYy

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

www.deda.cloud

Related Posts
DB2 for i SQL – Stringhe – POSSTR-LOCATE-LOCATE_IN_STRING (IT)

Introduzione Spesso, nelle nostre applicazioni, abbiamo la necessità di lavorare con le stringhe di testo e l'SQL del DB2 può Read more

DB2 for i & SQL – FAQ & Howto (Part. 1) (IT)

Database DB2 e SQL ... forse lo strumento più potente e completo che abbiamo sulla piattaforma IBM i: ecco una Read more

Annuncio IBM i 7.4

Arriva direttamente con l'uovo di Pasqua questo annuncio IBM per le novità della versione IBM i 7.4, versione iNext secondo Read more

Generated Always Columns – Approfondimenti (IT)

Introduzione "Generated Always Column": sono colonne, campi, di una tabella il cui contenuto è controllato direttamente dal sistema ... e Read more

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *