EXPLAIN sentencia

La sentencia EXPLAIN obtiene información sobre la selección de la ruta de acceso para una sentencia explicable. Inicio del cambio

Una instrucción es explicable si es una instrucción SELECT, MERGE, TRUNCATE o INSERT, o la forma buscada de una instrucción UPDATE o DELETE. fin del cambio

La información que se obtiene se coloca en un conjunto de tablas de usuario suministradas que se denominan tablas EXPLAIN.

Iniciar la información de la interfaz de programación específica del programa.

La tabla de plan contiene información sobre la vía de acceso para la sentencia o sentencias especificadas. La tabla de sentencia se puede cumplimentar con información sobre el coste estimado de ejecución de la sentencia explicable. La tabla de funciones se puede cumplimentar con información sobre cómo Db2 resuelve las funciones definidas por el usuario a las que se hace referencia en la sentencia explicable. Otras tablas de EXPLAIN se pueden cumplimentar con información adicional sobre la ejecución de la sentencia explicable. Para obtener una lista completa de las tablas EXPLAIN, consulte Tablas EXPLAIN.

Utilizando EXPLAIN para consultas que hacen referencia a las tablas temporales de periodos del sistema que están habilitadas para el mantenimiento de versiones de datos del sistema, el resultado mostrará las tablas temporales de periodos del sistema y las tablas de historial en la salida de EXPLAIN si la consulta necesita hacer referencia a ambas tablas para satisfacer la consulta.

Invocación para EXPLAIN

Esta sentencia puede incorporarse en un programa de aplicación o emitirse de forma interactiva. Se trata de una sentencia ejecutable que puede prepararse de forma dinámica.

Autorización para EXPLAIN

Las reglas de autorización son las definidas para la sentencia de SQL especificada en la sentencia EXPLAIN. Por ejemplo, consulte en la descripción de la sentencia DELETE las reglas de autorización que se aplican cuando se capturan los registros de EXPLAIN para las sentencias DELETE.

Si la sentencia EXPLAIN se ha incluido en un programa de aplicación, las reglas de autorización que se aplican son las definidas para incluir la sentencia SQL especificada en un programa de aplicación. Además, el propietario del plan o paquete deberá tener también una de las características siguientes:

Ser el propietario de una tabla de plan denominada PLAN_TABLE
Tener un alias en una tabla de plan denominada propietario.PLAN_TABLE y tener privilegios SELECT e INSERT en la tabla

Si la sentencia EXPLAIN se prepara dinámicamente, las reglas de autorización que se aplican son las definidas para preparar dinámicamente la sentencia de SQL especificada. Además, el ID de autorización de SQL del proceso o el rol que está asociado al proceso (si la sentencia EXPLAIN se ejecuta en un contexto fiable que especifica la cláusula ROLE AS OBJECT OWNER AND QUALIFIER) también debe tener una de las características siguientes:

Ser el creador de una tabla de plan denominada PLAN_TABLE
Tener un alias en una tabla de plan denominada creador.PLAN_TABLE y tener privilegios SELECT e INSERT en la tabla

Para las sentencias EXPLAIN con las palabras clave PLAN y ALL, el conjunto de privilegios que se define a continuación debe incluir al menos uno de los siguientes elementos:

EXPLAIN
SQLADM
DBADM del sistema
Las reglas de autorización que se han definido para la sentencia de SQL especificada en la sentencia EXPLAIN. Por ejemplo, las reglas de autorización que se aplican cuando se capturan registros de EXPLAIN para una sentencia DELETE son las reglas de autorización para la sentencia DELETE.

Inicio del cambio FL 500 Las reglas de autorización son diferentes si se especifican las palabras clave STMTCACHE o STABILIZED DYNAMIC QUERY. El conjunto de privilegios debe incluir al menos uno de los elementos siguientes: fin del cambio

Autorización SQLADM
Autorización SYSADM
La autorización necesaria para compartir la sentencia en almacenada en la memoria caché. Para obtener más información sobre la autoridad para utilizar la caché de sentencias dinámica, consulte Condiciones para compartir sentencias.
Autorización DBADM del sistema

