IDUG Content Committee

Por muchos años hemos sabido que al momento de ejecutar BIND sobre paquetes y planes crea las estructuras necesarias dentro del catálogo del Db2 y del directorio para poder ejecutar los programas que contienen. Sabemos sobre esto debido a que mantener dichas estructuras y sus access paths en un estado óptimo involucra una gran parte del tiempo de los DBAs: Ejecutar RUNSTATS, BIND, aprender a descifrar la salida de un EXPLAIN, evaluar los access paths, observar las tendencias de nuestras aplicaciones a lo largo del tiempo, hacer decisiones sobre su mantenimiento, afinación y corrección de queries, y mucho más que sólo esto; básicamente estamos describiendo un trabajo de tiempo completo, o al menos la porción del trabajo que usualmente mantendrá ocupado al DBA en el día a día. Todo esto también aplica a los entornos distribuidos en donde las aplicaciones residen en servidores remotos ya que la evolución nos lleva a nuevos entornos, de manera que nos hemos familiarizado con el Cache dinámico (Dynamic Statement Cache), local prepares, full prepares, algunos parámetros de subsistema y mucho más.

Sin embargo, en estos días también estamos aprendiendo sobre estrategias de administración de nuestras colecciones NULLID (aquellas colecciones que sirven como el puente de entrada para las conexiones remotas) y sus niveles de compatibilidad de aplicaciones. Ahora también tenemos un nuevo concepto en Db2 12 para z/OS que requiere atención y estudio para nuestras aplicaciones dinámicas: Dynamic SQL plan stability. Tal vez hayan escuchado sobre esta nueva característica de Db2, pero antes de que decidan activarla, primero debemos estudiarla a detalle y este artículo va a ayudarlos en su investigación.

Si desean leer más sobre esta nueva característica y otras mejoras que llegaron con Db2 12 para z/OS, en IDUG preparamos un documento bajo el título “DB2 12 for z/OS. An IDUG User Technical Perspective” y se encuentra en el siguiente link (artículo en inglés).

https://www.idug.org/p/do/sd/sid=9321

¿Qué es Dynamic SQL plan stability?

A partir de Db2 12 para z/OS con nivel de función 500 o superior, podemos utilizar esta nueva característica. Si se encuentra activada, Db2 va a guardar las estructuras preparadas sobre nuestros queries dinámicos dentro de las siguientes tablas del catálogo, las cuales pueden ser consultadas y exploradas:

• SYSDYNQRY. Aquí tendremos la información general sobre queries estabilizados.
• SYSDYNQRYDEP. Como menciona el nombre, aquí se encontrará información sobre las dependencias de paquetes y dependencias con autoridades.

También existe otro grupo de tablas para esta función, sin embargo, debido a que la información está en formato interno, no podremos ser capaces de consultarlas para poder aprender sobre su contenido.

• SYSDYNQRY_EXPL.
• SYSDYNQRY_OPL.
• SYSDYNQRY_SHTEL.
• SYSDYNQRY_SPAL.
• SYSDYNQRY_TXTL.

La fuente de esta información, es decir, los queries que van a ser estabilizados, se localizarán primero en el Dynamic Statement Cache y después van a ser guardados dentro de las tablas anteriores siempre y cuando apliquen a las condiciones especificadas al momento de ejecutar el comando. Más sobre esto a continuación.

Después, al momento de la petición de preparar un query, Db2 va a buscar dentro del Dynamic Statement Cache, aunque esta vez, si no se encuentra en el cache, Db2 va a buscar en SYSIBM.SYSDYNQRY y si se encuentra en el catálogo, la información se va a tomar de ahí y va a ser copiada hacia el DSC (Dynamic Statement Cache) para ser utilizada desde ahí. Lo mismo aplica cuando el contenido del DSC se pierde (por ejemplo, en un reinicio de Db2), en cuyo caso Db2 va a buscar dentro de SYSIBM.SYSDYNQRY y cargar los queries estabilizados desde el catálogo hacia el DSC. Con esto ya tienen una estrategia para guardar los access paths existentes que aplican a sus queries dinámicos.

Todo esto es lo que se conoce como el proceso de estabilización de queries dinámicos.

Lo que podría ser

