Diferencias entre SQL estático y dinámico

El SQL estático y el SQL dinámico son adecuados para distintas circunstancias. Es necesario tener en cuenta las diferencias entre los dos a la hora de determinar si a la aplicación le conviene más el SQL estático o el SQL dinámico.

Flexibilidad de SQL estático con variables de host

Conceptos introductorios

Cuando utiliza SQL estático, no puede cambiar la forma de las sentencias de SQL a menos que realice cambios en el programa. Sin embargo, puede aumentar la flexibilidad de las sentencias estáticas utilizando variables de host.

Ejemplo : En el siguiente ejemplo, la instrucción UPDATE puede actualizar el salario de cualquier empleado. En el momento de la vinculación, sabes que los salarios deben actualizarse, pero no sabes hasta el momento de la ejecución qué salarios deben actualizarse y en qué cuantía.

01  IOAREA.
    02  EMPID              PIC X(06).
    02  NEW-SALARY         PIC S9(7)V9(2) COMP-3.
⋮ (Other declarations)
READ CARDIN RECORD INTO IOAREA
  AT END MOVE 'N' TO INPUT-SWITCH.
⋮ (Other COBOL statements)
EXEC SQL
  UPDATE DSN8C10.EMP
    SET SALARY = :NEW-SALARY
    WHERE EMPNO = :EMPID
END-EXEC.

La sentencia (UPDATE) no cambia, ni tampoco su estructura básica, pero la entrada puede cambiar los resultados de la sentencia UPDATE.

Flexibilidad de SQL dinámico

¿Qué ocurre si un programa debe utilizar diferentes tipos y estructuras de sentencias SQL? Si hay tantos tipos y estructuras que no puede contener un modelo de cada uno, su programa podría necesitar SQL dinámico.

Puede utilizar uno de los siguientes programas para ejecutar SQL dinámico:

Db2 Query Management Facility (QMF): Proporciona una interfaz alternativa a Db2 que acepta casi cualquier instrucción SQL
SPUFI: Acepta instrucciones SQL de un conjunto de datos de entrada y, a continuación, las procesa y ejecuta dinámicamente
Db2 command line processor: Acepta instrucciones SQL de un entorno de servicios del sistema UNIX.

Limitaciones de SQL dinámico

No puede utilizar algunas de las sentencias SQL de forma dinámica.

Procesamiento dinámico de SQL

Un programa que proporciona SQL dinámico acepta como entrada, o genera, una instrucción SQL en forma de cadena de caracteres. Puede simplificar la programación si planifica el programa para no utilizar sentencias SELECT, o para utilizar solo aquellas que devuelvan un número conocido de valores de tipos conocidos. En el caso más general, en el que no se conocen de antemano las sentencias SQL que se ejecutarán, el programa suele seguir estos pasos:

Traduce los datos de entrada, incluidos los marcadores de parámetros, en una instrucción SQL
Prepara la instrucción SQL para su ejecución y adquiere una descripción de la tabla de resultados
Obtiene, para las sentencias SELECT, suficiente almacenamiento principal para contener los datos recuperados
Ejecuta la instrucción o recupera las filas de datos
Procesa la información devuelta
Maneja códigos de retorno de SQL.

Ejecución de SQL estático y dinámico

Para acceder a datos de Db2 , una instrucción SQL requiere una ruta de acceso. Dos factores importantes en el rendimiento de una instrucción SQL son la cantidad de tiempo que utiliza Db2 para determinar la ruta de acceso en tiempo de ejecución y si la ruta de acceso es eficiente. Db2 determina la ruta de acceso para un extracto en cualquiera de estos momentos:

Cuando vinculas el plan o paquete que contiene la instrucción SQL
Cuando se ejecuta la instrucción SQL

El momento en el que Db2 determina la ruta de acceso depende de estos factores:

Si el estado se ejecuta de forma estática o dinámica
Si el enunciado contiene variables de host de entrada
Si el estado contiene una tabla temporal global declarada.