Para las sentencias EXPLAIN que contienen la cláusula STMTCACHE ALL, el conjunto de privilegios debe incluir al menos uno de los elementos siguientes:

Autorización SQLADM
Autorización DBADM del sistema
Autorización SYSADM

Si el conjunto de privilegios no tiene la autorización necesaria, los registros de EXPLAIN sólo capturan las sentencias que tengan el mismo ID de autorización que el conjunto de privilegios.

Para la palabra clave PACKAGE, el conjunto de privilegios debe incluir al menos uno de los elementos siguientes:

Autorización SQLADM
Autorización SYSADM
autorización SYSOPR
Autorización SYSCTRL

Conjunto de privilegios:

El conjunto de privilegios comprende la unión de las autoridades que poseen los identificadores de autorización del proceso. Si el proceso se ejecuta en un contexto fiable con un rol, este rol se incluirá como un ID de autorización del proceso.

Sintaxis para EXPLAIN

especificación-ámbito-paquete:

descripción para EXPLAIN

PLAN

Especifica que la información de vía de acceso se captura para la sentencia de SQL. Bajo esta opción, Db2 utiliza el proceso de selección de vía de acceso para generar los registros de EXPLAIN para la sentencia.

Se inserta una fila en PLAN_TABLE para cada paso utilizado en la ejecución de sentencia-sql explicable. No se incluyen los pasos para aplicar restricciones de referencia.

Si existe una tabla de sentencias, se inserta una fila que proporciona una estimación de coste del proceso de la sentencia explicable en la tabla de sentencias. Si la sentencia explicable es SELECT FROM declaración-cambio-datos, se insertan dos filas en la tabla de sentencia.

Si existe una tabla de funciones, se inserta una fila en la tabla de funciones para cada función definida por el usuario a la que la sentencia explicable hace referencia.

Si existen tablas de EXPLAIN adicionales, también se insertan filas en dichas tablas.

Para obtener más información, consulte:

ALL

Tiene el mismo efecto que PLAN.

SET QUERYNO = entero

Asocia entero con sentencia-sql explicable. A la columna QUERYNO se le asigna el valor entero en cada fila insertada en la tabla de plan, la tabla de sentencia o la tabla de funciones mediante la sentencia EXPLAIN. Si no se especifica QUERYNO, el propio Db2 asigna un número. Para una sentencia EXPLAIN incorporada, el número es el número de sentencia asignado por el precompilador y colocado en el DBRM.

FOR sentencia-sql-explicable

Especifica el texto de una sentencia de SQL para la que se capturan los registros de EXPLAIN. explainable-sql-statement puede ser cualquier sentencia SQL explicable. Inicio del cambio

Una sentencia es explicable si es una sentencia SELECT, MERGE, TRUNCATE o INSERT, o la forma buscada de una sentencia UPDATE o DELETE. fin del cambio

Si la sentencia EXPLAIN está incrustada en un programa, la sentencia puede contener referencias a variables del host. Si EXPLAIN se prepara dinámicamente, la sentencia puede contener marcadores de parámetros. Las variables del lenguaje principal que aparecen en la sentencia deben definirse en el programa de la sentencia.

La sentencia debe hacer referencia a objetos en el servidor actual.

sentencia-sql explicable no debe contener una cláusula QUERYNO. Para especificar el valor de la columna QUERYNO, utilice la cláusula SET QUERYNO = entero de la sentencia EXPLAIN.

sentencia-sql explicable no puede ser un nombre de sentencia o una variable de lenguaje principal. Para utilizar registros de EXPLAIN de captura para sentencias de SQL dinámico, debe preparar la sentencia EXPLAIN completa de forma dinámica.

Para obtener información sobre una sentencia de SQL explicable que haga referencia a una tabla temporal declarada, la sentencia EXPLAIN debe ejecutarse en el mismo proceso de aplicación en el que se declaró la tabla. Para las sentencias EXPLAIN estáticas, la información no se obtiene en tiempo de enlace, sino en tiempo de ejecución cuando la sentencia EXPLAIN se enlaza de forma incremental.

STMTCACHE

