Valores de filas afectadas y claves identidad en DB2

Estimad@s,

expongo aquí algunas formas de trabajar con los datos recién insertados en tablas DB2.

Muchas veces es necesario conocer el último id insertado en una columna identity (auto-numérica) o también la última o últimas filas insertadas en una tabla.

Muchos desarrolladores tratan esto con algunas prácticas que no son muy buenas, algunos ejemplos pueden ser ejecutar una consulta del estilo SELECT MAX(id) FROM tabla luego de insertar, SELECT * FROM tabla WHERE descripcion='algún dato unique que se tenía antes de insertar' o almacenar últimos valores de clave en una tabla, esta última la peor de las prácticas.

Existen otros casos, pero casi todos tienen en común que no dan soporte a la concurrencia y que en general son muy ineficientes. DB2 posee una serie de características que permiten lidiar con estos problemas y darles una solución sencilla y elegante, además se tendrá en cuenta la concurrencia y la eficiencia en la ejecución.

Secuencias

Una manera puede ser utilizar secuencias. Las secuencias son objetos de la base de datos que permiten generar números en secuencia (valga aquí la redundancia) a pedido, además permite obtener el último número generado en la secuencia. Veamos un pequeño ejemplo de como crear y utilizar una secuencia.

Creamos la tabla que utilizaremos como ejemplo:

CREATE TABLE EJ_SECUENCIA  (
    id BIGINT NOT NULL, 
    descripcion VARCHAR(50) NOT NULL,
    CONSTRAINT PK_EJEMPLO PRIMARY KEY (id) 
); 

Luego la secuencia seq1 que inicia en 1 e incrementa de 1.

CREATE SEQUENCE seq1 AS INTEGER START WITH 1 INCREMENT BY 1

La sintaxis de las secuencias en DB2 es muy amplia y permite definir muchas opciones como: ciclos, secuencias decrementales, etc.

Las secuencias tienen dos métodos asociados, PREVVAL que permite obtener el último número generado (no puede utilizarse luego de crear la secuencia, debe generarse al menos un valor antes de ejecutar este método) y NEXTVAL que permite generar un nuevo valor de la secuencia. Veamos con un ejemplo como insertar valores en la tabla EJ_SECUENCIA con esta secuencia.

INSERT INTO EJ_SECUENCIA (id, descripcion) VALUES  (NEXTVAL FOR seq1, 'a');
VALUES (PREVVAL FOR seq1);

La salida será:

1
-----------
          1
    1 registro(s) seleccionado(s).

Si ejecutamos ahora:

INSERT INTO EJ_SECUENCIA (id, descripcion) VALUES 
    (NEXTVAL FOR seq1, 'b'),
    (NEXTVAL FOR seq1, 'c'),
    (NEXTVAL FOR seq1, 'd');

Obtendremos:

SELECT * FROM DB2ADMIN.EJ_SECUENCIA
  ID                   DESCRIPCION
-------------------- -------------
                   1 a
                   2 b
                   3 c
                   4 d
    4 registro(s) seleccionado(s).

Como se puede apreciar el uso de las secuencia es muy sencillo y flexible.

Autoincrementales y filas afectadas

Creamos la tabla de ejemplo:

CREATE TABLE EJ_IDS  (
    id BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY, 
    descripcion VARCHAR(10) NOT NULL,
    CONSTRAINT PK_IDS PRIMARY KEY (id)
); 

Insertamos algunos valores:

INSERT INTO EJ_IDS (descripcion) VALUES ('a'),('b'),('c');

Noten que solo es necesario dar valores a la columna descripcion, DB2 se encargará dar valores a la columna id.
 
Luego de consultar la tabla:

SELECT * FROM EJ_IDS

Obtendremos:

ID                   DESCRIPCION
-------------------- ---------------
                   1 a
                   2 b
                   3 c
    3 registro(s) seleccionado(s).

 
Luego ejecutando :

VALUES (IDENTITY_VAL_LOCAL()) 

La salida será:

1

-----------

          3

  1 registro(s) seleccionado(s).

 
Para finalizar una herramienta excelente para obtener las filas afectadas, denominamos filas afectadas a las filas insertadas o que cumplen un predicado y por ello son modificadas o eliminadas.

SELECT * FROM FINAL TABLE (INSERT INTO EJ_IDS (descripcion) VALUES ('e'),('f'),('g'))

La sentencia anterior cumplirá dos funciones, por un lado se insertarán tres nuevas filas y por otro se obtendrán de la consulta a la tabla 'FINAL TABLE'

ID                   DESCRIPCION
-------------------- -----------
                   4 e
                   5 f
                   6 g
    3 registro(s) seleccionado(s).

También podemos utilizar 'FINAL TABLE'  en modificaciones:

SELECT * FROM FINAL TABLE (UPDATE EJ_IDS SET descripcion = descripcion || '-nuevo' WHERE MOD(ID,2)=0)

Obtendremos:

ID                   DESCRIPCION
-------------------- -----------
                   2 b-nuevo
                   4 e-nuevo
                   6 g-nuevo
    3 registro(s) seleccionado(s).

Además de 'FINAL TABLE' podemos utilizar 'NEW TABLE' la diferencia es que con 'NEW TABLE' obtendremos los valores de la tabla antes que se ejecuten las restricciones referenciales (claves foráneas)  los triggers definidos como after:

Para obtener las filas borradas:

SELECT * FROM OLD TABLE (DELETE FROM EJ_IDS WHERE MOD(ID,3)=0)
  ID                   DESCRIPCION
-------------------- -----------
                   3 c
                   6 c-nuevo
    2 registro(s) seleccionado(s).

Espero que les sea útil.

Saludos

Mariano