Al utilizar esta característica, tendrán más trucos y técnicas para la administración de la carga que tenga su entorno, y también podría ofrecer visión dentro de las tendencias de sus aplicaciones si quieren explotar los contenidos de SYSIBM.SYSDYNQRY. Esto depende de cómo quieren utilizarlo ya que existen algunas alternativas para iniciar la colección de queries estabilizados. Podrían utilizar esto sobre un conjunto simple de queries o estabilizar queries específicos una vez que los hayan localizado dentro del DSC.

Lo que no es

Por ningún motivo debe tomarse esta característica como un atajo para administrar su entorno distribuido. La premisa es muy sencilla: Ustedes no van a querer estabilizar todos sus queries ya que también estarán incluyendo queries que tienen un desempeño no deseado.

¿Por qué, o cuándo hace sentido?

Especialmente para aplicaciones y entornos de alto volumen y transacciones, podríamos sufrir por cambios en access paths y por el costo de la acción de full prepare para miles de queries dinámicos. Como también sabemos, el contenido del DSC se pierde entre reinicios de Db2 y cada vez que el contenido del DSC se pierde, los queries deben ser preparados nuevamente.

El propósito, como lo indica el nombre de esta característica nueva, es estabilizar queries dinámicos dado que un siguiente PREPARE podría resultar en una diferente forma para el mismo query.

Esto podría ser un punto para un debate futuro, ya que estabilizar los queries y mantener sus access paths podría significar perder el beneficio de la elección de mejores access paths que pudieron haber sido encontrados. Por lo tanto, deben ser muy cautelosos al elegir sus opciones.

¿Cómo puedo usarlo?

Comenzaremos con el comando START y DISPLAY. Para activar la recolección de queries estabilizados, tenemos un comando START que requiere identificar un grupo de estabilización, el cual es un ID que nosotros vamos a proveer, y de esta manera podremos localizar una unidad de estabilización. También tenemos una opción para límites que sirve como un contador: Si un query se ha ejecutado por lo menos la misma cantidad de veces que indica esta opción, entonces se decide que este es query es un candidato para estabilizar, y el número base para esta opción es 2 ejecuciones. Es importante mencionar que para utilizar este parámetro se requiere la activación del IFCID 0316 y 038 para la clase 29 del trace de tipo MONITOR.

A partir de este punto, el resto de las opciones van a ofrecer algunas otras opciones o estrategias que describiremos a continuación.

Estrategia 1: Estabilizar toda la actividad dinámica

En el comando START tenemos la opción para filtrar por SQLID y su default es *, lo que significa que al omitir este parámetro (llamado CURSQLID), toda la actividad dinámica que califica para este número de ejecuciones va a ser estabilizada.

En este ejemplo, todos los queries dinámicos en el DSC que hayan sido ejecutados 50 veces o más van a ser estabilizados. Van a querer ser cuidadosos con esta opción ya que es muy probable que contenga queries con los que ustedes no están conformes, esto es, queries con bajo desempeño que ustedes ya habían identificado previamente o tal vez no.

Estrategia 2: Estabilizar aplicaciones

Si separan aplicaciones por id de usuario, entonces también pueden crear grupos de estabilización que representan una aplicación en específico, y en caso de que hayan agrupado todos los accesos sobre sus tablas críticas a un usuario específico, entonces también pueden estabilizar el grupo que identifica este usuario y toda su actividad dinámica para que puedan examinarla a más detalle.

Estrategia 3: Estabilización específica sobre queries dentro del DSC

También puede crear grupos de estabilización que apuntan hacia IDs de queries específicos dentro del DSC o por token. Esto significa que para usar esta estrategia, ustedes ya hicieron investigación previa al ejecutar EXPLAIN STATEMENT CACHE y estudiaron los contenidos de la tabla DSN_STATEMENT_CACHE_TABLE.

Como siempre, todo depende del punto de vista que deseen tomar. Si quieren examinar esta función en general, lo más recomendable sería ir directamente a esta alternativa para que puedan observar a detalle antes de tomar alguna decisión agresiva como estabilizar todos los queries de un usuario, o peor, estabilizar toda la actividad dinámica.

Estabilización dinámica