Especifica que los registros de EXPLAIN para las sentencias de SQL dinámico especificadas en la memoria caché de sentencias dinámicas se extraen y se graban en las tablas de EXPLAIN. Bajo esta opción, no se produce ningún nuevo proceso de selección de vías de acceso. Los registros de EXPLAIN se extraen de las vías de acceso existentes que se seleccionaron cuando se prepararon las sentencias de SQL dinámico y accedieron a la memoria caché de sentencias. En un entorno de intercambio de datos, los registros EXPLAIN se extraen de la caché de sentencias dinámica del miembro que comparte datos donde se ejecuta la sentencia EXPLAIN STMTCACHE.

ALL

Especifica que se extraen los registros de EXPLAIN para todas las sentencias almacenadas en la memoria caché. STMTCACHE ALL devuelve una fila para cada sentencia almacenada en memoria caché a DSN_STATEMENT_CACHE_TABLE. Estas filas contienen información de identificación sobre las sentencias de la memoria caché, así como estadísticas que reflejan la ejecución de las sentencias por todos los procesos que han ejecutado la sentencia. Los registros no se devuelven a otras tablas EXPLAIN cuando se especifica STMTCACHE ALL.

STMTID id-variable-lenguaje-principal o constante-entero

Especifica que se extraen los registros de EXPLAIN para la sentencia almacenada en memoria caché con el ID de sentencia especificado. El valor contenido en id-variable-lenguaje-principal o especificado por constante-entero identifica el ID de sentencia. STMTCACHE STMTID devuelve filas a las siguientes tablas EXPLAIN:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE
DSN_STATEMENT_CACHE_TABLE

El ID de sentencia es un entero que identifica de forma exclusiva una sentencia que se ha almacenado en memoria caché en la memoria caché de sentencias dinámicas. La identificación de la declaración de una declaración almacenada en caché se puede recuperar a través de las instalaciones de monitorización IFI desde IFCID 316 o 124. Algunos registros de rastreo de diagnóstico, como los IFCID 0173, 0196 y 0337 también muestran el ID de sentencia.

La columna QUERYNO de cada registro de tabla de EXPLAIN que se devuelve contiene el valor de ID de sentencia.

STMTTOKEN id-variable-lenguaje-principal o constante-serie

Especifica que los registros de EXPLAIN se extraen para las sentencias en memoria caché con la señal de LA sentencia especificada y se graban en determinadas tablas de EXPLAIN. El valor contenido en variable-lenguaje-principal-señal o especificado por constante-serie identifica la señal de la sentencia. STMTCACHE STMTTOKEN escribe registros en las siguientes tablas EXPLAIN:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE
DSN_STATEMENT_CACHE_TABLE

La señal de sentencia debe ser una serie de caracteres que no tenga más de 240 bytes. El programa de aplicación que prepara e inserta originalmente una sentencia en la memoria caché asocia una señal de sentencia con la sentencia en almacenada en memoria caché. El programa puede realizar esta asociación con la función SET_ID de RRSAF o con la API sqleseti si el programa está conectado de forma remota.

La columna STMTTOKEN de cada registro de PLAN_TABLE que se devuelve contiene el valor de señal de la sentencia. La columna QUERYNO de cada registro de tabla de EXPLAIN que se devuelve contiene el valor de ID de sentencia.

Para obtener más información, consulte:

PACKAGE

Especifica que los registros de EXPLAIN de todas las sentencias de SQL estático del paquete que coincide con el ámbito especificado se extraen y se graban en las tablas de EXPLAIN. Bajo esta opción, no se produce ningún nuevo proceso de selección de vías de acceso. Los registros se extraen de las vías de acceso existentes que se seleccionaron cuando se enlazó el paquete. La información de EXPLAIN se añade a PLAN_TABLE que es propiedad del usuario actual. Las demás tablas EXPLAIN no están pobladas.

COLLECTION nombre-recopilación

Especifica que los registros de EXPLAIN solo se capturan para sentencias bajo el nombre-recopilaciónespecificado. nombre-recopilación es una constante de serie o una variable del lenguaje principal que representa el nombre de la colección.

PAQUETE nombre-paquete

