Administracion de Db2

-- Listar todas las tablas de una biblioteca
SELECT TABLE_NAME, TABLE_TYPE, ROW_COUNT,
       DATA_SIZE, COLUMN_COUNT
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
ORDER BY TABLE_NAME;

-- Estructura de una tabla (equivale a d en psql)
SELECT
  ORDINAL_POSITION AS POS,
  COLUMN_NAME,
  DATA_TYPE,
  COALESCE(CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION) AS LARGO,
  IS_NULLABLE,
  COLUMN_DEFAULT,
  CCSID
FROM QSYS2.SYSCOLUMNS
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_NAME = 'CLIENTES'
ORDER BY ORDINAL_POSITION;

-- Indices de una tabla
SELECT
  INDEX_NAME,
  INDEX_TYPE,
  COLUMN_NAMES,
  IS_UNIQUE,
  NUMBER_ROWS
FROM QSYS2.SYSINDEXSTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_NAME = 'CLIENTES';

-- Foreign keys de una tabla
SELECT
  CONSTRAINT_NAME,
  TABLE_NAME AS TABLA_ORIGEN,
  COLUMN_NAME,
  REFERENCED_TABLE_NAME AS TABLA_DESTINO
FROM QSYS2.SYSKEYCST k
JOIN QSYS2.SYSREFCST r
  ON k.CONSTRAINT_NAME = r.CONSTRAINT_NAME
WHERE k.TABLE_SCHEMA = 'MILIB';

-- Generar el DDL SQL de una tabla existente
-- Util para documentar o migrar objetos
CALL SYSTOOLS.GENERATE_SQL(
  DATABASE_OBJECT_NAME    => 'CLIENTES',
  DATABASE_OBJECT_LIBRARY => 'MILIB',
  DATABASE_OBJECT_TYPE    => 'TABLE',
  DATABASE_SOURCE_FILE_NAME => 'QSQLSRC',
  DATABASE_SOURCE_FILE_LIBRARY => 'QTEMP',
  CREATE_OR_REPLACE_OPTION => '1',
  STATEMENT_FORMATTING_OPTION => '0'
);
-- El DDL se guarda en QTEMP/QSQLSRC

-- Verificar PTF groups instalados
SELECT GROUP_PTF_ID, PTF_GROUP_DESCRIPTION,
       PTF_GROUP_LEVEL, PTF_GROUP_STATUS
FROM SYSTOOLS.GROUP_PTF_CURRENCY
ORDER BY GROUP_PTF_ID;

-- Enviar un email desde SQL
CALL SYSTOOLS.SENDMAIL(
  SMTP_TO      => 'admin@empresa.com',
  CONTENT_TYPE => 'text/plain',
  SUBJECT      => 'Alerta: tabla CLIENTES supero 1M filas',
  MAIL_BODY    => 'La tabla MILIB/CLIENTES tiene mas de 1 millon de filas.'
);

-- Crear un schema completo (recomendado)
CREATE SCHEMA APPVENTAS
  AUTHORIZATION QSECOFR;
-- Esto crea: biblioteca APPVENTAS, journal QSQJRN,
-- receiver QSQJRN0001, catalog views

-- Listar todos los schemas del sistema
SELECT SCHEMA_NAME, SCHEMA_OWNER, SYSTEM_SCHEMA_NAME,
       SCHEMA_SIZE, NUMBER_TABLES, NUMBER_VIEWS
FROM QSYS2.SYSSCHEMAS
WHERE SCHEMA_NAME NOT LIKE 'Q%'
  AND SCHEMA_NAME NOT LIKE 'SYS%'
ORDER BY SCHEMA_NAME;

-- Cambiar el schema por defecto de la sesion
SET SCHEMA APPVENTAS;

-- Path de busqueda de schemas
SET PATH APPVENTAS, MILIB, QSYS2;

-- Eliminar un schema (CUIDADO: borra todo el contenido)
-- DROP SCHEMA APPTEST RESTRICT;
-- RESTRICT falla si el schema no esta vacio

