Performance de Db2

-- Ver queries recientes y su motor de optimizacion
SELECT
  SUBSTR(STATEMENT_TEXT, 1, 120) AS SQL_TEXT,
  QUERY_OPTIMIZATION_ENGINE AS MOTOR,
  AVERAGE_TOTAL_TIME AS TIEMPO_PROM_MS,
  TOTAL_TIMES_RUN AS EJECUCIONES
FROM QSYS2.SYSPLANSTAT
WHERE TOTAL_TIMES_RUN > 0
ORDER BY AVERAGE_TOTAL_TIME DESC
FETCH FIRST 20 ROWS ONLY;

-- Forzar que una query use SQE (si cae en CQE)
-- Reescribir evitando features solo-CQE como OPNQRYF

/* Imprimir informacion SQL de un programa */
PRTSQLINF OBJ(MILIB/MIPGM) +
          OBJTYPE(*PGM)

/* Para service programs */
PRTSQLINF OBJ(MILIB/MISRVPGM) +
          OBJTYPE(*SRVPGM)

/* El output va al spool: WRKSPLFLT */

-- Indice radix tree clasico (equivalente a B-Tree)
CREATE INDEX MILIB.IDX_CLIENTES_NOMBRE
  ON MILIB.CLIENTES (NOMBRE);

-- Indice compuesto para queries frecuentes
CREATE INDEX MILIB.IDX_PEDIDOS_CLIFECHA
  ON MILIB.PEDIDOS (CLIENTE_ID, FECHA DESC);

-- Encoded Vector Index para columna de baja cardinalidad
CREATE ENCODED VECTOR INDEX MILIB.EVI_CLIENTES_REGION
  ON MILIB.CLIENTES (REGION);

CREATE ENCODED VECTOR INDEX MILIB.EVI_PEDIDOS_ESTADO
  ON MILIB.PEDIDOS (ESTADO);

-- Indice sobre expresion (derived key)
CREATE INDEX MILIB.IDX_CLIENTES_UPPER_NOMBRE
  ON MILIB.CLIENTES (UPPER(NOMBRE));

-- Indice parcial (sparse): solo filas activas
CREATE INDEX MILIB.IDX_CLIENTES_ACTIVOS
  ON MILIB.CLIENTES (NOMBRE, SALDO)
  WHERE ESTADO = 'A';

-- Indice INCLUDE: columnas extra para index-only access
CREATE INDEX MILIB.IDX_CLIENTES_EMAIL_INC
  ON MILIB.CLIENTES (EMAIL)
  INCLUDE (NOMBRE, TELEFONO);

-- Ver indices recomendados por el optimizador
SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  INDEX_KEY AS COLUMNAS_SUGERIDAS,
  INDEX_INCLUDE_COLUMNS AS INCLUDE_COLS,
  TIMES_ADVISED AS VECES_RECOMENDADO,
  LAST_ADVISED AS ULTIMA_VEZ,
  ESTIMATED_CREATION_TIME AS TIEMPO_CREACION,
  REASON AS MOTIVO
FROM QSYS2.SYSIXADV
WHERE TABLE_SCHEMA = 'MILIB'
ORDER BY TIMES_ADVISED DESC;

-- Limpiar sugerencias acumuladas
-- CALL QSYS2.CLEAR_INDEX_ADVISOR('MILIB');

-- Estadisticas de tablas: filas, tamano, filas eliminadas
SELECT
  TABLE_SCHEMA, TABLE_NAME,
  ROW_COUNT AS FILAS,
  DATA_SIZE AS TAMANO_BYTES,
  NUMBER_DELETED_ROWS AS FILAS_ELIMINADAS,
  COLUMN_COUNT AS COLUMNAS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
ORDER BY ROW_COUNT DESC;