Especifica que los registros de EXPLAIN sólo se capturan para sentencias bajo el nombre-paqueteespecificado. nombre-paquete es una constante de serie o una variable del lenguaje principal que representa el nombre del paquete.

VERSION nombre-versión

Especifica que los registros de EXPLAIN sólo se capturan para sentencias bajo el nombre-versiónespecificado. nombre-versión es una constante de serie o una variable del lenguaje principal que representa el nombre de la versión. Si nombre-versión está todo en blanco o es una serie vacía, los registros sólo se capturan para las versiones del paquete que contienen todos los espacios en blanco para el nombre de la versión.

Si no se especifica la cláusula VERSION, los registros de EXPLAIN se capturan para las sentencias en todas las versiones del paquete nombre-paquete.

COPY id-copia

Especifica que los registros de EXPLAIN solo se capturan para sentencias bajo el id-copia especificado. id-copia debe ser uno de los valores siguientes:

ACTUAL
PREVIOUS
ORIGINAL

Inicio del cambio FL 500Si no se especifica la cláusula COPY, se capturan sentencias, registros EXPLAIN para las copias actual, anterior y original que existan para ese paquete. fin del cambio

La columna HINT_USED de PLAN_TABLE se llena con EXPLAIN PACKAGE: copy-id. copy-id en la columna HINT_USED será uno de los siguientes valores: Inicio del cambio

ACTUAL - la copia actual
ANTERIOR - la copia anterior
ORIGINAL - la copia original

STABILIZED DYNAMIC QUERY

FL 500Especifica que los registros explain de la sentencia dinámica estabilizada identificada por la palabra clave STMTID se extraigan y escriban en las siguientes tablas EXPLAIN:

PLAN_TABLE
DSN_STATEMNT_TABLE
DSN_FUNCTION_TABLE

STMTID id-variable-lenguaje-principal o constante-entero

Especifica que la información de EXPLAIN se captura para la sentencia con el identificador de sentencia especificado. Este valor se inserta en la columna PER_STMT_ID de las tablas EXPLAIN.

COPY id-copia

Especifica que la información de EXPLAIN solo se captura para las sentencias bajo el valor de identificador de copia especificado. id-copia es una constante de serie y debe ser uno de los valores siguientes:

ACTUAL
NO VÁLIDA

La columna PLAN_TABLE.HINT_USED se cumplimenta con la serie 'EXPLAIN SDQ: número-id-copia' donde número-ID-copia tiene uno de los valores siguientes:

ACTUAL: La copia actual.
NO VÁLIDA: La copia no válida.

La columna QUERYNO de cada registro de tabla EXPLAIN que se devuelve se establece en el valor predeterminado 0 y el valor de la columna COLLID se establece en 'DSNSTBLQRYEXPLAIN'.

Notas para EXPLAIN

Salida de EXPLAIN:

Db2 inserta una o más filas de datos en una tabla de plan y otras tablas de EXPLAINexistentes.

Para ver una lista de todas las tablas EXPLAIN, consulte Tablas EXPLAIN.

Debe existir una tabla de plan antes de la operación que da como resultado la salida de EXPLAIN. Puede encontrar un ejemplo de declaración CREATE TABLE para cada tabla EXPLAIN en el miembro DSNTESC de la biblioteca con prefijo. SDSNSAMP.

A menos que necesite la información proporcionada por las tablas de EXPLAIN adicionales, no es necesario crear dichas tablas para utilizar EXPLAIN. Sin embargo, es necesaria una tabla de memoria caché de sentencias cuando se especifica la palabra clave STMTCACHE ALL como parte de una sentencia EXPLAIN.

Db2 utiliza el proceso de selección de vía de acceso para generar registros de EXPLAIN sólo para determinados tipos de sentencias EXPLAIN, tal como se muestra en la tabla siguiente.

