SQL Avanzado en Db2 for i

CTEs, window functions, stored procedures, triggers, funciones JSON, tablas temporales y consultas analíticas avanzadas.

Common Table Expressions (CTEs)

Las CTEs permiten definir subconsultas temporales con nombre usando la cláusula WITH. Son más legibles que subconsultas anidadas y se pueden referenciar múltiples veces en la misma query.

SQL — CTE básico

-- CTE simple: clientes activos con saldo mayor a 10000
WITH clientes_vip AS (
  SELECT ID, NOMBRE, SALDO, EMAIL
  FROM MILIB.CLIENTES
  WHERE ESTADO = 'A' AND SALDO > 10000
),
pedidos_recientes AS (
  SELECT CLIENTE_ID, COUNT(*) AS TOTAL_PEDIDOS,
         SUM(MONTO) AS MONTO_TOTAL
  FROM MILIB.PEDIDOS
  WHERE FECHA >= CURRENT_DATE - 90 DAYS
  GROUP BY CLIENTE_ID
)
SELECT c.NOMBRE, c.SALDO,
       COALESCE(p.TOTAL_PEDIDOS, 0) AS PEDIDOS_90D,
       COALESCE(p.MONTO_TOTAL, 0) AS MONTO_90D
FROM clientes_vip c
LEFT JOIN pedidos_recientes p ON c.ID = p.CLIENTE_ID
ORDER BY p.MONTO_TOTAL DESC NULLS LAST;

Ventaja de las CTEs: Podés definir múltiples CTEs separadas por coma y cada una puede referenciar a las anteriores. Esto permite construir consultas complejas paso a paso, como un pipeline de datos.

CTEs recursivos

Los CTEs recursivos permiten trabajar con datos jerárquicos como organigramas, listas de materiales (BOM) o estructuras de categorías padre-hijo.

SQL — CTE recursivo: organigrama

-- Recorrer un organigrama empezando desde el CEO
WITH RECURSIVE jerarquia (
  EMPLEADO_ID, NOMBRE, PUESTO, JEFE_ID, NIVEL
) AS (
  -- Ancla: el nodo raíz (CEO)
  SELECT EMPLEADO_ID, NOMBRE, PUESTO, JEFE_ID, 0
  FROM MILIB.EMPLEADOS
  WHERE JEFE_ID IS NULL

  UNION ALL

  -- Parte recursiva: empleados que reportan al nivel anterior
  SELECT e.EMPLEADO_ID, e.NOMBRE, e.PUESTO, e.JEFE_ID,
         j.NIVEL + 1
  FROM MILIB.EMPLEADOS e
  INNER JOIN jerarquia j ON e.JEFE_ID = j.EMPLEADO_ID
)
SELECT REPEAT('  ', NIVEL) || NOMBRE AS ORGANIGRAMA,
       PUESTO, NIVEL
FROM jerarquia
ORDER BY NIVEL, NOMBRE;

SQL — CTE recursivo: explosión de materiales (BOM)

-- Bill of Materials: todos los componentes de un producto
WITH RECURSIVE bom (
  COMPONENTE_ID, NOMBRE, PADRE_ID, CANTIDAD, NIVEL
) AS (
  SELECT COMPONENTE_ID, NOMBRE, PADRE_ID, CANTIDAD, 0
  FROM MILIB.MATERIALES
  WHERE PADRE_ID IS NULL AND PRODUCTO_ID = 1001

  UNION ALL

  SELECT m.COMPONENTE_ID, m.NOMBRE, m.PADRE_ID,
         m.CANTIDAD * b.CANTIDAD, b.NIVEL + 1
  FROM MILIB.MATERIALES m
  INNER JOIN bom b ON m.PADRE_ID = b.COMPONENTE_ID
)
SELECT REPEAT('  ', NIVEL) || NOMBRE AS COMPONENTE,
       CANTIDAD, NIVEL
FROM bom
ORDER BY NIVEL, NOMBRE;

Cuidado: Los CTEs recursivos pueden generar loops infinitos si los datos tienen ciclos. Usá FETCH FIRST n ROWS ONLY o agregá un límite de nivel como condición para evitarlo.

Window Functions

Las window functions realizan cálculos sobre un conjunto de filas relacionadas con la fila actual, sin colapsar el resultado como haría un GROUP BY.