También tenemos la opción de utilizar esta función de una manera más dinámica. Todos los previos comandos tienen algo en común: Capturan queries que se han ejecutado por lo menos una cantidad de veces que nosotros especificamos, ¿y qué pasa si en el futuro algún otro query va a calificar para ser estabilizado? También tenemos una manera de utilizar la opción MONITOR para este propósito, el cual va a estar examinando constantemente el DSC, y si se encuentra algún query que coincide con un grupo de estabilización y ha alcanzado el número de ejecuciones que buscamos, entonces es agregado a SYSIBM.SYSDYNQRY.

Consideraciones para Data Sharing

Para grupos de Data Sharing, tenemos las siguientes notas:

• Al estabilizar por ID o token, Db2 va a buscar dentro del DSC del miembro en donde se ejecutó el comando.
• También podemos ejecutar el comando START con la opción SCOPE. Esto tiene una nota extra: Si en algún momento se agrega un nuevo miembro al grupo de Data Sharing, el monitor de estabilización no va a arrancar en automático para este nuevo miembro. Por ejemplo, si iniciamos el monitoreo activo sobre la actividad de un usuario en específico en todo el grupo de Data Sharing usando SCOPE(GROUP), y después en algún otro día se agrega un nuevo miembro dentro del grupo, el monitoreo no va a aplicar en automático para este nuevo miembro.

Otras consideraciones

La estabilización de queries dinámicos es posible con algunas limitaciones, en donde los queries que cumplan con alguna de las siguientes condiciones no van a calificar para estabilización:

• Queries que hagan referencias a archive tables y por lo tanto, sean afectadas por la variable GET_ARCHIVE.
• Queries que son transformados por System Temporal tables, Application Temporal tables y el uso de registros como CURRENT SYSTEM TEMPORAL TIME o CURRENT BUSINESS TEMPORAL TIME.
• Queries preparados con CONCENTRATE STATEMENT WITH LITERALS.
• Queries que fueron preparados con REOPT(AUTO).

Nota especial aquí: Como han visto en el último punto, tal vez en este momento se estén preguntando cómo es que esto va a aplicar a sus colecciones NULLID. Si ejecutaron el programa DB2Binder sin especificar la opción reopt, entonces sus paquetes tomaron la opción de default REOPT(NONE).

Contenido de la tabla SYSIBM.SYSDYNQRY

Ahora vamos a usar un ejemplo para utilizar esta función. Para esto, he creado una copia de la base de datos de ejemplo y he iniciado el monitor para capturar toda la actividad de mi usuario.

Mientras estén experimentando de su lado, también querrán utilizar MONITOR(YES) para que puedan observar cómo cambia el grupo de estabilización. Una vez activado, obtendrán un mensaje de confirmación que regresa un número de monitor de captura en caso de que deseen seguirlo después.

En este momento todavía no he ejecutado ninguna actividad sobre la base de datos de ejemplo, de manera que todavía no tenemos queries estabilizados.

Para este ejemplo, mi monitor de captura es el número 2 y con esto podré identificarlo mediante el comando DISPLAY DYNQUERYCAPTURE. Observen que si iniciamos la estabilización con MONITOR(NO), entonces no podremos ver la información sobre dicho grupo con el comando DISPLAY.

Al ejecutar actividad sobre esta base de datos, veremos que el número de queries estabilizados va a incrementarse. Comenzaremos con un simple SELECT para ver los cambios.

En este punto, si vemos el contenido de SYSIBM.SYSDYNQRY, veremos que ya tenemos algunos queries dentro del mismo como queries estabilizados incluyendo este mismo SELECT. Observen los valores en estas columnas, ya podemos obtener información útil a partir de esto.

Las columnas LASTUSED, COPYID y VALID nos van a dar información clave y conforme pasa el tiempo tendremos más datos para analizar las tendencias de nuestras aplicaciones.

Claro está, también tenemos el texto del query estabilizado dentro de la columna STMTTEXT.

Durante este ejercicio, debido que ejecutamos el comando sobre la actividad de un usuario, también veremos que nuestro query SELECT * FROM SYSIBM.SYSDYNQRY también aparecerá como un query estabilizado y esto sirve como un recordatorio para que sean cuidadosos en cómo desean aprovechar esta función.

