(Puedes leer esta publicacion en cualquier idioma haciendo click en esta aplicación)
*You can read this article in Spanish more down below in this same publish. (Puedes leer este artículo en español más abajo en esta misma publicación)
The most common IBM i (RPGLE/SQLRPGLE) nightly batch errors that can cost you… and how to prevent them
Nightly batch processes in IBM i (AS400) are critical
as they consolidate transactions, update reports, and prepare data for the next
business day. However, execution issues can impact system performance and
stability. Here are the most frequent problems and their solutions.
1️-Record and File Locks (Record
Locks & File Locks)
Issue:
- A
job locks a record or file, preventing other processes from accessing the
data.
- SQLRPGLE
programs using commit/rollback may leave records locked if the
transaction doesn’t complete correctly.
Solution:
Use WITH NC (No
Commit) in SQL queries when transactions are unnecessary.
Release locks using STRDBMON to identify problematic sessions.
Implement automatic retry mechanisms
(MONITOR/ON-ERROR) for locked records.
Example of SQL without unnecessary locks:
SELECT * FROM customers WITH NC;
2️-Poor SQL and RPGLE Performance
Issue:
- SQL
queries scanning large datasets without proper indexing.
- RPGLE
programs using inefficient loops over large files.
- Jobs
loading excessive data into memory.
Solution:
Analyze the execution plan (EXPLAIN PLAN) and add indexes to key tables.
===============
Example: Using EXPLAIN PLAN in IBM i (SQLRPGLE) for Performance Optimization
A-Creating a Temporary Table to Store Execution Plans
Before using EXPLAIN PLAN, we need a table to store the
analysis results. If you don’t have one, create it like this:
CREATE TABLE QTEMP.EXPLAIN_PLAN (
QUERYNO INT,
SEQNO INT,
OPERATION
CHAR(20),
OBJECT_NAME
CHAR(30),
INDEX_NAME
CHAR(30),
ESTIMATED_COST
DECIMAL(10,2)
);
B.-Generating the Execution Plan for a Query
Let’s analyze a query that retrieves recent orders:
EXPLAIN PLAN SET STATEMENT_ID = 'PLAN1'
FOR
SELECT * FROM orders WHERE date >= CURRENT DATE - 30
DAYS;
C.-Viewing the Execution Plan Results
After running EXPLAIN PLAN, we can examine its output:
SELECT * FROM TABLE(QSYS2.EXPLAIN_FORMAT('PLAN1')) ORDER BY
SEQNO;
Key Insights from
the Results:
- Operation:
Whether an INDEX SCAN or TABLE SCAN was used.
- Object
Name: The table being analyzed.
- Index
Name: Shows if an index was applied.
- Estimated
Cost: The estimated resource usage.
D-Optimization: Adding an Index
to Improve Query Performance
If EXPLAIN PLAN shows a TABLE SCAN, we can optimize the
query by creating an index on the date column:
CREATE INDEX idx_orders_date ON orders (date);
Then, we rerun the query and EXPLAIN PLAN to check if it now
uses INDEX SCAN.
Conclusion
EXPLAIN PLAN
helps detect slow queries and improve performance by adding indexes or
restructuring SQL.
Minimizing TABLE SCAN with proper indexing significantly enhances
execution speed on IBM i.
================
3.-Use OPTIMIZE FOR n ROWS to limit the number of fetched
rows.
Process data in small batches instead of handling large volumes in
memory.
Optimized SQL Example:
SELECT * FROM transactions WHERE date >= CURRENT DATE - 1
DAY OPTIMIZE FOR 100 ROWS;
4-Insufficient Disk Space (ASP
Usage High)
Issue:
- Large
temporary data volumes consume disk space (ASP - Auxiliary Storage Pool).
- Work
files or logs are not cleaned up after use.
- Jobs
generating uncontrolled output files.
Solution:
Use SQL DELETE with commit to clean up old data in batches.
Automate log cleanup using CLP (RMVLNK or DLTF).
Monitor disk space (WRKSYSSTS or DSPSYSSTS) before batch execution.
Example of automatic log cleanup:
DELETE FROM logs WHERE date < CURRENT DATE - 30 DAYS;
5-Concurrency Issues (Multiple
Jobs Updating the Same Data)
Issue:
- Multiple
jobs updating the same records simultaneously.
- Data
inconsistencies due to update conflicts.
Solution:
Sequence job execution based on priority.
Implement exclusive locks (LOCK TABLE or CHGOBJ) when needed.
Use temporary files (QTEMP) to minimize impact on production tables.
Concurrency Control Example:
LOCK TABLE inventory IN EXCLUSIVE MODE;
UPDATE inventory SET stock = stock - 1 WHERE product_id =
123;
COMMIT;
UNLOCK TABLE inventory;
6-Jobs
Stuck in MSGW (Message Waiting) State
Issue:
- A
job waits for manual intervention (*MSGW), halting the entire process.
- SQL
or RPGLE errors generate messages that block execution.
Solution:
Configure error handling (MONITOR/ON-ERROR) in RPGLE to prevent manual
stops.
Use QSYSOPR to capture and auto-respond to messages (QCMDCHK).
Monitor jobs using QZRCSRVS or WRKACTJOB to avoid long-running stuck
processes.
Error Handling in RPGLE Example:
Monitor;
Exec SQL UPDATE
orders SET status = 'CLOSED' WHERE date < CURRENT DATE - 1 DAY;
On-Error;
Dsply 'Error in
order closure';
EndMon;
Conclusion: Implementing Best Practices to Avoid Failures
To minimize risks in IBM i nightly processes, follow these
best practices:
Optimize SQL with indexes and OPTIMIZE FOR.
Avoid locks with WITH NC and controlled commits.
Monitor disk space and remove unnecessary files.
Ensure concurrency control with LOCK TABLE when required.
Automate error handling to prevent manual interventions.
Expected Outcome: Fewer failures, reduced execution
time, and greater system stability.
If you
find it interesting, share it or forward it to a friend. Knowledge is valuable,
Share it!
Share
your experience in the comments!
Until Next time!
---------------
Los errores más comunes en cierres nocturnos de
IBM i (RPGLE/SQLRPGLE) que pueden costarte caro… y cómo prevenirlos
Los
procesos de cierre nocturno en sistemas IBM i (AS400) suelen ser
críticos, ya que consolidan transacciones, actualizan reportes y preparan datos
para el siguiente día. Sin embargo, pueden presentar problemas de ejecución que
afectan la operatividad del negocio. A continuación, te detallo los errores más
comunes y cómo prevenirlos.
1️-Bloqueos de Registros y Archivos (Record Locks
& File Locks)
Problema:
- Un trabajo mantiene bloqueado
un registro o archivo, impidiendo que otros procesos accedan a los datos.
- Los programas SQLRPGLE que
usan commit/rollback pueden dejar registros bloqueados si la
transacción no finaliza correctamente.
Solución:
Usar WITH NC (No Commit) en consultas
SQL cuando no se requiera transaccionar.
Liberar bloqueos con STRDBMON para detectar sesiones problemáticas.
Implementar reintentos automáticos (MONITOR/ON-ERROR) si un registro
está bloqueado.
Ejemplo de SQL sin bloqueos
innecesarios:
SELECT * FROM clientes WITH NC;
2️-Bajo Rendimiento en Consultas SQL y Procesos
RPGLE
Problema:
- Consultas SQL que recorren
grandes volúmenes de datos sin índices adecuados.
- RPGLE con bucles innecesarios
que procesan millones de registros.
- Procesos que cargan demasiada
información en memoria.
Solución:
1.-Revisar el plan de ejecución (EXPLAIN PLAN) y agregar índices en
tablas críticas.
Ejemplo
práctico de cómo usar EXPLAIN PLAN en IBM i (AS400) para analizar el
rendimiento de una consulta SQL y optimizarla si es necesario.
==============
Ejemplo:
Uso de EXPLAIN PLAN en IBM i (SQLRPGLE)
A-Crear una Tabla Temporal para Almacenar el Plan
de Ejecución
Antes de
ejecutar EXPLAIN PLAN, es necesario tener una tabla donde se guardará la
información del análisis. Si no tienes una, puedes crearla así:
CREATE
TABLE QTEMP.EXPLAIN_PLAN (
QUERYNO INT,
SEQNO INT,
OPERATION
CHAR(20),
OBJECT_NAME
CHAR(30),
INDEX_NAME
CHAR(30),
ESTIMATED_COST DECIMAL(10,2)
);
B-Generar el Plan de Ejecución de una Consulta
Supongamos
que queremos analizar una consulta que obtiene pedidos recientes:
EXPLAIN PLAN SET STATEMENT_ID = 'PLAN1'
FOR
SELECT * FROM pedidos WHERE fecha >= CURRENT DATE - 30
DAYS;
C-Consultar el Plan de Ejecución Generado
Después de
ejecutar EXPLAIN PLAN, podemos analizar su resultado con esta consulta:
SELECT * FROM TABLE(QSYS2.EXPLAIN_FORMAT('PLAN1')) ORDER BY
SEQNO;
Qué
buscar en los resultados:
- Operation: Indica si se usó un índice
(INDEX SCAN) o un recorrido completo (TABLE SCAN).
- Object Name: Muestra la tabla analizada.
- Index Name: Si hay un índice en uso, se
muestra aquí.
- Estimated Cost: Costo estimado en términos de
recursos del sistema.
D-Optimización: Agregar un Índice para Mejorar
Rendimiento
Si EXPLAIN
PLAN indica que se está haciendo un TABLE SCAN, podemos optimizar la consulta
creando un índice en la columna fecha:
CREATE
INDEX idx_pedidos_fecha ON pedidos (fecha);
Y luego,
volvemos a ejecutar la consulta y EXPLAIN PLAN para verificar si ahora usa el
índice (INDEX SCAN).
Conclusión
EXPLAIN
PLAN ayuda a
detectar consultas lentas y a mejorar su rendimiento mediante la creación de
índices o reescritura de SQL.
Reducir TABLE SCAN usando índices optimizados acelera la ejecución de
procesos en IBM i. 🚀
=================
3.-Usar OPTIMIZE FOR n ROWS en SQL para limitar la cantidad de datos recuperados.
Procesar datos en bloques pequeños en lugar de manejar grandes volúmenes
en memoria.
Ejemplo
de SQL optimizado:
SELECT *
FROM transacciones WHERE fecha >= CURRENT DATE - 1 DAY OPTIMIZE FOR 100
ROWS;
4-Falta de Espacio en Disco (ASP Usage High)
Problema:
- Grandes volúmenes de datos
temporales ocupan espacio en el disco (ASP - Auxiliary Storage Pool).
- Archivos de trabajo o logs no
se eliminan después de usarse.
- Jobs que generan archivos de
salida sin control de crecimiento.
Solución:
Usar SQL DELETE con commit para limpiar datos innecesarios en lotes.
Automatizar eliminación de logs
antiguos con un programa CLP (RMVLNK o DLTF).
Monitorear espacio en disco (WRKSYSSTS o DSPSYSSTS) antes del cierre
nocturno.
Ejemplo
de eliminación automática de archivos viejos:
DELETE FROM logs WHERE fecha < CURRENT DATE - 30 DAYS;
5-Problemas
de Concurrencia (Multiple Jobs Affecting the Same Data)
Problema:
- Varios trabajos nocturnos
intentan actualizar los mismos registros al mismo tiempo.
- Se generan inconsistencias
en los datos por conflictos de actualización.
Solución:
Secuenciar la ejecución de procesos según prioridad.
Implementar bloqueos exclusivos (LOCK TABLE o CHGOBJ) si es necesario.
Usar archivos temporales (QTEMP) para minimizar impacto en tablas
principales.
Ejemplo
de control de concurrencia en SQL:
LOCK TABLE inventario IN EXCLUSIVE MODE;
UPDATE inventario SET stock = stock - 1 WHERE producto_id =
123;
COMMIT;
UNLOCK TABLE inventario;
6-Jobs que No Terminan o Quedan en Estado MSGW
(Message Waiting)
Problema:
- Un trabajo espera una
respuesta manual (*MSGW), deteniendo el proceso nocturno.
- Errores en SQL o en RPGLE
lanzan mensajes de error que bloquean la ejecución.
Solución:
Configurar manejo de errores
(MONITOR/ON-ERROR) en RPGLE para evitar interrupciones.
Usar QSYSOPR para capturar mensajes y responder automáticamente (QCMDCHK).
Monitorear procesos con QZRCSRVS o WRKACTJOB para evitar jobs colgados.
Ejemplo
de manejo de errores en RPGLE:
Monitor;
Exec SQL UPDATE pedidos SET estado =
'CERRADO' WHERE fecha < CURRENT DATE - 1 DAY;
On-Error;
Dsply 'Error en cierre de pedidos';
EndMon;
Conclusión:
Implementar Buenas Prácticas para Evitar Fallos
Para
minimizar riesgos en los procesos nocturnos en IBM i, sigue estas prácticas:
Optimiza
SQL con índices y
OPTIMIZE FOR.
Evita bloqueos con WITH NC y commits controlados.
Monitorea espacio en disco y elimina archivos temporales.
Asegura la concurrencia con LOCK TABLE cuando sea necesario.
Automatiza manejo de errores para evitar interrupciones manuales.
Resultado
esperado: Menos
fallos en cierre nocturno, reducción de tiempo de ejecución y mayor estabilidad
del sistema.
Si te pareció interesante, reenvíalo a un amigo haciendo click en el sobrecito que está al final del artículo o en el enlace de Linkedin que está encima de este mensaje. El conocimiento es valioso, compártelo.
¡Comparte tu experiencia en los comentarios!
¡Hasta la próxima!