ROW_NUMBER()

Número secuencial único por partición

RANK()

Ranking con saltos en empates (1,2,2,4)

DENSE_RANK()

Ranking sin saltos en empates (1,2,2,3)

LAG() / LEAD()

Acceder a filas anteriores o siguientes

SUM() OVER

Suma acumulada o por ventana

NTILE(n)

Dividir filas en n grupos iguales

SQL — Window functions en acción

-- Ranking de vendedores por ventas mensuales
SELECT
  VENDEDOR, MES,
  VENTAS,
  RANK() OVER (PARTITION BY MES ORDER BY VENTAS DESC)
    AS RANKING_MES,
  SUM(VENTAS) OVER (PARTITION BY VENDEDOR
    ORDER BY MES
    ROWS UNBOUNDED PRECEDING)
    AS ACUMULADO_VENDEDOR,
  LAG(VENTAS) OVER (PARTITION BY VENDEDOR ORDER BY MES)
    AS VENTAS_MES_ANT,
  VENTAS - LAG(VENTAS) OVER (PARTITION BY VENDEDOR ORDER BY MES)
    AS VARIACION
FROM MILIB.VENTAS_MENSUALES
ORDER BY MES, RANKING_MES;

SQL — Running totals y moving averages

-- Promedio móvil de 3 meses y suma acumulada
SELECT
  FECHA, MONTO,
  AVG(MONTO) OVER (ORDER BY FECHA
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    AS PROMEDIO_MOVIL_3M,
  SUM(MONTO) OVER (ORDER BY FECHA
    ROWS UNBOUNDED PRECEDING)
    AS TOTAL_ACUMULADO,
  MONTO * 100.0 / SUM(MONTO) OVER ()
    AS PORCENTAJE_DEL_TOTAL
FROM MILIB.TRANSACCIONES;

User-Defined Functions (UDFs)

Las UDFs escalares devuelven un único valor y se pueden usar en cualquier expresión SQL, igual que las funciones built-in.

SQL — Función escalar

-- UDF escalar: calcular edad a partir de fecha de nacimiento
CREATE OR REPLACE FUNCTION MILIB.CALCULAR_EDAD (
  FECHA_NAC DATE
)
RETURNS INTEGER
LANGUAGE SQL
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN
  RETURN YEAR(CURRENT_DATE) - YEAR(FECHA_NAC) -
    CASE WHEN MONTH(CURRENT_DATE) < MONTH(FECHA_NAC)
      OR (MONTH(CURRENT_DATE) = MONTH(FECHA_NAC)
          AND DAY(CURRENT_DATE) < DAY(FECHA_NAC))
    THEN 1 ELSE 0 END;
END;

-- Uso
SELECT NOMBRE, FECHA_NAC,
       MILIB.CALCULAR_EDAD(FECHA_NAC) AS EDAD
FROM MILIB.CLIENTES;

SQL — Función escalar con lógica de negocio

-- UDF: categorizar clientes por saldo
CREATE OR REPLACE FUNCTION MILIB.CATEGORIA_CLIENTE (
  SALDO DECIMAL(11,2)
)
RETURNS VARCHAR(20)
LANGUAGE SQL
DETERMINISTIC
BEGIN
  RETURN CASE
    WHEN SALDO >= 100000 THEN 'PLATINUM'
    WHEN SALDO >= 50000  THEN 'GOLD'
    WHEN SALDO >= 10000  THEN 'SILVER'
    ELSE 'STANDARD'
  END;
END;

-- Uso en WHERE
SELECT * FROM MILIB.CLIENTES
WHERE MILIB.CATEGORIA_CLIENTE(SALDO) = 'PLATINUM';

User-Defined Table Functions (UDTFs)

Las UDTFs devuelven un conjunto de filas y se usan en la cláusula FROM, como si fueran una tabla.

SQL — Table Function

-- UDTF: devolver los N mejores clientes de una región
CREATE OR REPLACE FUNCTION MILIB.TOP_CLIENTES_REGION (
  P_REGION VARCHAR(50),
  P_LIMITE INTEGER
)
RETURNS TABLE (
  NOMBRE VARCHAR(100),
  EMAIL VARCHAR(200),
  SALDO DECIMAL(11,2),
  RANKING INTEGER
)
LANGUAGE SQL
READS SQL DATA
BEGIN ATOMIC
  RETURN
    SELECT NOMBRE, EMAIL, SALDO,
           ROW_NUMBER() OVER (ORDER BY SALDO DESC)
    FROM MILIB.CLIENTES
    WHERE REGION = P_REGION AND ESTADO = 'A'
    FETCH FIRST P_LIMITE ROWS ONLY;
END;

-- Uso con TABLE()
SELECT * FROM TABLE(MILIB.TOP_CLIENTES_REGION('NORTE', 10)) t;

Stored Procedures

Los stored procedures encapsulan lógica de negocio en el servidor de base de datos. Pueden tener parámetros de entrada, salida e inout, y devolver result sets.

SQL — Stored procedure con parámetros

CREATE OR REPLACE PROCEDURE MILIB.CREAR_PEDIDO (
  IN  P_CLIENTE_ID   INTEGER,
  IN  P_PRODUCTO_ID  INTEGER,
  IN  P_CANTIDAD     INTEGER,
  OUT P_PEDIDO_ID    INTEGER,
  OUT P_MENSAJE      VARCHAR(200)
)
LANGUAGE SQL
BEGIN
  DECLARE V_STOCK INTEGER;
  DECLARE V_PRECIO DECIMAL(11,2);

  -- Verificar stock
  SELECT STOCK, PRECIO INTO V_STOCK, V_PRECIO
  FROM MILIB.PRODUCTOS
  WHERE PRODUCTO_ID = P_PRODUCTO_ID;

  IF V_STOCK IS NULL THEN
    SET P_PEDIDO_ID = -1;
    SET P_MENSAJE = 'Producto no encontrado';
    RETURN;
  END IF;

  IF V_STOCK < P_CANTIDAD THEN
    SET P_PEDIDO_ID = -1;
    SET P_MENSAJE = 'Stock insuficiente: ' || CHAR(V_STOCK);
    RETURN;
  END IF;

  -- Crear pedido
  INSERT INTO MILIB.PEDIDOS (CLIENTE_ID, PRODUCTO_ID,
    CANTIDAD, MONTO, FECHA)
  VALUES (P_CLIENTE_ID, P_PRODUCTO_ID,
    P_CANTIDAD, V_PRECIO * P_CANTIDAD, CURRENT_TIMESTAMP);

  SET P_PEDIDO_ID = IDENTITY_VAL_LOCAL();

  -- Actualizar stock
  UPDATE MILIB.PRODUCTOS
  SET STOCK = STOCK - P_CANTIDAD
  WHERE PRODUCTO_ID = P_PRODUCTO_ID;

  SET P_MENSAJE = 'Pedido creado exitosamente';
END;

-- Llamar al procedure
CALL MILIB.CREAR_PEDIDO(1001, 5020, 3, ?, ?);

SQL — Procedure con result set

-- Procedure que devuelve un result set dinámico
CREATE OR REPLACE PROCEDURE MILIB.BUSCAR_CLIENTES (
  IN P_CRITERIO VARCHAR(100)
)
LANGUAGE SQL
DYNAMIC RESULT SETS 1
BEGIN
  DECLARE C1 CURSOR WITH RETURN FOR
    SELECT ID, NOMBRE, EMAIL, SALDO
    FROM MILIB.CLIENTES
    WHERE UPPER(NOMBRE) LIKE '%' || UPPER(P_CRITERIO) || '%'
       OR UPPER(EMAIL) LIKE '%' || UPPER(P_CRITERIO) || '%'
    ORDER BY NOMBRE;

  OPEN C1;
END;

Triggers

Los triggers ejecutan lógica automáticamente antes o después de operaciones INSERT, UPDATE o DELETE. Son ideales para auditoría, validación y propagación de cambios.

BEFORE triggers

•Se ejecutan antes de la operación
•Pueden modificar los valores que se insertan/actualizan
•Pueden rechazar la operación con SIGNAL
•Ideales para validación y valores por defecto
•Acceso a NEW y OLD row

AFTER triggers

•Se ejecutan después de la operación
•No pueden modificar los datos de la operación
•Pueden insertar en otras tablas (auditoría)
•Ideales para logging y propagación
•Acceso a NEW y OLD row

SQL — Trigger de auditoría

-- Trigger AFTER UPDATE para auditoría de cambios de saldo
CREATE OR REPLACE TRIGGER MILIB.AUDIT_SALDO
AFTER UPDATE OF SALDO ON MILIB.CLIENTES
REFERENCING OLD AS OLD_ROW NEW AS NEW_ROW
FOR EACH ROW
BEGIN ATOMIC
  INSERT INTO MILIB.AUDIT_CLIENTES (
    CLIENTE_ID, CAMPO, VALOR_ANTERIOR, VALOR_NUEVO,
    USUARIO, FECHA_CAMBIO
  ) VALUES (
    NEW_ROW.ID, 'SALDO',
    CHAR(OLD_ROW.SALDO), CHAR(NEW_ROW.SALDO),
    CURRENT_USER, CURRENT_TIMESTAMP
  );
END;

SQL — Trigger de validación BEFORE

-- Trigger BEFORE INSERT para validar datos
CREATE OR REPLACE TRIGGER MILIB.VALIDAR_CLIENTE
BEFORE INSERT ON MILIB.CLIENTES
REFERENCING NEW AS NEW_ROW
FOR EACH ROW
BEGIN ATOMIC
  -- Email obligatorio
  IF NEW_ROW.EMAIL IS NULL OR NEW_ROW.EMAIL = '' THEN
    SIGNAL SQLSTATE '75001'
      SET MESSAGE_TEXT = 'El email es obligatorio';
  END IF;

  -- Normalizar nombre a mayúsculas
  SET NEW_ROW.NOMBRE = UPPER(NEW_ROW.NOMBRE);

  -- Asignar fecha de creación
  SET NEW_ROW.CREADO = CURRENT_TIMESTAMP;
END;

MERGE (Upserts)

El statement MERGE combina INSERT y UPDATE en una sola operación atómica. Es el equivalente al "upsert" de otras bases.

SQL — MERGE completo

-- Sincronizar precios desde un archivo de staging
MERGE INTO MILIB.PRODUCTOS AS target
USING MILIB.STAGING_PRECIOS AS source
ON target.PRODUCTO_ID = source.PRODUCTO_ID
WHEN MATCHED AND source.PRECIO <> target.PRECIO THEN
  UPDATE SET
    PRECIO = source.PRECIO,
    PRECIO_ANTERIOR = target.PRECIO,
    FECHA_ACTUALIZACION = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (PRODUCTO_ID, NOMBRE, PRECIO, FECHA_ACTUALIZACION)
  VALUES (source.PRODUCTO_ID, source.NOMBRE,
          source.PRECIO, CURRENT_TIMESTAMP)
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET ESTADO = 'D';  -- Marcar descontinuados

Variables globales y registros especiales

Db2 for i proporciona registros especiales (special registers) y permite crear variables globales que persisten durante toda la conexión.

Registro especialDescripción

CURRENT_USERPerfil de usuario actual

CURRENT_DATEFecha actual del sistema

CURRENT_TIMESTAMPTimestamp actual con microsegundos

CURRENT_SCHEMASchema por defecto para objetos no calificados

CURRENT_PATHLista de schemas para resolución de funciones

CURRENT_SERVERNombre del servidor de base de datos

SESSION_USERUsuario de la sesión (puede diferir de CURRENT_USER)

SQL — Variables globales

-- Crear variable global
CREATE OR REPLACE VARIABLE MILIB.APP_VERSION VARCHAR(20)
  DEFAULT '2.5.1';

CREATE OR REPLACE VARIABLE MILIB.MODO_DEBUG INTEGER
  DEFAULT 0;

-- Usar y modificar
SET MILIB.MODO_DEBUG = 1;
SELECT MILIB.APP_VERSION FROM SYSIBM.SYSDUMMY1;

-- Variable global por sesión (cada conexión tiene su valor)
CREATE OR REPLACE VARIABLE MILIB.SESSION_CONTEXT VARCHAR(500)
  DEFAULT '';

Funciones OLAP y analíticas

Db2 for i soporta funciones OLAP avanzadas para análisis multidimensional, agrupaciones especiales y cálculos estadísticos.

SQL — GROUPING SETS y ROLLUP

-- ROLLUP: subtotales jerárquicos
SELECT
  COALESCE(REGION, '** TOTAL **') AS REGION,
  COALESCE(SUCURSAL, '** Subtotal **') AS SUCURSAL,
  SUM(VENTAS) AS TOTAL_VENTAS,
  COUNT(*) AS TRANSACCIONES
FROM MILIB.VENTAS
GROUP BY ROLLUP(REGION, SUCURSAL)
ORDER BY GROUPING(REGION), REGION,
         GROUPING(SUCURSAL), SUCURSAL;

-- GROUPING SETS: combinaciones específicas
SELECT REGION, CATEGORIA, MES,
       SUM(VENTAS) AS TOTAL
FROM MILIB.VENTAS
GROUP BY GROUPING SETS (
  (REGION, CATEGORIA),
  (REGION, MES),
  (CATEGORIA),
  ()  -- gran total
);

SQL — CUBE y funciones estadísticas

-- CUBE: todas las combinaciones posibles
SELECT REGION, CATEGORIA, YEAR(FECHA) AS ANIO,
       SUM(MONTO) AS TOTAL,
       AVG(MONTO) AS PROMEDIO,
       STDDEV(MONTO) AS DESVIACION
FROM MILIB.VENTAS
GROUP BY CUBE(REGION, CATEGORIA, YEAR(FECHA));

-- Percentiles y medianas
SELECT DEPARTAMENTO,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY SALARIO)
    AS MEDIANA,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY SALARIO)
    AS PERCENTIL_90