Tabla 1. Origen de los registros de EXPLAIN para diversas opciones de sentencia EXPLAIN
Opciones especificadas	Cómo Db2 crea registros de EXPLAIN
`EXPLAIN PLAN FOR explainable-sql-statement`	Utiliza el proceso de selección de vía de acceso para generar los registros de EXPLAIN
`EXPLAIN PACKAGE ...`	Extrae la información de vía de acceso existente del paquete para crear los registros de EXPLAIN.
`EXPLAIN STMTCACHE ...`	Extrae información de vía de acceso de la memoria caché de sentencias dinámicas para crear los registros de EXPLAIN.
`EXPLAIN STABILIZED DYNAMIC QUERY ...`	Extrae información de vía de acceso de tablas de catálogo de las sentencias de SQL dinámico estabilizadas especificadas.

Cada fila de una tabla de EXPLAIN describe algún aspecto de un paso de la ejecución de una consulta o una subconsulta en una sentencia explicable. Los valores de las columnas de la fila identifican, entre otras cosas, la consulta o la subconsulta, las tablas y otros objetos involucrados, los métodos utilizados para llevar a cabo cada paso y la información sobre los costes relativa a esos métodos.

Algunas herramientas de optimización también pueden crear y utilizar instancias de estas tablas. Para obtener información sobre el significado de los diferentes valores en la tabla de planes y otras tablas EXPLAIN, consulte Interpretación del acceso a datos mediante EXPLAIN.

Para obtener información sobre cómo correlacionar información entre tablas EXPLAIN, consulte Correlacionar información entre tablas EXPLAIN.

Las tablas de EXPLAIN pueden contener nombres que empiecen por DSN generados por Db2.

Importante : No manipule manualmente los datos de las tablas EXPLAIN creadas por herramientas de optimización.

Permisos de control a la columna o filas aplicados para las tablas de EXPLAIN:

Los permisos de control de acceso a columnas y filas se pueden aplicar para las tablas de EXPLAIN. Sin embargo, los permisos de fila y las máscaras de columna no se aplican cuando Db2 inserta filas en dichas tablas.

Si la sentencia especificada hace referencia a tablas para las que se activa el control de acceso a filas o columnas, la siguiente información de las definiciones de permiso de fila y máscara de columna creadas para las tablas puede aparecer en las tablas de EXPLAIN:

DSN_FUNCTION_TABLE - funciones definidas por el usuario
DSN_PREDICAT_TABLE - predicados (excepto los predicados en las cláusulas CASE WHEN)
DSN_STRUCT_TABLE - bloques de consulta
PLAN_TABLE - vía de acceso de las subconsultas

Además, el texto de definición completa o parcial puede aparecer en tablas EXPLAIN como DSN_FUNCTION_TABLE, DSN_PREDICAT_TABLE, DSN_QUERY_TABLE, DSN_SORTKEY_TABLE, DSN_STATEMENT_CACHE_TABLE y DSN_STATEMENT_RUNTIME_INFO.

Impacto en las vías de acceso existentes cuando la tabla ha aplicado los permisos de control de acceso a columna o filas:

Los predicados de los permisos de fila se consideran en la selección de vía de acceso. Por lo tanto, se muestran en las tablas de EXPLAIN con la finalidad de ajuste del rendimiento.

Impacto en las tablas de EXPLAIN cuando se hace referencia a un objeto no existente en una sentencia de SQL dinámico

En algunas situaciones en las que EXPLAIN se ejecuta en una sentencia de SQL dinámico que hace referencia a un objeto inexistente, se emite un SQLCODE -204 para indicar que el objeto no está definido en el subsistema Db2. Sin embargo, es posible que los cambios en las tablas de EXPLAIN relacionadas no se retrotraen. fin del cambio

Consideraciones cuando se capturan registros de EXPLAIN para la aceleración de consultas de conjunto de filas:

No se puede pasar una consulta de conjunto de filas a un servidor de acelerador para su proceso en los casos siguientes:

Si la consulta de conjunto de filas se ejecuta de forma remota
Si la consulta de conjunto de filas se declara WITH RETURN
Si la consulta de conjunto de filas se ejecuta con una rutina PL de SQL

No puede utilizar una sentencia EXPLAIN estática para determinar si una consulta de conjunto de filas se pasa a un servidor de acelerador, porque no puede especificar el atributo de cursor WITH ROWSET POSITIONING para una sentencia EXPLAIN estática. En su lugar, debe utilizar una sentencia EXPLAIN dinámica, donde la cláusula WITH ROWSET POSITIONING se especifica en la serie de atributo. También puede especificar la cláusula WITH RETURN en la serie de atributo para ver la inelegibilidad de conjuntos de resultados.