-- Estadisticas de columnas: cardinalidad y distribucion
SELECT
  COLUMN_NAME,
  NUMBER_DISTINCT_VALUES AS VALORES_DISTINTOS,
  LOW_VALUE, HIGH_VALUE,
  AVG_LENGTH AS LARGO_PROMEDIO,
  NUMBER_NULLS AS NULOS
FROM QSYS2.SYSCOLUMNSTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_NAME = 'CLIENTES'
ORDER BY NUMBER_DISTINCT_VALUES DESC;

-- Estadisticas de indices: uso y efectividad
SELECT
  INDEX_NAME, TABLE_NAME,
  COLUMN_NAMES,
  NUMBER_ROWS AS FILAS_INDICE,
  INDEX_SIZE AS TAMANO,
  QUERY_USE_COUNT AS VECES_USADO,
  LAST_QUERY_USE AS ULTIMO_USO
FROM QSYS2.SYSINDEXSTAT
WHERE TABLE_SCHEMA = 'MILIB'
ORDER BY QUERY_USE_COUNT DESC;

/* Reorganizar tabla y actualizar estadisticas */
RGZPFM FILE(MILIB/CLIENTES) RBDACCPTH(*YES)

/* RBDACCPTH(*YES) reconstruye los access paths (indices)
   y actualiza las estadisticas del catalog */

/* Para solo actualizar estadisticas sin reorganizar */
CALL SYSPROC.AQPADVISE('MILIB', 'CLIENTES')

-- Top 20 queries mas costosas por tiempo total acumulado
SELECT
  AVERAGE_TOTAL_TIME AS TIEMPO_PROM_MS,
  TOTAL_TIMES_RUN AS EJECUCIONES,
  DECIMAL(AVERAGE_TOTAL_TIME * TOTAL_TIMES_RUN / 1000, 11, 1)
    AS COSTO_TOTAL_SEG,
  ROWS_RETURNED_AVERAGE AS FILAS_PROM,
  QUERY_OPTIMIZATION_ENGINE AS MOTOR,
  SUBSTR(STATEMENT_TEXT, 1, 200) AS SQL_TEXT
FROM QSYS2.SYSPLANSTAT
WHERE AVERAGE_TOTAL_TIME > 0
ORDER BY COSTO_TOTAL_SEG DESC
FETCH FIRST 20 ROWS ONLY;

-- Queries que usan table scan (candidatas a indice)
SELECT
  SUBSTR(STATEMENT_TEXT, 1, 200) AS SQL_TEXT,
  AVERAGE_TOTAL_TIME AS TIEMPO_MS,
  TOTAL_TIMES_RUN AS EJECUCIONES
FROM QSYS2.SYSPLANSTAT
WHERE PLAN_TEXT LIKE '%Table Scan%'
  AND TOTAL_TIMES_RUN > 10
ORDER BY AVERAGE_TOTAL_TIME DESC
FETCH FIRST 20 ROWS ONLY;

-- Crear snapshot persistente del plan cache
CALL QSYS2.DUMP_PLAN_CACHE(
  PLAN_CACHE_SCHEMA => 'MILIB'
);

/* Limite de tiempo para queries del job actual */
CHGQRYA QRYTIMLMT(30)
/* Queries que excedan 30 segundos se cancelan */

/* Permitir paralelismo con limite */
CHGQRYA QRYTIMLMT(60) DEGREE(*MAX)

/* Aplicar QAQQINI de una biblioteca especifica */
CHGQRYA QRYOPTLIB(MILIB)

-- Copiar QAQQINI a tu biblioteca para personalizarlo
-- CRTDUPOBJ OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(MILIB)

-- Ver opciones actuales
SELECT QQPARM AS PARAMETRO, QQVAL AS VALOR
FROM MILIB.QAQQINI
ORDER BY QQPARM;

-- Configuraciones comunes de performance
INSERT INTO MILIB.QAQQINI (QQPARM, QQVAL) VALUES
  ('QUERY_TIME_LIMIT', '120'),
  ('PARALLEL_DEGREE', 'ANY'),
  ('OPTIMIZE_FOR_N_ROWS', '100'),
  ('REOPTIMIZE_ACCESS_PLAN', '*YES'),
  ('FORCE_JOIN_ORDER', '*NO'),
  ('ALLOW_TEMPORARY_INDEXES', '*YES');