FROM MILIB.EMPLEADOS
GROUP BY DEPARTAMENTO;

Tablas temporales

Db2 for i soporta tablas temporales declaradas (DGTT) que existen solo durante la sesión, y tablas temporales creadas (CGTT) que persisten la definición pero no los datos.

SQL — Tablas temporales

-- Declared Global Temporary Table (solo esta sesión)
DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP_RESULTADOS (
  CLIENTE_ID INTEGER,
  NOMBRE VARCHAR(100),
  TOTAL DECIMAL(11,2)
) WITH REPLACE;

-- Insertar datos temporales
INSERT INTO SESSION.TEMP_RESULTADOS
SELECT ID, NOMBRE, SUM(SALDO)
FROM MILIB.CLIENTES
GROUP BY ID, NOMBRE;

-- Consultar (solo visible en esta sesión)
SELECT * FROM SESSION.TEMP_RESULTADOS
ORDER BY TOTAL DESC;

-- Created Global Temporary Table (definición persiste)
CREATE GLOBAL TEMPORARY TABLE MILIB.TEMP_ANALISIS (
  PERIODO CHAR(7),
  METRICA VARCHAR(50),
  VALOR DECIMAL(15,2)
) ON COMMIT DELETE ROWS;

System-period temporal tables: Db2 for i también soporta tablas con versionado temporal del sistema (system-time temporal tables). Estas mantienen automáticamente un historial de todos los cambios, permitiendo consultar datos "como eran" en cualquier punto en el tiempo usando FOR SYSTEM_TIME AS OF.

