DB2 - Sequences

Seguramente todos conocemos la posibilidad de definir una columna de una tabla con IDENTITY, para que de esa forma se nos autogeneren valores y no tengamos que preocuparnos de realizar el típico SELECT MAX. Otro método un poco más desconocido para realizar esto son las SEQUENCES.

Una SEQUENCE es un objeto almacenado que permite generar una secuencia de números en forma ascendente o descendente. Aunque hay similitudes con una columna IDENTITY, la principal diferencia es que mientras la columna IDENTITY se define dentro del ámbito de una tabla, una SEQUENCE no está ligada a una sola tabla, por lo que puede ser usada desde varias. Esto permite usar la SEQUENCE para generar una clave primaria y coordinarla entre registros de distintas tablas.

Las principales características de una secuencia son:

• Garantiza valores únicos.
• Genera valores de forma creciente o decreciente dentro de un rango.
• Puede incrementar valores en más de 1 unidad
• Si se produce un error en DB2 la secuencia se reconstruye a partir del log, lo que garantiza que se seguirán generando correctamente los valores tras un fallo.

 Aquí va un ejemplo de cómo crear una secuencia:

CREATE SEQUENCE EJEMPLO_SEQ
       START WITH 1
       INCREMENT BY 1

Indicamos que deseamos crear la secuencia EJEMPLO_SEQ que comenzará en 1 y se incrementará en una unidad.

Ahora, suponiendo que la secuencia no se ha usado todavía, vamos a generar el primer valor y lo usaremos para insertar en la columna ID de la tabla EJEMPLO_TAB1. Tenemos dos opciones, generar el valor en el INSERT o generar el primer valor en una variable y luego utilizarlo en el INSERT.

Ejemplo de generarlo directamente en el INSERT:

INSERT INTO EJEMPLO_TAB1 (ID, NOMBRE)
VALUES (NEXT VALUE FOR EJEMPLO_SEQ, 'LOBOC');

Ejemplo de generar el valor sobre una variable y luego usarla en el INSERT:

SELECT NEXT VALUE FOR EJEMPLO_SEQ INTO :VAR_SEQ;

INSERT INTO EJEMPLO_TAB1 (ID, NOMBRE)
VALUES (:VAR_SEQ, 'LOBOC');

Cuando generamos un valor para una secuencia con NEXT VALUE (aunque este se realice en un SELECT) ese valor es consumido, y la próxima vez que se solicite un valor se generará uno nuevo. Esto ocurre incluso cuando se produce un fallo en la sentencia que contiene el NEXT VALUE o cuando se realiza rollback.

Justamente una de las potencias de las secuencias, el que garanticen que un NEXT VALUE siempre genera un nuevo valor bajo cualquier circunstancia podría llegar a ser un inconveniente. Por ejemplo, imaginemos que en un proceso batch generamos un fichero de LOAD donde para cada registro generamos un valor con NEXT VALUE. Si el fichero tiene 100.000 registros, habremos incrementado la secuencia en 100.000. Luego por cualquier problema o error, ese fichero de LOAD no se llega a cargar y se descarta. Esos 100.000 valores ya se habrán consumido y se habrán quedado huérfanos. En ciertos casos, donde sea importante que los números sean correlativos y no existan "huecos" (ej: numeración de las facturas) o donde tengamos el ID bastante ajustado en tamaño y no queremos que se nos consuman más valores de los estrictamente necesarios, quizás el uso de SEQUENCES no sea lo más recomendado.

Para profundizar más en el tema de las SEQUENCES os remitimos al  SQL Reference (SC19-2983-03) de IBM

NOTA: Para conocer las secuencias que ya están definidas podemos usar la siguiente consulta:

SELECT * FROM SYSIBM.SYSSEQUENCES

Fuente: SQL Reference (SC19-2983-03) IBM