Ahora, para continuar con el experimento, veamos los contenidos de SYSDYNQRYREP para este query estabilizado. Esta tabla tendrá información sobre las dependencias de estos queries. Las columnas BNAME y BTYPE indicarán los nombres de los objetos de los que dependen y qué tipo de objetos son, mientras que las columnas AUTHID, BAUTH, PUBLICAUTH y BADMINAUTH tendrán información sobre las dependencias en privilegios. También vean el contenido de la columna CLASS, el cual va a diferencias si una columna pertenece a una autorización o una dependencia de DDL. ¿Qué más nos está diciendo esta tabla? Nos está confirmando que un DROP, ALTER o REVOKE puede invalidar nuestros queries estabilizados.

¿Cómo sucede la invalidación?

En la tabla SYSIBM.SYSDYNQRY tenemos las columnas COPYID y VALID que reportarán sobre esto. La columna VALID contiene algunas opciones que especificarán por qué un query ya no es válido: Un DROP pondrá un valor de ‘N’, mientras que ALTERs tendrán un valor de ‘A’ o ‘H’. La columna COPYID solo tendrá dos opciones: 0 para todas las copias actuales y 4 si esta es una copia inválida. Por lo tanto, al ejecutar un DROP tendremos la siguiente combinación.

¿Cuándo vamos a ver un valor de 4 en COPYID?

• La siguiente vez que el mismo query sea preparado, veremos una nueva copia en SYSIBM.SYSDYNQRY con el mismo valor en SDQ_STMT_ID, sin embargo, tendremos el valor de 0 para indicar que esta es la copia actual y el registro que existía antes indicará que es una copia inválida al tener un indicador de 4.

¿Cómo puedo borrar registros de mis queries estabilizados?

Después de un tiempo cuando decidan borrar las copias invalidadas de sus queries, pueden ejecutar el comando FREE STABILIZED DYNAMIC QUERY, que también va a tener varias opciones:

• Borrar un grupo de estabilización (o todos los grupos).
• Borrar copias de un ID de query en particular.

Y para las dos opciones anteriores, también tenemos los siguientes filtros:

• Borrar todas las copias.
• Borrar solamente las copias inválidas. Tenemos las opciones INVALIDONLY y PLANMGMTSCOPE para filtrar sobre los valores de VALID y COPYID respecticamente.

Tengan cuidado, ya que el default es borrar todas las copias sin importar su validez.

En la salida del comando también podemos observar cuántas copias fueron eliminadas.

¿Tenemos parámetros de subsistema para esta función?

Sí. Ahora tenemos los parámetros de subsistema CACHEDYN_STABILIZATION que va a controlar cómo se comporta esta función, y tiene las siguientes opciones:

• Deshabilitar esta función por completo (valor NONE).
• Habilitar la captura hacia el catálogo pero deshabilitar la carga hacia el DSC (valor CAPTURE).
• Habilitar la carga en el DSC pero deshabilitar la captura de queries (valor LOAD).
• Habilitar la captura de queries y su carga hacia el DSC (este es el default, con el valor BOTH).

Conclusiones

• ¿Cuál es la opción correcta para ustedes? Esa es la gran pregunta. Estabilizar todo va a tener un impacto sobre el espacio en disco del catálogo y podría incluir queries que no deseen que se estabilicen, sin embargo, en la forma más básica del comando START el default es estabilizar todos los queries que se hayan ejecutado más de dos veces, así que tenemos que recordarles que tengan mucho cuidado. Una estrategia, aunque requiere mayor estudio de su parte, es ejecutar EXPLAIN STATEMENT CACHE y observar cuidadosamente las estadísticas de los queries, o tal vez ya hayan identificado previamente algunos queries y quieren confirmarlo al realizar esta práctica.
• Sin importar como deseen experimentar con esta función, requiere un alto nivel de conocimiento de sus aplicaciones y un monitoreo constante, así que tal vez querrán hacer algunos experimentos de su lado antes de considerar sus opciones y también para tener una buena idea de cómo va a ser el impacto en sus tareas cotidianas y cuánto esfuerzo pueden invertir en administrar sus queries estabilizados.