Colonne ad incremento automatico nel DB2 for IBMi

Per creare una tabella con un campo ad incremento automatico, in MySQL si specifica AUTO_INCREMENT. Esempio:

create table MIATABLE (
Id_Key int(11) NOT NULL AUTO_INCREMENT,
Descrizione varchar(100) not null with default,
PRIMARY KEY ('Id_Key') )

Se devi farlo su un server IBMi (AS/400) che come database di default utilizza il DB2 for IBMi (DB2/400) bisogna utilizzare i campi IDENTITY al posto di AUTO_INCREMENT. Esempio:

create table MIATABLE (
Id_Key int NOT NULL GENERATED ALWAYS AS IDENTITY,
Descrizione varchar(100) not null with default,
PRIMARY KEY (Id_Key) )

In questo caso si otterrà un incremento automatico di 1 ad ogni INSERT. Esempio:

INSERT INTO MIATABLE VALUES(DEFAULT , 'Paolo') ;
INSERT INTO MIATABLE VALUES(DEFAULT , 'Eustachio') ;
INSERT INTO MIATABLE VALUES(DEFAULT , 'Gino') ;

dopo l'esecuzione di queste 3 INSERT si avrà il seguente risultato:

Da notare l'utilizzo della keyword DEFAULT all'interno dei valori dell'istruzione INSERT. E' necessaria per far si che sia il DB a valorizzare il campo Id_Key.

Se l'inserimento avviene a fronte di una SELECT l'istruzione sarà simile alla seguente:

Insert into tabella_01
OVERRIDING user VALUE

select
0,    
col01,
col02,
...
from    tabella_02

Se volessi che l'auto-incremento avesse un passo diverso dovrei specificare anche start with NN, increment by NN nell'istruzione precedente. Esempio:

create table MIATABLE (
Id_Key int NOT NULL GENERATED ALWAYS AS IDENTITY (start with 10, increment by 10),
Descrizione varchar(100) not null with default,
PRIMARY KEY (Id_Key) )

In questo caso Id_Key verrebbe incrementato di 10 ad ogni INSERT partendo dal valore 10 al primo inserimento.

Fino ad ora ho definito il campo IDENTITY come GENERATED ALWAYS ma esiste anche un'altra possibilità di gestire l'auto-incremento: definire la colonna della tabella come GENERATED BY DEFAULT AS IDENTITY. La differenza principale tra BY DEFAULT e ALWAYS è abbastanza intuibile: con ALWAYS è sempre il DB2 ad occuparsi della valorizzazione della colonna e NON permette di forzare il dato in nessun modo. Questo permette al DB2 di garantire l'univocità del valore.

Utilizzando BY DEFAULT il DB2 incrementa il dato solo se non specificato, altrimenti assume quello indicato dall'utente/applicazione. In questo caso il DB2 non garantisce l'univocità che quindi è a carico dell'applicazione.

BY DEFAULT è molto comodo nel caso in cui la tabella debba essere copiata in schemi/librerie diverse perchè permette di mantenere gli stessi valori della tabella originaria mentre se la colonna fosse definita ALWAYS non sarebbe possibile garantirlo.

ATTENZIONE: una considerazione molto importante da fare è che con BY DEFAULT, se l'applicazione forza un valore superiore al massimo già inserito, il DB2 non aggiorna il proprio contatore ma mantiene il precedente valore, quindi i successivi inserimenti di record con valore di default arriveranno a replicare il valore precedentemente forzato dall'applicazione. Se la colonna è anche PRIMARY KEY si otterrà un errore per chiave duplicata.

Per evitare questo tipo di problemi è necessario resettare il contatore della colonna GENERATED BY DEFAULT AS IDENTITY ogni volta che si forza un valore superiore al massimo con il seguente comando:

ALTER TABLE <mia_tabella> ALTER COLUMN <mia_colonna> RESTART WITH <n>

dove <n> dev'esse il MAX() + 1.

Per conoscere il valore di un campo IDENTITY dopo un INSERT bisogna utilizzare la funzione IDENTITY_VAL_LOCAL(). In pratica la funzione IDENTITY_VAL_LOCAL() è per il DB2 for IBMi quello che la funzione LAST_INSERT_ID() è per MySQL o SCOPE_IDENTITY è per MS SQL Server.

Ad esempio, dopo la terza operazione INSERT indicata sopra, è sufficiente eseguire l'istruzione:

select IDENTITY_VAL_LOCAL() from sysibm/sysdummy1

e quindi utilizzare una FETCH per conoscere il valore del campo IDENTITY Id_Key.

Ovviamente è possibile utilizzare la stessa funzione anche all'interno di una Stored Procedure valorizzando una variabile con il valore del campo IDENTITY immediatamente dopo aver eseguito l'operazione INSERT. Ad esempio:

…
INSERT INTO miatable (Descrizione) values('Giacomo');
set My_Key = IDENTITY_VAL_LOCAL();
...

Semplice e molto potente.

by