Instrucciones SQL estáticas sin variables de host de entrada

Para las sentencias SQL estáticas que no contienen variables de host de entrada, Db2 determina la ruta de acceso cuando vinculas el plan o el paquete. Esta combinación ofrece el mejor rendimiento porque la ruta de acceso ya está determinada cuando se ejecuta el programa.

Instrucciones SQL estáticas con variables de host de entrada

Para las sentencias SQL estáticas que tienen variables de host de entrada, el momento en el que Db2 determina la ruta de acceso depende de la opción de enlace REOPT que especifique: REOPT(NONE) o REOPT(ALWAYS). REOPT(NONE) es el valor predeterminado. No especifique REOPT(AUTO) o REOPT(ONCE); estas opciones solo son aplicables a las sentencias dinámicas. Db2 ignora REOPT(ONCE) y REOPT(AUTO) para sentencias SQL estáticas, porque Db2 almacena en caché solo sentencias SQL dinámicas.

Si especifica REOPT(NONE), Db2 determina la ruta de acceso en el momento de la vinculación, tal como lo hace cuando no hay variables de entrada.

Si especifica REOPT(ALWAYS), Db2 determina la ruta de acceso en el momento de la vinculación y de nuevo en el momento de la ejecución, utilizando los valores de los siguientes tipos de variables de entrada:

Variables de host
Marcadores de parámetro
Registros especiales

Db2 debe dedicar tiempo adicional a determinar la ruta de acceso para las declaraciones en tiempo de ejecución. Sin embargo, si Db2 determina una ruta de acceso significativamente mejor utilizando los valores de las variables, es posible que se produzca una mejora general del rendimiento. Con REOPT(ALWAYS), Db2 optimiza las sentencias utilizando valores literales conocidos. Conocer los valores literales puede ayudar a Db2 a elegir una ruta de acceso más eficiente cuando las columnas contienen datos sesgados. Db2 también puede reconocer qué particiones cumplen los requisitos si hay condiciones de búsqueda con variables de host en las claves de límite de los espacios de tabla particionados.

Con REOPT(ALWAYS) Db2 , la optimización no se reinicia desde el principio. Por ejemplo, Db2 no realiza transformaciones de consultas basadas en los valores literales. En consecuencia, las sentencias SQL estáticas que utilizan variables de host optimizadas con REOPT(ALWAYS) y sentencias SQL similares que utilizan valores literales explícitos pueden dar lugar a diferentes rutas de acceso.

Sentencias de SQL dinámico

Para sentencias de SQL dinámico, Db2 determina la vía de acceso en tiempo de ejecución, cuando se prepara la sentencia. El coste repetitivo de preparar una sentencia dinámica puede hacer que el rendimiento sea peor que el de las sentencias SQL estáticas. Sin embargo, si ejecuta la misma instrucción SQL a menudo, puede utilizar la caché de instrucciones dinámicas para disminuir el número de veces que deben prepararse esas instrucciones dinámicas.

Instrucciones SQL dinámicas con variables de host de entrada

Cuando vincule aplicaciones que contengan sentencias SQL dinámicas con variables de host de entrada, considere la posibilidad de utilizar las opciones de vinculación REOPT(ALWAYS), REOPT(ONCE) o REOPT(AUTO), en lugar de la opción REOPT(NONE).

Utilice REOPT(ALWAYS) cuando no esté utilizando la caché de sentencias dinámicas. Db2 determina la ruta de acceso para las sentencias en cada EXECUTE u OPEN de la sentencia. Esta opción garantiza la mejor ruta de acceso para una instrucción, pero el uso de REOPT(ALWAYS) puede aumentar el coste de las instrucciones SQL dinámicas utilizadas con frecuencia.

