DB2 12 para z/OS: Dos mejoras SQL altamente requeridas

By Chris Crone posted Jan 24, 2018 12:00 PM

  

Nota del editor: Este artículo fue publicado originalmente en inglés. Con la aprobación del autor, el equipo de IDUG ha creado la traducción que a continuación se presenta.

por: Meg Bernal, Chris Crone, IBM SVL Lab

DB2 12 trae una serie de nuevas mejoras relacionadas con SQL y SQL PL. Dos de las mejoras relacionadas con SQL fueron  solicitadas por muchos clientes, y son las siguientes::

  • “DELETE por partes” y
  • la capacidad de “MERGE de dos tablas”.

En esta primera entrega de este artículo en 2 entregas, comentaremos la nueva sintaxis de la sentencia DELETE que permite a los usuarios eliminar grandes cantidades de filas de una tabla sin impactar gravemente el sistema.

Modificación de “DELETE por partes”

Los administradores de bases de datos (DBA) pueden recibir un requerimiento de negocio para eliminar datos de una tabla (o vista). La solicitud de eliminación puede afectar a muchas filas que cumplen la condición de borrado; y el borrado de una gran cantidad de filas puede ocasionar situaciones de bloqueo con otras aplicaciones o procesos.

Para eliminar los datos, los DBA solicitaron un cambio en la sintaxis de la sentencia SQL existente en lugar de tener que escribir un programa de aplicación. En muchas instalaciones, la implementación de un cambio en la aplicación requeriría que el DBA siga un proceso de control de cambios que a veces puede ser engorroso y lento.

Para mitigar el efecto de bloqueo sobre un gran número de filas, o que se produzca “lock escalation”, DB2 12 ha sido mejorado para permitir la especificación de la cláusula FETCH FIRST N ROWS ONLY en la sentencia DELETE.

Supongamos que el DBA recibe una solicitud para depurar una tabla histórica eliminando todas las entradas que tienen más de cinco años. Para reducir el número de filas bloqueadas en cualquier momento, una sentencia COMMIT se emite después de un número predeterminado de recuperaciones, para mantener la actividad de bloqueo por debajo del número de “lock escalation” y, por lo tanto, manteniendo la tabla disponible para otro proceso.

Antes de DB2 12, un DBA codificaría una transacción autónoma para purgar la tabla histórica de las filas con una marca de tiempo anterior a cinco años, que realizaría lo siguiente:

  1. DECLARE CURSOR con la opción WITH HOLD para acceder las filas
  2. OPEN CURSOR para todas las filas seleccionadas
  3. Realizar un bucle para:
  4. Leer de la tabla
  5. Posicionarse sobre la fila a eliminar y borrarla.
  6. Después de un número N de filas, ejecutar una sentencia COMMIT para confirmar el borrado de datos y liberar los bloqueos.

Una transacción autónoma puede ser una opción atractiva porque la transacción autónoma puede emitir COMMIT. Mientras que otros procedimientos almacenados solo emiten COMMIT ON RETURN al final de su ejecución, un Procedimiento SQL autónomo puede emitir COMMIT dentro del cuerpo del procedimiento.

Además, el DBA desplegaría la transacción autónoma pasando por el proceso de control de cambios sólo una vez. Después del despliegue inicial de la transacción autónoma, podría emplearse en el borrado de datos de diferentes tablas simplemente llamando a la transacción autónoma con una instrucción SELECT diferente.

 En este ejemplo de “borrado posicionado”, los siguientes cuatro (4) parámetros se pasan a la transacción autónoma:

  1. P_SELECT_STMT - Una instrucción SELECT que contiene un predicado que califica las filas con marcas de tiempo anteriores a cinco años
  2. P_TABLE_NAME - El nombre de la tabla de la tabla que tiene las filas a borrar.
  3. P_NUMER_OF_LOOPS: el número total de filas que tienen marcas de tiempo anteriores a cinco (5) años
  4. P_COMMIT_INTERVAL: el número de recuperaciones que se deben realizar antes de emitir la instrucción COMMIT

La figura siguiente muestra la solución de “borrado posicionado”: 

CREATE PROCEDURE PURGE_ROWS_SP(IN P_SELECT_STMT VARCHAR(1000),
                               IN P_TABLE_NAME VARCHAR(128),
                               IN P_NUMBER_OF_LOOPS INTEGER,
                               IN P_COMMIT_INTERVAL INTEGER)
