Generated Always Columns – Approfondimenti (IT)

Index

Toggle

Introduzione

“Generated Always Column”: sono colonne, campi, di una tabella il cui contenuto è controllato direttamente dal sistema … e l’utente non ha possibilità di intervenire o modificare tali valori.

E’ una regola quindi nettamente più forte del “DEFAULT VALUE” … che invece setta un valore di default in mancanza del valore “utente”.

Sono molto comode per i processi di Auditing perché è direttamente il DBMS che si preoccupa di gestirne il contenuto, ad esempio un id autoincrementale, o un timestamp di modifica o, ancora, l’utente o l’applicazione che effetta la modifica su un record del database. Come vedremo più avanti in questo articolo, non danno delle vere certezze perchè con adeguati accorgimenti possiamo forzarne il valore.

La definizione di queste colonne e di questi automatismi possono essere fatti solo generando la tabella con SQL Create table e non con le vecchie DDS… tecnica di definizione della tabelle che dovremmo scordarci e passare tutti direttamente a SQL… ci sono solo vantaggi!

Vediamo due casi tipici di utilizzo delle Generated Always Colums

ID (Identity)

E’ buona regola che ogni tabella abbia un proprio campo con un ID univoco del record: questo permette di gestire meglio il record stesso anche nei casi tipici in cui abbiamo a che fare con chiavi multiple come, ad esempio, un tabella con i listini, dove la chiave pootrebbe essre Cliente-Articolo-DataInizioValidità-DataFineValidità. In questi casi un campo ID permette di gestire meglio modifiche e cancellazioni di eventuli record del listino stesso.

CREATE OR REPLACE TABLE faq400.pricelist (
id            Integer     Generated always 
                         as Identity  primary key,
ItemId       char(15) not null default '',
CustomerId   char(15)  not NULL default '',
FromDate     date not null default current date,
ToDate       date not null default current date,
Price        decimal(21, 4) not null default 0,
Discount1    decimal(11, 3) not null default 0,
Discount2    decimal(11, 3) not null default 0);

Il campo id della tabella faq400.pricelist sopra descritta è Generated Always, Identity e Primary Key della tabella: Identity non è altro che auto-increment … non è necessariamente una chiave univoca, Primary Key identifica invece che è una chiave univoca per i record della tabella… in questo caso particola la colonna Id è Identity e Primary Key contemporaneamente. Il campo id viene generato automaticamente dal sistema all’inserimento di nuovi records.

Quando facciamo un insert in una tabella il campo ID non deve essere specificato oppure deve essere specificato con un valore Default :

insert into faq400.pricelist
 (itemId, CustomerId, FromDate, ToDate, Price)
 values('ABC001', '000101', current date, current date + 1 month, 230.00);

insert into faq400.pricelist
 (id, itemId, CustomerId, FromDate, ToDate, Price)
 values(default, 'ABC002', '000101', current date, current date + 1 month, 250.00);

Timestamp (insert & update)

Un altro utilizzo molto interessante dei generated always columns è per la gestione della “data-Ora ultima modifica” del record, utilissima per l’audit e per i vari controlli e log. Nell’esempio che segue modifico (CREATE or REPLACE) la tabella dei listini con due nuove colonne, una con il TimeStamp del momento di creazione del record … e una con il TimeStamp del momento di modifica di un record. Solo questa seconda è Generated Always … la colonna con il timestamp di creazione è solo per default … se volessimo inserire un record con un timestamp forzato è comunque possibile.

CREATE OR REPLACE TABLE faq400.pricelist (
 id            Integer     Generated always 
                          as Identity  primary key,
 ItemId       char(15) not null default '',
 CustomerId   char(15)  not NULL default '',
 FromDate     date not null default current date,
 ToDate       date not null default current date,
 Price        decimal(21, 4) not null default 0,
 Discount1    decimal(11, 3) not null default 0,
 Discount2    decimal(11, 3) not null default 0,
 audit_TSinsert     Timestamp not null default current timestamp ,                              
 audit_TSupdate     Timestamp Generated always for each row on update
                    as row change Timestamp
                    not NULL
 ) ;

Se aggiungiamo una nuova riga e modifichiamo una riga esistente possiamo vedere gli effetti sulle due nuove colonne:

insert into faq400.pricelist
 (id, itemId, CustomerId, FromDate, ToDate, Price)
 values(default, 'ABC003', '000101', current date, current date + 1 month, 290.00);

 update faq400.pricelist
  set Price=200.00
  where id=1;

Insert SQL e Overriding System Value & Overriding User Value

Quando abbiamo delle colonne con dei valori di default … oppure con dei valori “generated always” potrebbe comunque essere utile mantenere dei valori di un record anche in caso di copia record tra tabelle (o dentro dentro la stessa tabella!).

Per gestire correttamente i valori delle colonne di Default e/o Generated Always bisogna indicare “Overriding System Value” oppure “Overriding User Value” a seconda che si vogliano sovrascrivere tutti i valori default e generated always (System) oppure solo quelli di default (user).

