Journaling Deep Dive

CRTJRNRCV JRNRCV(MILIB/RCVR0001) +
          THRESHOLD(500000) +
          TEXT('Receiver inicial para MILIB')

/* THRESHOLD: tamano en KB a partir del cual el sistema
   genera un mensaje de warning. Con MNGRCV(*SYSTEM) en
   el journal, se hace swap automatico al llegar.       */

CRTJRN JRN(MILIB/QSQJRN) +
       JRNRCV(MILIB/RCVR0001) +
       MNGRCV(*SYSTEM) +
       DLTRCV(*YES) +
       RCVSIZOPT(*MAXOPT2) +
       MINENTDTA(*NONE) +
       TEXT('Journal principal de MILIB')

/* Parametros clave:
   MNGRCV(*SYSTEM) - swap automatico de receivers
   DLTRCV(*YES)    - elimina receivers viejos automaticamente
   RCVSIZOPT(*MAXOPT2) - optimiza I/O de receivers grandes
   MINENTDTA(*NONE)    - datos completos en cada entrada   */

/* Asociar tablas criticas al journal */
STRJRNPF FILE(MILIB/CLIENTES) +
         JRN(MILIB/QSQJRN) +
         IMAGES(*BOTH) +
         OMTJRNE(*OPNCLO)

STRJRNPF FILE(MILIB/PEDIDOS) +
         JRN(MILIB/QSQJRN) +
         IMAGES(*BOTH) +
         OMTJRNE(*OPNCLO)

STRJRNPF FILE(MILIB/FACTURAS) +
         JRN(MILIB/QSQJRN) +
         IMAGES(*BOTH) +
         OMTJRNE(*OPNCLO)

/* Detener journaling de una tabla */
ENDJRNPF FILE(MILIB/TABLA_TEMP) +
         JRN(MILIB/QSQJRN)

/* Forma clasica: volcar entradas a un outfile */
DSPJRN JRN(MILIB/QSQJRN) +
       FILE(MILIB/CLIENTES) +
       RCVRNG(*CURCHAIN) +
       JRNCDE(R) +
       ENTTYP(PT UB UP DL) +
       OUTPUT(*OUTFILE) +
       OUTFILFMT(*TYPE5) +
       OUTFILE(QTEMP/JRNDATA)

/* Luego consultar el outfile */
SELECT * FROM QTEMP.JRNDATA
ORDER BY JOENTT DESC
FETCH FIRST 50 ROWS ONLY

-- Rastrear todos los cambios sobre el cliente 1001
SELECT
  ENTRY_TIMESTAMP AS CUANDO,
  USER_NAME AS QUIEN,
  JOB_NAME AS DESDE_JOB,
  PROGRAM_NAME AS PROGRAMA,
  JOURNAL_ENTRY_TYPE AS OPERACION,
  CASE JOURNAL_ENTRY_TYPE
    WHEN 'PT' THEN 'INSERT'
    WHEN 'UP' THEN 'UPDATE (after)'
    WHEN 'UB' THEN 'UPDATE (before)'
    WHEN 'DL' THEN 'DELETE'
    ELSE JOURNAL_ENTRY_TYPE
  END AS TIPO_LEGIBLE,
  ENTRY_DATA
FROM TABLE(
  QSYS2.DISPLAY_JOURNAL(
    'MILIB', 'QSQJRN',
    JOURNAL_CODES       => 'R',
    JOURNAL_ENTRY_TYPES => 'PT,UB,UP,DL',
    OBJECT_NAME         => 'CLIENTES',
    OBJECT_LIBRARY      => 'MILIB',
    STARTING_TIMESTAMP  => CURRENT_TIMESTAMP - 30 DAYS
  )
) AS jrn
ORDER BY ENTRY_TIMESTAMP DESC;

-- Dashboard de actividad: operaciones por usuario en la ultima semana
SELECT
  USER_NAME AS USUARIO,
  OBJECT_NAME AS TABLA,
  SUM(CASE WHEN JOURNAL_ENTRY_TYPE = 'PT' THEN 1 ELSE 0 END) AS INSERTS,
  SUM(CASE WHEN JOURNAL_ENTRY_TYPE = 'UP' THEN 1 ELSE 0 END) AS UPDATES,
  SUM(CASE WHEN JOURNAL_ENTRY_TYPE = 'DL' THEN 1 ELSE 0 END) AS DELETES,
  COUNT(*) AS TOTAL_OPS,
  MIN(ENTRY_TIMESTAMP) AS PRIMERA_OP,
  MAX(ENTRY_TIMESTAMP) AS ULTIMA_OP