Además, la sentencia EXPLAIN no se puede utilizar para determinar que una consulta de conjunto de filas no se puede pasar a un servidor de acelerador porque la consulta se ejecuta de forma remota o con una rutina PL de SQL. Si PREPARE de la sentencia EXPLAIN se ejecuta localmente, Db2 determina si la consulta de conjunto de filas se puede acelerar como una descarga de consulta local. Sin embargo, si PREPARE de la sentencia EXPLAIN se ejecuta de forma remota, Db2 indica que la consulta de conjunto de filas no se puede acelerar. En su lugar, puede utilizar el registro especial CURRENT EXPLAIN MODE para determinar el comportamiento de las sentencias de SQL dinámico elegibles durante la ejecución de la aplicación. Para más información, consulte el registro especial CURRENT EXPLAIN MODE.

Tablas EXPLAIN

Para ver una descripción de las tablas EXPLAIN, consulte Tablas EXPLAIN.

Ejemplos para EXPLAIN

Ejemplo 1: Determine los pasos necesarios para ejecutar la consulta '

SELECT
X.ACTNO...

'. Supongamos que ningún conjunto de filas de PLAN_TABLE tiene el valor 13 para la columna QUERYNO.

   EXPLAIN PLAN SET QUERYNO = 13
   FOR SELECT X.ACTNO, X.PROJNO, X.EMPNO, Y.JOB, Y.EDLEVEL
       FROM DSN8C10.EMPPROJACT X, DSN8C10.EMP Y
          WHERE X.EMPNO = Y.EMPNO
             AND X.EMPTIME > 0.5
             AND (Y.JOB = 'DESIGNER' OR Y.EDLEVEL >= 12)
          ORDER BY X.ACTNO, X.PROJNO;

Ejemplo 2: Recuperar la información que se ha devuelto en el Ejemplo 1. Supongamos que existe una tabla de sentencia, por lo que también se recupera el coste estimado de proceso de la consulta. Utilice la consulta siguiente, que une la tabla de plan y la tabla de sentencia.

  SELECT * FROM PLAN_TABLE A, DSN_STATEMNT_TABLE B
    WHERE A.QUERYNO = 13 and B.QUERYNO = 13
    ORDER BY A.QBLOCKNO, A.PLANNO, A.MIXOPSEQ;

Ejemplo 3: Extraiga la información de vía de acceso existente para capturar registros de EXPLAIN para la sentencia almacenada en memoria caché con el ID de sentencia 124. Supongamos que el SID de variable del lenguaje principal contiene 124.

EXPLAIN STMTCACHE STMTID :SID;

Ejemplo 4: Extraiga la información de vía de acceso existente para capturar una fila de datos de EXPLAIN para cada sentencia de la memoria caché de sentencias dinámicas. Los registros sólo se graban en DSN_STATEMENT_CACHE_TABLE.

EXPLAIN STMTCACHE ALL;

Ejemplo 5: Supongamos que desea utilizar la tabla de plan creada por ADMF001 y su ID de autorización es SYSADM. Si tiene un alias en ADMF001.PLAN_TABLE (CREATE ALIAS SYSADM.PLAN_TABLE FOR ADMF001.PLAN_TABLE) y suficientes privilegios INSERT y SELECT en la tabla, se ejecutará la siguiente sentencia EXPLAIN y ADMF001.PLAN_TABLE.

  EXPLAIN PLAN SET QUERYNO = 101
    FOR SELECT * FROM DSN8C10.EMP;

Ejemplo 6: Extraiga la información de vía de acceso existente para capturar registros de EXPLAIN en la PLAN_TABLE del usuario actual para todas las sentencias de SQL estático en la copia actual del paquete 'COLLA.PACK52604':

  EXPLAIN PACKAGE COLLECTION 'COLLA' PACKAGE 'PACK52604' COPY 'CURRENT';

Finalizar la información de la interfaz de programación específica del programa.