Funciones JSON en Db2 for i

A partir de IBM i 7.4, Db2 for i incluye funciones nativas para generar y parsear JSON, facilitando la integración con APIs REST y aplicaciones modernas.

SQL — Generar JSON

-- JSON_OBJECT: crear un objeto JSON desde columnas
SELECT JSON_OBJECT(
  'id'     VALUE ID,
  'nombre' VALUE NOMBRE,
  'email'  VALUE EMAIL,
  'saldo'  VALUE SALDO
) AS CLIENTE_JSON
FROM MILIB.CLIENTES
WHERE ID = 1001;
-- Resultado: {"id":1001,"nombre":"JUAN PEREZ","email":"juan@email.com","saldo":50000.00}

-- JSON_ARRAY: crear un array JSON
SELECT JSON_ARRAY(
  SELECT JSON_OBJECT(
    'id' VALUE ID,
    'nombre' VALUE NOMBRE
  )
  FROM MILIB.CLIENTES
  WHERE ESTADO = 'A'
  FETCH FIRST 5 ROWS ONLY
) AS CLIENTES_ARRAY;

-- JSON_ARRAYAGG: agregar filas en un array
SELECT REGION,
  JSON_ARRAYAGG(
    JSON_OBJECT('nombre' VALUE NOMBRE, 'saldo' VALUE SALDO)
  ) AS CLIENTES