-- Ver CCSID de columnas de una tabla
SELECT
  COLUMN_NAME, DATA_TYPE, CCSID,
  CHARACTER_MAXIMUM_LENGTH
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_NAME = 'CLIENTES'
  AND DATA_TYPE IN ('CHAR', 'VARCHAR', 'CLOB')
ORDER BY ORDINAL_POSITION;

-- Crear tabla con CCSID UTF-8
CREATE TABLE MILIB.CLIENTES_UTF8 (
  ID INTEGER GENERATED ALWAYS AS IDENTITY,
  NOMBRE VARCHAR(200) CCSID 1208,
  EMAIL  VARCHAR(254) CCSID 1208
);

-- MQT de ventas mensuales preagregadas
CREATE TABLE MILIB.MQT_VENTAS_MENSUALES AS (
  SELECT
    YEAR(FECHA) AS ANIO,
    MONTH(FECHA) AS MES,
    REGION,
    CATEGORIA,
    COUNT(*) AS CANTIDAD_VENTAS,
    SUM(MONTO) AS TOTAL_VENTAS,
    AVG(MONTO) AS PROMEDIO_VENTA
  FROM MILIB.VENTAS
  GROUP BY YEAR(FECHA), MONTH(FECHA), REGION, CATEGORIA
) DATA INITIALLY DEFERRED REFRESH DEFERRED
  MAINTAINED BY USER;

-- Cargar/refrescar datos
REFRESH TABLE MILIB.MQT_VENTAS_MENSUALES;

-- SQE puede usar la MQT automaticamente para:
SELECT REGION, SUM(MONTO) AS TOTAL
FROM MILIB.VENTAS
WHERE YEAR(FECHA) = 2025
GROUP BY REGION;
-- El optimizador detecta que puede resolverlo
-- leyendo MQT_VENTAS_MENSUALES en vez de VENTAS

-- Indices no usados (candidatos a eliminar)
SELECT
  INDEX_NAME, TABLE_NAME, TABLE_SCHEMA,
  COLUMN_NAMES, INDEX_SIZE,
  QUERY_USE_COUNT, LAST_QUERY_USE
FROM QSYS2.SYSINDEXSTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND (QUERY_USE_COUNT = 0
       OR LAST_QUERY_USE < CURRENT_TIMESTAMP - 6 MONTHS)
ORDER BY INDEX_SIZE DESC;

-- Tablas con muchas filas eliminadas (necesitan RGZPFM)
SELECT
  TABLE_SCHEMA, TABLE_NAME,
  ROW_COUNT, NUMBER_DELETED_ROWS,
  DECIMAL(NUMBER_DELETED_ROWS * 100.0
    / NULLIF(ROW_COUNT + NUMBER_DELETED_ROWS, 0), 5, 1)
    AS PCT_ELIMINADAS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND NUMBER_DELETED_ROWS > 0
ORDER BY PCT_ELIMINADAS DESC;

-- Locks activos sobre objetos de tu biblioteca
SELECT
  JOB_NAME, LOCK_STATE,
  LOCK_OBJECT_NAME, LOCK_OBJECT_LIBRARY,
  LOCK_TYPE
FROM QSYS2.OBJECT_LOCK_INFO
WHERE LOCK_OBJECT_LIBRARY = 'MILIB'
ORDER BY LOCK_STATE;

-- Ver queries donde AQP intervino con sugerencias
SELECT QQRID, QQJFLD, QQ1000,
       QQRCOD AS REASON_CODE
FROM QSYS2.SYSPLANSTAT
WHERE QQ1000 LIKE '%AQP%'
ORDER BY QQRID DESC
FETCH FIRST 20 ROWS ONLY;