Qué es una secuencia SQL y cómo implementarla en el DB2 de AS400

Detalles

Escrito por Nora Salmún

Utilice los objetos "Secuencia SQL" (SQL Sequence) para generar en DB2 series incrementales de valores numéricos enteros, optimizando de esta forma el código de su aplicación. Le enseñamos cómo crear una "Secuencia SQL" fácil y rápidamente desde iSeries Navigator y cómo referenciarla posteriormente.

Asimismo, las aplicaciones, al usar Secuencias pueden eliminar posibles problemas de concurrencia y performance que podrían surgir al generar un contador único por fuera de la base de datos.

A diferencia de utilizar otro recurso de base de datos disponible para la generación de valores automáticos (por ejemplo, el atributo "columna de identidad" - ver apartado con algunas diferencias más abajo), una Secuencia no está "atada" a una columna de una tabla en particular y se accede a ella de manera separada,  debido a que la Secuencia es un objeto independiente de cualquier tabla. Por lo tanto, se puede usar una Secuencia para reemplazar el uso de una "columna de identidad" (o algún otro recurso creado por el usuario para generar automáticamente valores clave), otorgando además más funcionalidad.

Para crear el  objeto Secuencia se puede usar:

• la sentencia SQL CREATE SEQUENCE directamente en un script SQL (si está familiarizado con su sintaxis),
• o utilizando la interfaz gráfica iSeries Navigator, de manera asistida.

En ambos casos, luego de crear un objeto Secuencia,  al visualizar la biblioteca donde se creó, se encontrará un nuevo objeto en el almacenamiento,  de tipo *DTAARA (area de datos) que representa la Secuencia. No hay un objeto "especial" de tipo "Secuencia", sino un Area de Datos con el nombre de la Secuencia.. Si bien está representada por un Area de datos, no es conveniente utilizar el comando CHGDTAARA (Cambiar area de datos) para realizar cambios sobre la Secuencia creada, debido a podrían obtenerse fallas o resultados inesperados al hacer referencia posteriormente a la Secuencia mediante sentencias SQL. Si se desea modificar la Secuencia, se lo hará posteriomente con comandos SQL apropiados.

Objeto Secuencia SQL: alternativas para su creación

Como mencionáramos anteriormente, las Secuencias son objetos independientes de cualquier tabla, y necesitan ser creadas como cualquier objeto SQL (tabla, vista, indice, etc.)

Para crear una Secuencia SQL, si se conoce bien su sintaxis, se puede usar directamente la sentencia SQL CREATE SEQUENCE en un script SQL especificando los parámetros necesarios (Recomendable la lectura del tip en Teknodatips: Creación y ejecución de un script SQL desde Windows usando iSeries Navigator), o una alternativa más conveniente y práctica es utilizar la interfaz gráfica iSeries Navigator, que guiado por el asistente, permitirá crear fácilmente una Secuencia SQL, evitando la necesidad de conocer la sintaxis de la sentencia SQL subyacente.

Usando directamente la sentencia SQL CREATE SEQUENCE

En el presente tip no se realizará un análisis detallado de la sintaxis y consideraciones más avanzadas de la sentencia CREATE SEQUENCE, sino que ejemplificaremos su creación y una posible manera de referirse a la misma para ver su comportamiento.

El siguiente ejemplo usa el comando CREATE SEQUENCE para crear en SQL un objeto Secuencia que puede ser incorporado en un Script SQL.:

CREATE SEQUENCE TEKDB2.NROEMPL AS INTEGER

START WITH 1000

INCREMENT BY 10

NO ORDER

NO CYCLE

NO MINVALUE

NO MAXVALUE

CACHE 20;

El código expresado arriba, crea una Secuencia SQL NROEMPL  en la  biblioteca TEKDB2 donde el primer valor asignado a la columna en donde se haga referencia a la secuencia, será 1000 y luego se incrementará en cada uso en 10 para generar el próximo Nro de empleado en la secuencia.

La referencia a la secuencia NROEMPL creada como objeto en la biblioteca TEKDB2 se verá en el apartado siguiente, utilizando el iSeries Navigator.

Creando una Secuencia usando iSeries Navigator

Abordaremos la creación de la Secuencia ejemplo NroEmpl en la biblioteca TEKDB2 mediante la interfaz gráfica iSeries Navigator, para luego insertar también a modo de ejemplo dos registros en una tabla EMPLEADOS, referenciando en la sentencia INSERT a la Secuencia SQL creada y así comprobar cómo se generaron automáticamente en la tabla EMPLEADOS los nros. para cada empleado, de acuerdo a la especificación realizada en la secuencia que se creó.