FROM MILIB.CLIENTES
GROUP BY REGION;

SQL — Parsear JSON

-- JSON_TABLE: convertir JSON a filas/columnas relacionales
SELECT t.*
FROM JSON_TABLE(
  '{"pedidos":[
     {"id":1,"producto":"Widget","cantidad":5},
     {"id":2,"producto":"Gadget","cantidad":3}
   ]}',
  '$.pedidos[*]'
  COLUMNS (
    PEDIDO_ID  INTEGER PATH '$.id',
    PRODUCTO   VARCHAR(50) PATH '$.producto',
    CANTIDAD   INTEGER PATH '$.cantidad'
  )
) AS t;

-- JSON_VALUE: extraer un valor escalar de un JSON
SELECT JSON_VALUE(DATOS_JSON, '$.direccion.ciudad')
  AS CIUDAD
FROM MILIB.CLIENTES_EXT
WHERE ID = 1001;

-- JSON_EXISTS: filtrar por contenido JSON
SELECT * FROM MILIB.CLIENTES_EXT
WHERE JSON_EXISTS(DATOS_JSON, '$.tags[*]?(@ == "VIP")');

Ejemplo: reporte complejo con CTEs y window functions

Este ejemplo combina múltiples técnicas avanzadas para generar un reporte de ventas completo con rankings, comparaciones intermensuales y acumulados.