LANGUAGE SQL
AUTONOMOUS
P1: BEGIN
 
 DECLARE v_DYNAMIC VARCHAR(1000);
 DECLARE v_SQL VARCHAR(1000);
 DECLARE v_DELETE VARCHAR(1000);
 DECLARE v_counter integer DEFAULT 0;
 DECLARE v_temp integer DEFAULT 0;
 DECLARE v_at_end SMALLINT DEFAULT 0;
 DECLARE v_target VARCHAR(100);
 
 DECLARE not_found CONDITION for SQLSTATE '02000';
 DECLARE C1_CUR CURSOR WITH HOLD FOR v_DYNAMIC;
 DECLARE CONTINUE HANDLER for not_found SET v_at_end = 1;
 
 SET v_SQL =  P_SELECT_STMT  || ' FOR UPDATE ';
 SET v_DELETE =
   'DELETE FROM '|| P_TABLE_NAME || ' WHERE CURRENT OF C1_CUR ';
 
 PREPARE v_DYNAMIC FROM v_SQL;
 
 OPEN C1_CUR;
 
 fetch_loop: LOOP
 
   FETCH C1_CUR INTO v_target;
   IF v_at_end <> 0 THEN
     LEAVE fetch_loop;
   END IF;
 
   EXECUTE IMMEDIATE v_DELETE;
 
   SET v_temp = MOD(v_counter,P_COMMIT_INTERVAL);
 
   IF v_temp = 0 THEN
     COMMIT;
   END IF;
 
   SET v_counter = v_counter + 1;
   IF v_counter >= P_NUMBER_OF_LOOPS THEN
     LEAVE fetch_loop;
   END IF;
 
   END LOOP fetch_loop;
 
 CLOSE C1_CUR;
 
END P1!

 

A partir de DB2 12, el desarrollador de aplicaciones puede utilizar la sintaxis de “eliminación por partes” para eliminar N número de filas y emitir un COMMIT para liberar bloqueos. El procedimiento almacenado es mucho más simple de codificar en DB2 12 ya que ya no es necesario declarar un cursor. En la solución de “eliminación por partes”, los cuatro (4) parámetros pasados ​​a la transacción autónoma son siempre los mismos excepto la variable P_SELECT_STATMENT que ahora contiene el predicado que califica filas que tienen marcas de tiempo de más de cinco años.

En DB 12 la transacción autónoma necesitaría sólo hacer lo siguiente:

  1. Realizar un bucle para
    1. Realizar un “eliminación por partes” para borrar un número N de filas
    2. Generar un COMMIT para liberar bloqueos.

La figura que sigue muestra esta solución de “eliminación por partes”: Observe la cláusula FETCH FIRST ROWS ONLY incluída en la sentencia DELETE:

CREATE PROCEDURE FFNR_PURGE_ROWS_SP(IN P_SELECT_STMT VARCHAR(1000),
                                    IN P_TABLE_NAME VARCHAR(128),
                                    IN P_NUMBER_OF_LOOPS INTEGER,
                                    IN P_COMMIT_INTERVAL INTEGER)
LANGUAGE SQL
AUTONOMOUS
P1: BEGIN
 
 DECLARE v_DELETE VARCHAR(1000);
 DECLARE v_counter integer DEFAULT 0;
 
 SET v_DELETE =
'DELETE FROM '|| P_TABLE_NAME || '
 WHERE ' || P_SELECT_STMT
 || ' FETCH FIRST ' || P_COMMIT_INTERVAL ||' ROWS ONLY';
 
 delete_loop: LOOP
 
 EXECUTE IMMEDIATE v_DELETE;
 COMMIT;
 
 SET v_counter = v_counter + P_COMMIT_INTERVAL;
 IF v_counter >= P_NUMBER_OF_LOOPS THEN
 LEAVE delete_loop;
 END IF;
 
 END LOOP delete_loop;
 
END P1!

Como puede ver, DB2 12 simplifica grandemente el mantenimiento de información cuando se requiere borrado de datos. Haciendo uso del soporte de transacción autónoma en DB2 11, es relativamente simple ELIMINAR datos evitando problemas de concurrencia con otras aplicaciones que pueden necesitar acceder a la misma tabla.  

En la segunda entrega de este post, exploraremos la otra frecuentemente solicitada mejora de la sentencia MERGE


#espanol
0 comments
4 views

Permalink