Desde iSeries Navigator: Abrir Conexión → Elegir conexión AS400 determinada →Base de Datos → Esquema (Biblioteca) donde crear la secuencia → Secuencia y Botón derecho del mouse → Nuevo → Secuencia:

En la pantallaque se presenta, completar los datos requeridos para la secuencia a crear, y si se desea Mostrar la sentencia SQL asociada a esa creación, presionar el botón correspondiente como se muestra en la siguiente pantalla, que detallará el código completo de  la sentencia CREATE SEQUENCE relacionada. Luego, es necesario presionar Aceptar, para que se cree la Secuencia NroEmpl en la biblioteca TEKDB2.

La sentencia CREATE SEQUENCE asociada, muestra cómo crear el objeto Secuencia que genera valores enteros para un Nro. de empleado comenzando con un valor de 1000 e incrementándolo en 10 para generar el próximo Nro de empleado en la secuencia:

Una vez creada la Secuencia SQL se corresponderá con la creación de un objeto en el almacenamiento en el AS400 de tipo *DTAARA que la representa.

Referencia y uso de un objeto Secuencia

Una vez que la Secuencia NroEmpl en TEKDB2 fue creada, veremos cómo se comporta la secuencia. Por ejemplo al insertar valores a una columna de una tabla, usando esa secuencia creada:

Como ejemplo: para insertar el próximo valor de la secuencia NroEmpl en la tabla EMPLEADOS que tiene columnas ID_EMPLE y NOM_EMPLE con los valores "próximo empleado en la secuencia" y "Rosa María", respectivamente, ejecutar lo siguiente:

INSERT INTO TEKDB2.EMPLEADOS (ID_EMPLE, NOM_EMPLE)

VALUES (NEXT VALUE FOR TEKDB2.NroEmpl, 'Rosa María')

Si posteriormente se realiza otra inserción sobre la tabla EMPLEADOS:

INSERT INTO TEKDB2.EMPLEADOS (ID_EMPLE, NOM_EMPLE)

VALUES (NEXT VALUE FOR TEKDB2.NroEmpl, 'Juan José')

Luego, al visualizar el contenido de la tabla Empleados (ejecución de la sentencia SELECT * FROM TEKDB2.EMPLEADOS), se verá lo siguiente:

donde en la columna ID_EMPLE se insertaron automáticamente los nros. de empleados referenciados en la secuencia NroEmpl.

Considerar además que si el nro. de empleado necesita ser compartido con otras tablas, puede usarse también la expresión PREVIOUS VALUE.

En el ejemplo de abajo, una nueva fila en la tabla EMPL_HIST de TEKDB2, contendrá el mismo valor del campo ID_EMPLE que fue usado en la sentencia de inserción anterior sobre la tabla EMPLEADOS, con la descripción "Desarrollador RPG".

INSERT INTO tekdb2.EMPL_HIST