Vediamo un esempio che lo rende più chiaro:

 -- Aggiungiamo una colonna con nota
 CREATE OR REPLACE TABLE faq400.pricelist (
 id            Integer     Generated always 
                          as Identity  primary key,
 ItemId       char(15) not null default '',
 CustomerId   char(15)  not NULL default '',
 FromDate     date not null default current date,
 ToDate       date not null default current date,
 Price        decimal(21, 4) not null default 0,
 Discount1    decimal(11, 3) not null default 0,
 Discount2    decimal(11, 3) not null default 0,
 Nota         char(15) not null default '',
 audit_TSinsert     Timestamp not null default current timestamp ,                              
 audit_TSupdate     Timestamp Generated always for each row on update
                    as row change Timestamp
                    not NULL
 ) ;
 -- Caso 01 Duplichiamo un record ignorando i timestamp
 insert into faq400.pricelist
 (ItemId, CustomerId, FromDate, ToDate, Price, nota)
 (select ItemId, CustomerId, FromDate, ToDate, Price, 'Caso 01'
  from faq400.pricelist
   where Id=1);

 -- Caso 02 Duplichiamo un record  considerando i TimeStamp
 --         ma "forzando" solo i default utente con quelli del recor origine
 insert into faq400.pricelist
 (ItemId, CustomerId, FromDate, ToDate, Price, nota,audit_TSinsert,audit_TSUpdate )
 overriding user value
 (select ItemId, CustomerId, FromDate, ToDate, Price, 'Caso 02', audit_TSinsert, audit_TSUpdate
  from faq400.pricelist
   where Id=1);

 -- Caso 03 Duplichiamo un record considerando i TimeStamp
 --         "forzando" nel nuovo record i valori originali, anche se Generated Always
 insert into faq400.pricelist
 (ItemId, CustomerId, FromDate, ToDate, Price, nota,audit_TSinsert,audit_TSUpdate )
 overriding system value
 (select ItemId, CustomerId, FromDate, ToDate, Price, 'Caso 03', audit_TSinsert, audit_TSUpdate
  from faq400.pricelist
   where Id=1);  

 -- Vediamo il risultato
 select * from faq400.pricelist 
 where ItemID='ABC001';

Come vediamo sopra … la colonna AUDIT_TSUPDATE, generated alwasy … viene mantenuta uguale a quella originale solo in caso di Overriding System Values .., la colonna AUDIT_TSINSERT che ha solo un valore di default e non un “generated always” viene mantenuta uguale a quella originale sia nel caso di Overriding System Value che in quello di Overriding User Value.

Update SQL e Overriding System Value – Overriding User Value

Possiamo usare la tecnica dell’Overriding anche per gli update … senza specificare l’overriding non si potrebbero comunque forzare dei valori delle colonne generated always … ottenendo comunque anche un messaggio di errore di “tentativo di modificare una colonna Generated Always”: vediamo sempre i due esempi che seguono dove nel caso U01 tentiamo un update impossibile … e nel caso U02 tentiamo un update che comunque non “vince” sul valore generated always della colonna di Update:

--- Caso U01 UPDATE ... senza specificare l'overring non è possibile!
 update faq400.pricelist
   set price=201, audit_tsupdate=current timestamp -1 day
   where id=1;
 --  Stato SQL: 428C9 Codice fornitore: -798 Messaggio: 
 --  [SQL0798] Impossibile specificare il valore per la colonna GENERATED ALWAYS

  
 --- Caso U02 UPDATE con OVERRIDING USER VALUE
 update faq400.pricelist
   overriding user value
   set price=201, 
   audit_tsupdate=current timestamp -1 days,
   audit_tsinsert=current timestamp -2 days
   where id=1;

-- Vediamo il risultato dove si vede che la colonna di Update non ha preso il valore desiderato ma quello "forzato" dal sistema
 select * from faq400.pricelist 
 where ItemID='ABC001';

Se usiamo Overriding System Value possiamo comunque forzare il valoe di una colonna Generated Always a nostro piacimento…e questa cosa piacerà soprattutto a noi Italiani … sì, c’è la regola del Generated Always … ma mio cugino sa come fare per superarla … e se c’è l’audit sistemiamo tutto il giorno prima!

--- Caso U03 UPDATE con OVERRIDING SYSTEM VALUE
 update faq400.pricelist
   overriding system value
   set price=202, 
   audit_tsupdate=current timestamp -10 days,
   audit_tsinsert=current timestamp -20 days
   where id=1;

 select * from faq400.pricelist 
 where ItemID='ABC001';

SQL Embedded in RPG

Tutti quelli abituati a lavorare con l’I/O nativo di RPG (Chain, Update, Write, Delete) preferiscono generalmente utilizzare delle DS con la definizione dei campi della tabella e inserire o aggiornare record in SQL appoggiandosi a queste DS invece di dichiarare tutti i campi della tabella in aggiornamento.