Por lo tanto, la opción REOPT(ALWAYS) no es una buena elección para consultas de gran volumen en menos de un segundo. Para consultas de gran volumen y ejecución rápida, el coste repetitivo de preparación puede superar el coste de ejecución de la instrucción. Las sentencias que se procesan con la opción REOPT(ALWAYS) se excluyen de la caché de sentencias dinámicas, incluso si el almacenamiento en caché de sentencias dinámicas está habilitado, porque Db2 no puede reutilizar las rutas de acceso cuando se especifica REOPT(ALWAYS).

Utilice REOPT(ONCE) o REOPT(AUTO) cuando utilice la caché de sentencias dinámicas:

Si especifica REOPT(ONCE), Db2 determina y la ruta de acceso para las sentencias solo en el primer EXECUTE u OPEN de la sentencia. Guarda esa ruta de acceso en la caché de sentencias dinámicas y la utiliza hasta que la sentencia se invalida o se elimina de la caché. Esta reutilización de la ruta de acceso reduce el coste de preparación de las sentencias SQL dinámicas de uso frecuente que contienen variables de host de entrada; sin embargo, no tiene en cuenta los cambios en los valores de los marcadores de parámetros para las sentencias dinámicas.
La opción REOPT(ONCE) es ideal para aplicaciones de consulta ad-hoc como SPUFI, DSNTEP2, DSNTEP4, DSNTIAUL y QMF Db2 pueden optimizar mejor las sentencias conociendo los valores literales de registros especiales como CURRENT DATE y CURRENT TIMESTAMP, en lugar de utilizar estimaciones de factores de filtro predeterminados.
Si especifica REOPT(AUTO), Db2 determina la ruta de acceso en tiempo de ejecución. Db2 , genera una nueva ruta de acceso para cada ejecución de una declaración con marcadores de parámetros, si determina que una nueva ruta de acceso puede mejorar el rendimiento.

Codificación de sentencias PREPARE para una optimización eficiente

Debe codificar sus declaraciones PREPARE para minimizar los gastos generales. Con REOPT(AUTO), REOPT(ALWAYS) y REOPT(ONCE), Db2 prepara una instrucción SQL al mismo tiempo que procesa OPEN o EXECUTE para la instrucción. Es decir, Db2 procesa la declaración como si especificara DEFER(PREPARE). Sin embargo, Db2 prepara el estado de cuenta dos veces en las siguientes situaciones:

Su programa emite la sentencia DESCRIBE antes de la sentencia OPEN
Usted emite la sentencia PREPARE con el parámetro INTO

Para la primera preparación, Db2 determina la ruta de acceso sin utilizar valores de variables de entrada. Para la segunda preparación, Db2 utiliza los valores de las variables de entrada para determinar la ruta de acceso. Esta preparación adicional puede disminuir el rendimiento.

Si especifica REOPT(ALWAYS), Db2 prepara el estado dos veces cada vez que se ejecuta.

Si especifica REOPT(ONCE), Db2 prepara el estado de cuenta dos veces solo cuando el estado de cuenta nunca se ha guardado en la memoria caché. Si la instrucción se ha preparado y guardado en la memoria caché, Db2 utilizará la versión guardada de la instrucción para completar la instrucción DESCRIBE.

Si especifica REOPT(AUTO), Db2 prepara inicialmente el estado sin utilizar valores de variables de entrada. Si la declaración se ha guardado en la memoria caché, para el OPEN o EXECUTE posterior, Db2 determina si se necesita una nueva ruta de acceso de acuerdo con los valores de las variables de entrada.

Para una sentencia que utiliza un cursor, puede evitar la doble preparación colocando la sentencia DESCRIBE después de la sentencia OPEN en su programa.

Si utiliza un gobierno predictivo y una instrucción SQL dinámica vinculada con REOPT(ALWAYS) o REOPT(ONCE) supera un umbral de advertencia de gobierno predictivo, su aplicación no recibe un SQLCODE de advertencia. Sin embargo, recibirá un error SQLCODE de la instrucción OPEN o EXECUTE.