FROM TABLE(
  QSYS2.DISPLAY_JOURNAL(
    'MILIB', 'QSQJRN',
    JOURNAL_CODES      => 'R',
    STARTING_TIMESTAMP => CURRENT_DATE - 7 DAYS
  )
) AS jrn
GROUP BY USER_NAME, OBJECT_NAME
ORDER BY TOTAL_OPS DESC;

/* Escenario: la tabla CLIENTES se corrompio.
   Tenes un backup de anoche y los journals del dia. */

/* Paso 1: Restaurar la tabla desde el backup */
RSTOBJ OBJ(CLIENTES) SAVLIB(MILIB) DEV(*SAVF) +
       SAVF(MILIB/SAVNOCHE)

/* Paso 2: Aplicar los cambios del journal desde el ultimo save */
APYJRNCHG JRN(MILIB/QSQJRN) +
          FILE(MILIB/CLIENTES) +
          FROMENT(*LASTSAVE) +
          TOENT(*LAST)

/* Resultado: la tabla queda con todos los datos
   hasta la ultima entrada del journal */

/* Escenario: a las 14:00 alguien ejecuto
   DELETE FROM MILIB.CLIENTES (sin WHERE!) */

/* Deshacer todos los cambios desde las 14:00 */
RMVJRNCHG JRN(MILIB/QSQJRN) +
          FILE(MILIB/CLIENTES) +
          FROMENT(*LAST) +
          TOTIME('2025-01-15-14.00.00')

/* Point-in-time recovery: aplicar hasta un momento especifico */
APYJRNCHG JRN(MILIB/QSQJRN) +
          FILE(MILIB/CLIENTES) +
          FROMENT(*LASTSAVE) +
          TOTIME('2025-01-15-13.59.59')

/* Agregar remote journal al journal existente */
ADDRMTJRN JRN(MILIB/QSQJRN) +
          RMTJRN((MILIB/QSQJRN *RMTSYSNAME SYS_DR)) +
          DLTRCV(*YES) +
          JRNSTATE(*ACTIVE) +
          DELIVERY(*ASYNC)

/* Verificar estado del remote journal */
WRKRMTJRN JRN(MILIB/QSQJRN)

-- Consultar estado de remote journals
SELECT
  JOURNAL_NAME,
  JOURNAL_LIBRARY,
  REMOTE_JOURNAL_TYPE,
  REMOTE_JOURNAL_DELIVERY,
  RELATIONAL_DATABASE_NAME AS SISTEMA_REMOTO,
  REMOTE_JOURNAL_STATE AS ESTADO,
  CURRENT_RECEIVER_NAME,
  CURRENT_RECEIVER_LIBRARY
FROM QSYS2.REMOTE_JOURNAL_INFO
WHERE JOURNAL_NAME = 'QSQJRN'
  AND JOURNAL_LIBRARY = 'MILIB';

/* Swap manual: crear nuevo receiver y cambiar */
CRTJRNRCV JRNRCV(MILIB/RCVR0002) +
          THRESHOLD(500000) +
          TEXT('Receiver 2 para MILIB')

CHGJRN JRN(MILIB/QSQJRN) +
       JRNRCV(MILIB/RCVR0002)
/* El receiver anterior queda "detached" */

/* Eliminar un receiver viejo (verificar que no sea necesario) */
DLTJRNRCV JRNRCV(MILIB/RCVR0001)
/* Solo se puede eliminar si esta detached y
   no es necesario para recovery */

/* Ver informacion del journal */
WRKJRN JRN(MILIB/QSQJRN)
WRKJRNA JRN(MILIB/QSQJRN)

-- Estado de todos los receivers de un journal
SELECT
  JOURNAL_RECEIVER_NAME AS RECEIVER,
  JOURNAL_RECEIVER_LIBRARY AS BIBLIOTECA,
  CASE ATTACHED
    WHEN 'YES' THEN 'ATTACHED (activo)'
    ELSE 'DETACHED'
  END AS ESTADO,
  SIZE AS TAMANO_KB,
  NUMBER_JOURNAL_ENTRIES AS ENTRADAS,
  FIRST_ENTRY_TIMESTAMP AS PRIMERA_ENTRADA,
  LAST_ENTRY_TIMESTAMP AS ULTIMA_ENTRADA
