Gestion de l’IDENTITY d’une table

Que se passe-t-il si on définit soi-même une zone IDENTITY lors de la mise à jour d’une table ?

Ce n’est évidemment pas la meilleure des idées qu’on puisse avoir, mais parfois dans l’urgence d’une correction de données …

  • Commençons par créer une table de tests avec une zone identité de type bigint :
CREATE TABLE NK.IDENT
(
ID BIGINT GENERATED BY DEFAULT AS IDENTITY (
  START WITH 1  INCREMENT BY 1
  NO MINVALUE  NO MAXVALUE
  NO CYCLE NO ORDER),
 NOM_SQL_ZONE_CHAR FOR COLUMN ZONECHAR CHAR(20) NOT NULL DEFAULT '',
 CONSTRAINT IDENT_PK PRIMARY KEY( ID)
)
RCDFMT RIDENT  ;
RENAME TABLE NK.IDENT TO TESTS_IDENTITY
 FOR SYSTEM NAME IDENT;
  • Les zones qui nous intéressent dans la vue syscolumns de QSYS2  ressemblent à ça :
select column_name,
       is_identity,
       identity_generation,
       identity_minimum,
       identity_maximum 
  from qsys2.syscolumns
 where system_table_name ='IDENT'
   and system_table_schema ='NK'
order by ordinal_position;
  • Alimentation de la table avec quelques enregistrements
 insert into nk.ident (Zonechar) values ('Insert Zone2 #1');
 insert into nk.ident (Zonechar) values ('Insert Zone2 #2');
 insert into nk.ident (Zonechar) values ('Insert Zone2 #3');
 insert into nk.ident (ID, Zonechar) values (DEFAULT, 'Insert Zone2 #4');
 insert into nk.ident (ID, Zonechar) values (DEFAULT, 'Insert Zone2 #5');
 insert into nk.ident (ID, Zonechar) values (DEFAULT, 'Insert Zone2 #6');

On peut ignorer ID ou le renseigner en DEFAULT, la table est alimentée :

select * from nk.ident;
  • Que se passe-t-il si je définis moi-même ID lors d’un insert ?

Si l’identity est déjà occupée par un enregistrement : SQL n’accepte pas l’instruction, il fait ce qu’on lui a demandé ! 

insert into nk.ident (ID, Zonechar) values (1, 'Insert KO');
  • Si je fais des insertions de données dans IDENT en définissant moi-même des ID libres :
insert into nk.ident
 select id+6, trim(zonechar) || ' Cpy' from nk.ident;

Tout semble s’être bien passé :

select * from nk.ident;

Si j’interroge SQL sur la dernière valeur IDENTITY qu’il a géré :

values (IDENTITY_VAL_LOCAL());

Tout semble encore correct.

Mais si je refais une insertion de données en laissant à nouveau SQL gérer l’identity :

insert into nk.ident (ID, Zonechar) values (DEFAULT, 'Insert Zone2 #7');

On consulte la log du travail comme le message d’erreur nous invite :

select message_second_level_text 
  from table(qsys2.joblog_info('111778/QUSER/QZDASOINIT')) 
 where message_id = 'CPF5009';

Deux enregistrements sont trouvés :

&N Cause . . . . . :   L’opération d’écriture ou de mise à jour dans le membre numéro 1 (enregistrement numéro 0, format RIDENT) pour le membre IDENT du fichier IDENT, se trouvant dans NK, n’a pas abouti.Le membre numéro 1 (enregistrement numéro 1, format RIDENT) a la même clé d’enregistrement que le membre numéro 1 (enregistrement numéro 0, format RIDENT). Si ce numéro d’enregistrement est 0, la clé d’enregistrement en double a été créée lors d’une opération d’écriture.

&N Que faire . . . : Modifiez les clés en double, de sorte que chaque enregistrement ait une clé unique. Renouvelez la demande.

&N Cause . . . . . :   L’opération d’écriture ou de mise à jour dans le membre numéro 1 (enregistrement numéro 0, format RIDENT) pour le membre IDENT du fichier IDENT, se trouvant dans NK, n’a pas abouti. Le membre numéro 1 (enregistrement numéro 7, format RIDENT) a la même clé d’enregistrement que le membre numéro 1 (enregistrement numéro 0, format RIDENT). Si ce numéro d’enregistrement est 0, la clé d’enregistrement en double a été créée lors d’une opération d’écriture.

&N Que faire . . . : Modifiez les clés en double, de sorte que chaque enregistrement ait une clé unique. Renouvelez la demande.

Le premier message est relatif à la tentative d’insertion « insert into nk.ident (ID, Zonechar) values (1, ‘Insert KO’); » tentée plus haut et pour laquelle l’erreur était attendue.

Le second message est relatif à «insert into nk.ident (ID, Zonechar) values (DEFAULT, ‘Insert Zone2 #7’); »

SQL a tenté d’utiliser la valeur suivante de la dernière identity qu’il a lui-même géré, mais a échoué car la valeur IDENT.ID=7 existe déjà.

Si on retente l’insertion qui vient juste d’échouer :

insert into nk.ident (ID, Zonechar) values (DEFAULT, 'Insert Zone2 #7');

Elle échoue de la même façon, sauf que cette fois SQL a tenté d’utiliser l’ID = 8 :

&N Cause . . . . . :   L’opération d’écriture ou de mise à jour dans le membre numéro 1 (enregistrement numéro 0, format RIDENT) pour le membre IDENT du fichier IDENT, se trouvant dans NK, n’a pas abouti. Le membre numéro 1 (enregistrement numéro 8, format RIDENT) a la même clé d’enregistrement que le membre numéro 1 (enregistrement numéro 0, format RIDENT). Si ce numéro d’enregistrement est 0, la clé d’enregistrement en double a été créée lors d’une opération d’écriture.

&N Que faire . . . : Modifiez les clés en double, de sorte que chaque enregistrement ait une clé unique. Renouvelez la demande.

  • Comment corriger la situation ?

La solution pour se sortir de là si on a fait 3000 insertions ne va pas être de tenter 3000 insertions bidons pour que la table ait son compteur interne gérant l’identity à jour (d’ailleurs, si quelqu’un sait où il se cache je suis preneur).

On récupère la dernière ID utilisée dans la table :

select max(ID) from nk.ident ;

Et on ajoute 1 pour mettre à jour la table :

alter table nk.ident
alter column id restart with 13;

L’instruction précédente :

insert into nk.ident (ID, Zonechar) values (DEFAULT, 'Insert Zone2 #7');

se passe bien maintenant et la numérotation de IDENT.ID a bien repris normalement :

 select * from nk.ident;

Pour se prémunir de tout ceci, il suffit de vérifier la nature de la clé primaire d’une table avant de commencer à y insérer des enregistrements.

Sur DB2 l’usage d’IDENTITY dans une table SQL n’est pas très répandu, il est donc nécessaire de comprendre la structure d’une table avant de l’utiliser. L’IDENTITY se révèle alors pratique tant qu’on laisse le système la gérer. On peut bien sûr, dans des cas exceptionnels, la gérer soi-même si on fait attention…

/par