Le DS (Data Structures definite con la keyword EXTNAME(‘TABELLA’) sono indubbiamente molto comode in RPG ma portano con sè anche tutti i limiti in caso di modifiche del tracciato record di una tabella (costringendo spesso a ricompilare i programmi che le utilizzano in caso di ALTER TABLE o CREATE or REPLACE table che aggiungono o tolgono colonne alla tabella). Anche con le colonne Generated Always creano qualche problema… l’ideale sarebbe sempre utilizzare l’I/O con SQL segliendo solo le colonne interessate ed evitare come la peste il SELECT * FROM TABELLA.

Vediamo negli esempi che seguono un po’ di trucchi per aggirare i problemi delle colonne Generated Always con le le DS Data Structure in RPG

  //***********************************************************
  //  F4PRICELIS: How to deal with Generated Always Columns
  //            from RPG SQL Embedded
  //
  //**********************************************************
   ctl-opt option(*srcstmt:*nodebugio);


   // DS Definition: pay attention to;
   //   "alias" for long field name
   //   "qualified"
   dcl-ds myds1 extname('PRICELIST') qualified alias end-ds;

   // Null_ds
   dcl-s null_ind int(5);
   dcl-ds null_dsds;
          null_ds dim(200) like(null_ind);
   end-ds;



   // CASO R01
   // In questo caso eseguiamo una INSERT nella tabella
   // specificando le singole colonne... se abbiamo colonne
   // generated always o cambia il tracciato della tabella
   // stessa riduciamo al minimo i problemi
   // (Eng) We are adding a record to our table
   // with an INSERT SQL with single Colums and their values
   // No problem with Generated Always Column or
   // change to the our table's structure

   exec sql
     insert into PRICELIST
     (itemId, CustomerId, FromDate, ToDate, Price)
      values
     ('RPG001', '000101', current date, current date + 1 month, 230.00);




   // CASO R02
   // Anche in caso di update, definendo le colonne coinvolte nell'update
   // non dovremmo aver problemi
   // (Eng) Updating our table selecting all interested columns
   //       we should have no problems with generated always columns
   //       or changes to table's structure

   exec sql
     update PRICELIST
       set price=231
       where ItemId='RPG001';


   // CASO R03
   // Quando la tabella contiene un numero importante di colonne
   // e tutte o tante sono coinvolte nel nostro processo di
   // insert/update l'utilizzo di una DS (definita con EXTNAME)
   // rende le cose più facili (ma un po' più rischiose in caso
   // di modifica).
   // Se abbiamo colonne Generated Alwasy dobbiamo indicare
   // OVERRIDING SYSTEM/USER VALUE per evitare l'errore di
   // precompilazione SQL0798
   // (Eng) When we have an important number of colums end
   //       we have to update or insert a lot of them
   //       Data Structure DS (extname) seems to be interesting
   //       but pay attention to Generated Always Columns or
   //       changes to table structure...
   //       We have to use OVERRIDING USER/SYSTEM VALUE
   //       to prevent SQL0798 precompiling error.

   clear myds1;
   myds1.ItemId='RPG002';
   myds1.CustomerId='000100';
   myds1.FromDate=%date();
   myds1.ToDate=%date+%days(30);
   myds1.price=100;
   myds1.audit_tsInsert=%timestamp();   // If we didn't set this value
                                        // will be 01-01-0001 ...

   exec sql
     insert into pricelist
     OVERRIDING USER VALUE
      values(:myds1);

   // STEP04
   // Anche l'aggiornamento di un record è possibile farlo con una DS
   // ma anche in questo caso valgono gli accorgimenti indicati per
   // l'insert (colonne generated always e cambi di definizione della
   // tabella
   // (Eng): We can update record using a DS, but we have to pay
   //        attention to generated always columns and changes
   //        to table's structure.


   clear myds1;
   exec sql
     select * into :myds1
      from pricelist
      where ItemId='RPG002';

   if sqlcod<>100;
       myds1.price=101;
      exec sql
       update pricelist
       overriding user value
        set row=:myds1
        where ItemId='RPG002';
    endif;

   exsr fine;



   //---------------------------
   // fine
   //---------------------------
   begsr fine;
     *inlr = *on;
     return;
   endsr;

Il risultato degli insert e update via RPG SQL con le DS:

Cosa posso inserire in una colonna Generated Always

Se hai almeno una 7.3 di sistema operativo allora puoi utilizzare le Generated Always Columns anche per inserire in modo automatico: utente, job, tipo di operazione e altre interessanti cose, vedi ad esempio, questo ottimo articolo di Simon Hutchinson su RPGPGM.COM:Vedi questo ottimo articolo di Simon Hutchinson su RPGPGM.COM: https://www.rpgpgm.com/2017/01/using-auditing-columns-to-audit-data.html

Conclusione

Le colonne Generated Always sono veramente interessanti per tutte quelle funzioni di Audit o di Log su modifiche e inserimento dei record in tabella … possono essere aggiunte anche a tabelle esistenti (previa modifica da DDS a DDM SQL) e, addirittura, tracciare in modo automatico date, utenti e applicazioni che modificano i record delle nostre tabelle … con un po’ di attenzione, creando apposite viste, anche senza toccare le applicazioni in uso!

Richiedono però qualche attenzione come abbiamo visto nei casi trattati da questo articolo … ma i vantaggi sono sicuramente superiori… per cui … pronti! via! tutti a creare colonne generated alwasy nelle vostre tabelle!

--- Roberto De Pedrini Faq400.com