FROM QSYS2.JOURNAL_RECEIVER_INFO
WHERE JOURNAL_NAME = 'QSQJRN'
  AND JOURNAL_LIBRARY = 'MILIB'
ORDER BY FIRST_ENTRY_TIMESTAMP;

-- Tamano total de receivers activos
SELECT
  SUM(SIZE) / 1024 AS TOTAL_MB,
  COUNT(*) AS CANTIDAD_RECEIVERS
FROM QSYS2.JOURNAL_RECEIVER_INFO
WHERE JOURNAL_NAME = 'QSQJRN'
  AND JOURNAL_LIBRARY = 'MILIB';

-- Consulta basica de journal entries
SELECT
  ENTRY_TIMESTAMP,
  JOURNAL_ENTRY_TYPE,
  JOURNAL_CODE,
  JOB_NAME,
  USER_NAME,
  PROGRAM_NAME,
  OBJECT_NAME,
  OBJECT_LIBRARY,
  ENTRY_DATA
FROM TABLE(
  QSYS2.DISPLAY_JOURNAL(
    'MILIB', 'QSQJRN',
    JOURNAL_ENTRY_TYPES   => 'PT,UB,UP,DL',
    JOURNAL_CODES         => 'R',
    OBJECT_NAME           => 'CLIENTES',
    OBJECT_LIBRARY        => 'MILIB',
    STARTING_TIMESTAMP    => '2025-01-15 08:00:00',
    ENDING_TIMESTAMP      => '2025-01-15 17:00:00'
  )
) AS jrn
ORDER BY ENTRY_TIMESTAMP DESC
FETCH FIRST 100 ROWS ONLY;

-- Dashboard completo de actividad semanal
WITH actividad AS (
  SELECT
    DATE(ENTRY_TIMESTAMP) AS FECHA,
    HOUR(ENTRY_TIMESTAMP) AS HORA,
    USER_NAME,
    OBJECT_NAME AS TABLA,
    JOURNAL_ENTRY_TYPE AS TIPO,
    COUNT(*) AS CANTIDAD
  FROM TABLE(
    QSYS2.DISPLAY_JOURNAL(
      'MILIB', 'QSQJRN',
      JOURNAL_CODES       => 'R',
      JOURNAL_ENTRY_TYPES => 'PT,UP,DL',
      STARTING_TIMESTAMP  => CURRENT_DATE - 7 DAYS
    )
  ) AS jrn
  GROUP BY DATE(ENTRY_TIMESTAMP), HOUR(ENTRY_TIMESTAMP),
           USER_NAME, OBJECT_NAME, JOURNAL_ENTRY_TYPE
)
SELECT FECHA, HORA, USER_NAME, TABLA,
  SUM(CASE WHEN TIPO = 'PT' THEN CANTIDAD ELSE 0 END) AS INSERTS,
  SUM(CASE WHEN TIPO = 'UP' THEN CANTIDAD ELSE 0 END) AS UPDATES,
  SUM(CASE WHEN TIPO = 'DL' THEN CANTIDAD ELSE 0 END) AS DELETES,
  SUM(CANTIDAD) AS TOTAL
FROM actividad
GROUP BY FECHA, HORA, USER_NAME, TABLA
ORDER BY FECHA DESC, HORA, TOTAL DESC;

/* Setup optimo de journaling para produccion */
CRTJRNRCV JRNRCV(PRODLIB/RCVR0001) +
          THRESHOLD(1000000) +
          TEXT('Receiver inicial produccion')

CRTJRN JRN(PRODLIB/QSQJRN) +
       JRNRCV(PRODLIB/RCVR0001) +
       MNGRCV(*SYSTEM) +
       DLTRCV(*NO) +
       RCVSIZOPT(*MAXOPT2) +
       MINENTDTA(*NONE) +
       TEXT('Journal produccion')

/* Journalear TODAS las tablas de la biblioteca */
STRJRNPF FILE(PRODLIB/*ALL) +
         JRN(PRODLIB/QSQJRN) +
         IMAGES(*BOTH) +
         OMTJRNE(*OPNCLO)