SQL — Reporte de ventas avanzado

-- Reporte mensual de ventas con análisis completo
WITH ventas_mensuales AS (
  -- Paso 1: agregar ventas por vendedor y mes
  SELECT
    v.VENDEDOR_ID,
    e.NOMBRE AS VENDEDOR,
    e.REGION,
    YEAR(v.FECHA) AS ANIO,
    MONTH(v.FECHA) AS MES,
    COUNT(*) AS NUM_OPERACIONES,
    SUM(v.MONTO) AS TOTAL_VENTAS,
    AVG(v.MONTO) AS TICKET_PROMEDIO
  FROM MILIB.VENTAS v
  JOIN MILIB.EMPLEADOS e ON v.VENDEDOR_ID = e.EMPLEADO_ID
  WHERE v.FECHA >= CURRENT_DATE - 12 MONTHS
  GROUP BY v.VENDEDOR_ID, e.NOMBRE, e.REGION,
           YEAR(v.FECHA), MONTH(v.FECHA)
),
con_rankings AS (
  -- Paso 2: agregar rankings y comparaciones
  SELECT
    vm.*,
    RANK() OVER (
      PARTITION BY ANIO, MES
      ORDER BY TOTAL_VENTAS DESC
    ) AS RANKING_MENSUAL,
    LAG(TOTAL_VENTAS) OVER (
      PARTITION BY VENDEDOR_ID
      ORDER BY ANIO, MES
    ) AS VENTAS_MES_ANTERIOR,
    SUM(TOTAL_VENTAS) OVER (
      PARTITION BY VENDEDOR_ID, ANIO
      ORDER BY MES
      ROWS UNBOUNDED PRECEDING
    ) AS ACUMULADO_ANUAL
  FROM ventas_mensuales vm
),
resumen_final AS (
  -- Paso 3: calcular variación porcentual
  SELECT
    cr.*,
    CASE WHEN VENTAS_MES_ANTERIOR > 0
      THEN DECIMAL(
        (TOTAL_VENTAS - VENTAS_MES_ANTERIOR) * 100.0
        / VENTAS_MES_ANTERIOR, 5, 1)
      ELSE NULL
    END AS VARIACION_PCT
  FROM con_rankings cr
)
-- Resultado final
SELECT
  VENDEDOR, REGION,
  ANIO || '-' || RIGHT('0' || CHAR(MES), 2) AS PERIODO,
  NUM_OPERACIONES,
  TOTAL_VENTAS,
  TICKET_PROMEDIO,
  RANKING_MENSUAL,
  VARIACION_PCT,
  ACUMULADO_ANUAL,
  CASE WHEN RANKING_MENSUAL = 1 THEN '*** TOP ***'
       WHEN RANKING_MENSUAL <= 3 THEN '* Top 3 *'
       ELSE ''
  END AS DISTINCION