VALUES (PREVIOUS VALUE FOR TEKDB2.NroEmpl, ‘'Desarrollador RPG’ )

También es posible usar la sentencia UPDATE que refrencie a la secuencia para cambiar un valor determinado de una columna:

UPDATE teklib.Ejem_Tabla

SET Cuenta = NEXT VALUE FOR  Secuencia_Ejemplo

WHERE Cuenta = 0;

Cambio de una Secuencia

Es posible, además, cambiar la Secuencia creada, ejecutando la sentencia  ALTER SEQUENCE o utilizando el iSeries Navigator como se muestra más abajo.

Se puede usar la sentencia ALTER SEQUENCE  para cambiar una secuencia en cualquiera de los siguientes maneras:

Rearrancar la secuencia.

Cambiar el incremento entre los valores de secuencia futuros

Especificar o eliminar los valores mínimo o máximo

Cambiar el número de números de secuencia en cache.

Cambiar el atributo que determina si la secuencia puede ciclar o no

Cambiar si los números de secuencia deben ser generados en orden de requerimiento o no

Ejemplo 1:

ALTER SEQUENCE NroEmpl  RESTART

La sentencia ALTER SEQUENCE anterior, sólo "resetea" la secuencia a su estado original.

Ejemplo 2:

ALTER SEQUENCE TEKDB2.NroEmpl

INCREMENT BY 5

Después de completar el cambio (cambiar el incremento por 5, en vez de 10), ejecutar una sentencia INSERT sobre la tabla EMPLEADOS del ejemplo y volver a visualizar la tabla para ver los nuevos valores.

Si se desea cambiar la secuencia desde iSeries Navigator:

Al seleccionar Secuencias sobre el panel de la izquierda, se visualizarán todas las secuencias que han sido definidas sobre ese esquema (Biblitoeca).

Sobre la secuencia que quiera cambiar, presionar botón derecho del mouse y elegir Definicion. Al hacer click, se presenta la pantalla que permite cambiar los valores de la secuencia. Luego, presionando Aceptar, se contará ya con los cambios establecidos sobre la misma:

Notar que también es posible Generar el SQL correspondiente a esa definición para recrear posteriormente esa secuencia o realizar algunos cambios.

Algunas diferencias entre "columna de identidad" y Secuencia SQL

Si bien las columnas de identidad y las secuencias son similares en muchos aspectos (ambas generan automáticamente valores numéricos en secuencia), también existen diferencias entre ellas; por lo tanto, considerar esas diferencias para ayudar a decidir cuál usar en un caso determinado.

Características de una "Columna de identidad" (Identity Column):

• Una columna de identidad puede definirse en el momento de crear una tabla (como parte de la tabla) o puede ser agregada a una columna de la tabla posteriormente, usando la sentencia ALTER TABLE. Después de haber creado una tabla, se pueden cambiar las características de una columna de identidad.
• Una columna de identidad genera automáticamente valores para una única tabla, es decir, es dependiente de la tabla donde se define.
• Cuando una columna de identidad está definida como GENERATED ALWAYS, los valores usados siempre son generados por el manejador de Base de datos.  No es posible que las aplicaciones provean sus propios valores cuando cambian los contenidos de una tabla.
• Se puede usar la función IDENTITY_VAL_LOCAL para visualizar el último valor que fue asignado a una columna de identidad.

Características de una Secuencia SQL:

• Una secuencia es un objeto del sistema de tipo *DTAARA que es independiente de cualquier tabla.
• Una secuencia genera valores secuenciales que pueden ser usados en cualquier sentencia SQL.
• Los valores generados por un objeto secuencia pueden ser accedidos sólamente mediante expresiones determinadas y existen dos expresiones usadas para recuperar los valores siguientes en la secuencia y visualizar el valor anterior asignado para esa secuencia. La expresión PREVIOUS VALUE devuelve el último valor generado para la secuencia especificada usado por una sentencia dentro de la sesión corriente. La expresión NEXT VALUE devuelve el próximo valor para secuencia especificada. Al usar estas expresiones, permite que se use el mismo valor en varias sentencias SQL y dentro de diferentes tablas. Las palabras clave NEXTVAL y PREVVAL pueden ser usados como alternativa a las expresiones NEXT VALUE y PREVIOUS VALUE respectivamente.

Si bien las expresadas no son todas las características que tienen las columnas de identidad y las secuencias, conocer estas características puede ayudar a determinar qué usar, dependiendo del diseño de la base de datos y de las aplicaciones que la usarán.

Para tener en cuenta ...

• Los objetos Secuencia ayudan a eliminar los posibles problemas de concurrencia o bloqueo que sí se pueden presentar al usar otras técnicas para generar valores enteros únicos secuenciales como lo que se usó en este tip. Los valores generados por un objeto secuencia pueden ser accedidos sólamente mediante expresiones determinadas
• Se necesita poseer  la autorización *USE sobre el comando CRTDTAARA (Crear Area de Datos) para crear el objeto *DTAARA que representa a la secuencia.
• El propietario del objeto Secuencia creada es el perfil de usuario del trabajo que ejecutó la sentencia de creación.
• En IBM i 7.1: Existe una nueva cláusula REPLACE, que permite reemplazar la definición para la secuencia si ya existe una en el servidor corriente al momento de crearla, sin necesidad de eliminar la ya existente. La definición existente es efectivamente eliminada antes de que la nueva definición para la secuencia sea reemplazada en la tabla de catálogo SYSSEQOBJECTS, con la excepción de que los privilegios que fueron otorgados sobre la secuencia no se vean afectados.  Esta opción será ignorada si no existe en el servidor corriente una definición para la secuencia.

Copyright 2012 Teknoda S.A.