/* Crear biblioteca */
CRTLIB LIB(APPVENTAS) TEXT('Aplicacion de Ventas')

/* Ver objetos en una biblioteca */
DSPLIB LIB(APPVENTAS)

/* Listar bibliotecas del usuario */
DSPLIBL

/* Agregar biblioteca a la lista */
ADDLIBLE LIB(APPVENTAS) POSITION(*FIRST)

-- Contar objetos por tipo en una biblioteca
SELECT
  TABLE_TYPE,
  COUNT(*) AS CANTIDAD,
  SUM(DATA_SIZE) AS TAMANO_TOTAL
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
GROUP BY TABLE_TYPE
ORDER BY CANTIDAD DESC;

-- Listar todos los procedures y functions
SELECT ROUTINE_NAME, ROUTINE_TYPE,
       EXTERNAL_LANGUAGE, SQL_DATA_ACCESS,
       NUMBER_OF_PARAMETERS
FROM QSYS2.SYSROUTINES
WHERE ROUTINE_SCHEMA = 'MILIB'
ORDER BY ROUTINE_TYPE, ROUTINE_NAME;

-- Listar triggers asociados a tablas
SELECT
  TRIGGER_NAME,
  EVENT_OBJECT_TABLE AS TABLA,
  ACTION_TIMING AS WHEN_FIRES,
  EVENT_MANIPULATION AS EVENTO
FROM QSYS2.SYSTRIGGERS
WHERE EVENT_OBJECT_SCHEMA = 'MILIB'
ORDER BY EVENT_OBJECT_TABLE;

/* Reorganizar tabla (equivale a VACUUM en PostgreSQL) */
RGZPFM FILE(MILIB/CLIENTES) +
       RBDACCPTH(*YES)

/* RBDACCPTH(*YES) tambien reconstruye indices
   y actualiza estadisticas del catalog */

/* Reorganizar con keyfile especifico */
RGZPFM FILE(MILIB/PEDIDOS) +
       KEYFILE(MILIB/PEDIDOS1) +
       RBDACCPTH(*YES)

/* Ver filas eliminadas antes de reorganizar */
DSPFD FILE(MILIB/CLIENTES) TYPE(*ATTR)

-- Tablas con alto porcentaje de filas eliminadas
SELECT
  TABLE_SCHEMA, TABLE_NAME,
  ROW_COUNT AS FILAS_ACTIVAS,
  NUMBER_DELETED_ROWS AS FILAS_ELIMINADAS,
  ROW_COUNT + NUMBER_DELETED_ROWS AS FILAS_FISICAS,
  DECIMAL(NUMBER_DELETED_ROWS * 100.0
    / NULLIF(ROW_COUNT + NUMBER_DELETED_ROWS, 0), 5, 1)
    AS PCT_DESPERDICIO,
  DATA_SIZE / 1024 AS TAMANO_KB
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND NUMBER_DELETED_ROWS > 0
ORDER BY PCT_DESPERDICIO DESC;

-- Tablas mas grandes de la biblioteca
SELECT TABLE_NAME,
       ROW_COUNT AS FILAS,
       DATA_SIZE / 1048576 AS TAMANO_MB,
       COLUMN_COUNT AS COLUMNAS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_TYPE = 'T'
ORDER BY DATA_SIZE DESC
FETCH FIRST 20 ROWS ONLY;

/* Cambiar tamano de registro (agregar columna en DDS) */
CHGPF FILE(MILIB/CLIENTES) SRCFILE(MILIB/QDDSSRC)

/* Cambiar el tamano maximo de la tabla */
CHGPF FILE(MILIB/PEDIDOS) SIZE(*NOMAX)

/* Cambiar CCSID de un archivo */
CHGPF FILE(MILIB/CLIENTES) CCSID(1208)

/* Para tablas DDL, usar ALTER TABLE */

-- Agregar columna
ALTER TABLE MILIB.CLIENTES
  ADD COLUMN TELEFONO VARCHAR(20);