FROM resumen_final
ORDER BY ANIO DESC, MES DESC, RANKING_MENSUAL;

Tip de diseño: Construir consultas complejas con CTEs encadenados hace que cada paso sea testeable de forma independiente. Podés ejecutar solo la primera CTE para verificar los datos base, luego ir agregando capas de análisis.

FINAL TABLE / OLD TABLE (IBM i 7.6)

IBM i 7.6 introduce soporte para FINAL TABLE, que permite obtener los valores finales de las filas despues de un INSERT, UPDATE o DELETE, incluyendo valores generados por triggers, defaults o identity columns. Tambien se soporta FROM OLD TABLE para capturar los valores previos a la modificacion.

Ejemplo: INSERT con FINAL TABLE

SQL — FINAL TABLE con INSERT

-- Insertar y obtener los valores finales (incluyendo identity y defaults)
SELECT * FROM FINAL TABLE (
  INSERT INTO MILIB.ORDENES (CLIENTE_ID, PRODUCTO, CANTIDAD)
  VALUES (1001, 'Widget-A', 50)
);
-- Retorna la fila insertada con ORDEN_ID generado, FECHA default, etc.

Ejemplo: UPDATE con FINAL TABLE

SQL — FINAL TABLE con UPDATE

-- Actualizar y ver los valores resultantes
SELECT ORDEN_ID, ESTADO, FECHA_MODIFICACION
FROM FINAL TABLE (
  UPDATE MILIB.ORDENES
  SET ESTADO = 'ENVIADO', FECHA_MODIFICACION = CURRENT_TIMESTAMP
  WHERE ORDEN_ID = 5042
);

Ejemplo: DELETE con OLD TABLE

SQL — FROM OLD TABLE con DELETE

-- Eliminar y capturar los valores de las filas eliminadas
SELECT * FROM OLD TABLE (
  DELETE FROM MILIB.ORDENES_TEMP
  WHERE FECHA_CREACION < CURRENT_DATE - 90 DAYS
);
-- Retorna todas las filas que fueron eliminadas para auditoria

PARALLEL DEGREE

UPDATE y DELETE con FINAL TABLE soportan PARALLEL DEGREE para ejecucion paralela, mejorando el rendimiento en operaciones masivas sobre tablas grandes.

SQL Services nuevos en IBM i 7.6

IBM i 7.6 agrega y mejora multiples IBM i SQL Services en QSYS2, expandiendo lo que se puede administrar y consultar via SQL puro.

MFA via SQL

Consultar y configurar parametros de MFA, verificar estado de usuarios MFA y gestionar tokens TOTP desde SQL.

Audit Journal mejorado

Funciones helper para facilitar el analisis del journal de auditoria del sistema (QAUDJRN) via SQL.

iSCSI configuration

Nuevos servicios para configurar y consultar conexiones iSCSI desde SQL, incluyendo credenciales CHAP.

ILE Application Services

Acceso mejorado a informacion de programas y service programs ILE: activation groups, binding, etc.

Server subsystem routing

Consulta y configuracion mejorada del ruteo de subsistemas de servidor para conexiones SQL.

Run SQL Scripts mejorado

En ACS: nuevas capacidades de edicion, ejecucion y visualizacion de resultados en Run SQL Scripts.

VS Code Db2 for i extension

La extension Db2 for i para VS Code provee un entorno completo para trabajar con Db2 for i desde el IDE moderno, complementando a Run SQL Scripts de ACS.

Caracteristicas principales

Ejecucion de SQL con resultados en panel integrado
Exploracion de esquemas, tablas, vistas y procedimientos
Autocompletado inteligente de SQL
Integracion con SELF (SQL Error Logging Facility) para diagnostico
Soporte de MFA cuando esta habilitado en el sistema

Instalacion

Instalar la extension desde VS Code Marketplace: buscar "Db2 for IBM i"
Verificar que el componente servidor este instalado en el IBM i (requiere PTFs especificos)
Conectar via SSH al sistema IBM i

Consejo: Si ya usas Code for IBM i (la extension principal de desarrollo), Db2 for i se integra perfectamente al mismo entorno, compartiendo la conexion SSH.

Db2 Fundamentos Journaling Deep Dive