-- Cambiar tipo de dato
ALTER TABLE MILIB.CLIENTES
  ALTER COLUMN NOMBRE SET DATA TYPE VARCHAR(200);

-- Agregar constraint
ALTER TABLE MILIB.CLIENTES
  ADD CONSTRAINT UK_CLIENTES_EMAIL
  UNIQUE (EMAIL);

-- Agregar foreign key
ALTER TABLE MILIB.PEDIDOS
  ADD CONSTRAINT FK_PEDIDOS_CLIENTE
  FOREIGN KEY (CLIENTE_ID)
  REFERENCES MILIB.CLIENTES (ID);

-- Renombrar tabla
RENAME TABLE MILIB.CLIENTES_VIEJO TO CLIENTES_BACKUP;

-- Otorgar permisos sobre una tabla
GRANT SELECT, INSERT, UPDATE ON MILIB.CLIENTES
  TO USRVENTA;

-- Otorgar todos los permisos
GRANT ALL ON MILIB.PEDIDOS
  TO USRADMIN;

-- Revocar permisos
REVOKE DELETE ON MILIB.CLIENTES
  FROM USRVENTA;

-- Permisos sobre un schema completo
GRANT SELECT ON SCHEMA MILIB
  TO USRLECTURA;

-- Permisos sobre procedure
GRANT EXECUTE ON PROCEDURE MILIB.CREAR_PEDIDO
  TO USRVENTA;

-- Ver permisos actuales
SELECT GRANTEE, PRIVILEGE_TYPE, IS_GRANTABLE
FROM QSYS2.SYSTABLEPRIVILEGES
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_NAME = 'CLIENTES'
ORDER BY GRANTEE;

/* Otorgar autoridad a nivel de objeto (complementario a GRANT) */
GRTOBJAUT OBJ(MILIB/CLIENTES) OBJTYPE(*FILE) +
          USER(USRVENTA) +
          AUT(*CHANGE)

/* Autoridades de IBM i equivalentes:
   *USE     = SELECT
   *CHANGE  = SELECT + INSERT + UPDATE + DELETE
   *ALL     = Todos los permisos + alterar estructura
   *EXCLUDE = Sin acceso
*/

/* Revocar */
RVKOBJAUT OBJ(MILIB/CLIENTES) OBJTYPE(*FILE) +
          USER(USRVENTA) AUT(*ALL)

/* Ver autoridades de un objeto */
DSPOBJAUT OBJ(MILIB/CLIENTES) OBJTYPE(*FILE)

-- Tablas sin journaling (riesgo de recovery)
SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT
FROM QSYS2.SYSTABLESTAT t
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_TYPE = 'T'
  AND NOT EXISTS (
    SELECT 1 FROM QSYS2.JOURNAL_INFO j
    WHERE j.JOURNAL_OBJECT_NAME = t.TABLE_NAME
      AND j.JOURNAL_OBJECT_LIBRARY = t.TABLE_SCHEMA
  )
ORDER BY ROW_COUNT DESC;

-- Tablas sin primary key definida
SELECT TABLE_SCHEMA, TABLE_NAME, ROW_COUNT
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
  AND TABLE_TYPE = 'T'
  AND TABLE_NAME NOT IN (
    SELECT TABLE_NAME FROM QSYS2.SYSCONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = 'MILIB'
      AND CONSTRAINT_TYPE = 'PRIMARY KEY'
  )
ORDER BY TABLE_NAME;

-- Indices no utilizados en los ultimos 6 meses
SELECT INDEX_NAME, TABLE_NAME,
       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;

-- Espacio usado por biblioteca
SELECT TABLE_SCHEMA,
       COUNT(*) AS OBJETOS,
       SUM(DATA_SIZE) / 1048576 AS TAMANO_TOTAL_MB,
       SUM(NUMBER_DELETED_ROWS) AS TOTAL_FILAS_ELIMINADAS
FROM QSYS2.SYSTABLESTAT
WHERE TABLE_SCHEMA = 'MILIB'
GROUP BY TABLE_SCHEMA;

-- Jobs con SQL activo en este momento
SELECT
  JOB_NAME,
  AUTHORIZATION_NAME AS USUARIO,
  FUNCTION_TYPE,
  FUNCTION AS PROGRAMA,
  SQL_STATEMENT_STATUS AS ESTADO_SQL,
  ELAPSED_CPU_PERCENTAGE AS CPU_PCT,
  ELAPSED_TOTAL_DISK_IO_COUNT AS IO_DISCO,
  SUBSTR(SQL_STATEMENT_TEXT, 1, 150) AS SQL_TEXT
FROM TABLE(QSYS2.ACTIVE_JOB_INFO(
  DETAILED_INFO => 'ALL'
)) AS j
WHERE SQL_STATEMENT_TEXT IS NOT NULL
  AND SQL_STATEMENT_TEXT <> ''
ORDER BY ELAPSED_CPU_PERCENTAGE DESC
FETCH FIRST 20 ROWS ONLY;

-- Locks y waits activos
SELECT
  JOB_NAME,
  LOCK_STATE,
  LOCK_OBJECT_NAME AS OBJETO,
  LOCK_OBJECT_LIBRARY AS BIBLIOTECA,
  LOCK_TYPE,
  LOCK_SCOPE
FROM QSYS2.OBJECT_LOCK_INFO
WHERE LOCK_OBJECT_LIBRARY = 'MILIB'
  AND LOCK_STATE <> 'HELD'
ORDER BY JOB_NAME;

-- Record locks activos
SELECT
  SYSTEM_TABLE_SCHEMA, SYSTEM_TABLE_NAME,
  TABLE_LOCK_STATE, ROW_LOCK_STATE,
  JOB_NAME, LOCK_COUNT
FROM QSYS2.SQL_OPEN_CURSOR_INFO
WHERE SYSTEM_TABLE_SCHEMA = 'MILIB'
  AND ROW_LOCK_STATE IS NOT NULL;

-- Usar GENERATE_SQL para obtener el DDL equivalente
CALL SYSTOOLS.GENERATE_SQL(
  DATABASE_OBJECT_NAME    => 'CLIENTES',
  DATABASE_OBJECT_LIBRARY => 'MILIB',
  DATABASE_OBJECT_TYPE    => 'TABLE',
  DATABASE_SOURCE_FILE_NAME => 'QSQLSRC',
  DATABASE_SOURCE_FILE_LIBRARY => 'QTEMP',
  CREATE_OR_REPLACE_OPTION => '1'
);

-- Leer el DDL generado
SELECT SRCDTA FROM QTEMP.QSQLSRC ORDER BY SRCSEQ;

-- DDS original (CRTPF FILE(MILIB/CLIENTES))
-- A R CLIENTESR
-- A  ID         10S 0       TEXT('ID Cliente')
-- A  NOMBRE     50A         TEXT('Nombre')
-- A  SALDO      11S 2       TEXT('Saldo')
-- A  ESTADO      1A         TEXT('Estado A/I')
-- A K ID

-- DDL equivalente:
CREATE TABLE MILIB.CLIENTES (
  ID      INTEGER NOT NULL,
  NOMBRE  VARCHAR(50) NOT NULL,
  SALDO   DECIMAL(11,2) DEFAULT 0,
  ESTADO  CHAR(1) DEFAULT 'A'
    CHECK (ESTADO IN ('A', 'I')),
  CONSTRAINT PK_CLIENTES PRIMARY KEY (ID)
);

-- Logical file con seleccion -> VIEW + INDEX
-- DDS: CRTLF FILE(MILIB/CLIENTESV) SELECT/OMIT ESTADO='A'
-- DDL:
CREATE VIEW MILIB.V_CLIENTES_ACTIVOS AS
  SELECT * FROM MILIB.CLIENTES WHERE ESTADO = 'A';

CREATE INDEX MILIB.IDX_CLIENTES_ESTADO
  ON MILIB.CLIENTES (ESTADO);