IBM i Technical Services

Restoring libraries to entirely different names in the IBM i world might seem straightforward, like using RSTLIB SAVLIB(PRODLIB) RSTLIB(TESTLIB) to create a test environment or clone production. However, this seemingly harmless task can lead to hidden headaches; cross-logical files stubbornly pointing to the original library, SQL temporary objects that fail to restore correctly, and unexpected issues with file triggers, stored procedures, and journals. I’ll walk you through exactly what can go wrong, share real examples with clear diagrams, and most importantly, show you the powerful DFRID(*YES) technique that automatically solves many of these issues. By the end of this short lesson, you’ll know how to avoid those annoying restore surprises and create clean test or recovery environments with much more confidence. Let’s dive in!

An IBM i stored procedure is a user-defined program or routine stored in the IBM i system's database (DB2 for i) that can be called from a client application, another program, or a database interface to perform a specific task or set of operations. It encapsulates business logic or database operations, allowing for modularity, reusability, and improved performance in applications running on the IBM i platform.

To list all stored procedures on IBM i, run this SQL query against the system catalog: SELECT ROUTINESCHEMA AS SCHEMA_NAME, ROUTINENAME AS PROCEDURE_NAME, ORIGIN, PARM_COUNT AS PARAMETER_COUNT, LANGUAGE FROM QSYS2.SYSROUTINES WHERE ROUTINETYPE = 'P' AND ROUTINESCHEMA NOT LIKE 'SYS%' ORDER BY SCHEMA_NAME, PROCEDURE_NAME;

Methods to Enable/Disable Triggers Use the Change Physical File Trigger (CHGPFTRG) command or the ALTER TRIGGER SQL statement. Using CHGPFTRG Command; you can enable or disable named triggers or all triggers for a file using the CHGPFTRG command. Using ALTER TRIGGER SQL Statement; you can also disable triggers using the ALTER TRIGGER SQL statement. The third option is using System i Navigator; you can enable or disable triggers.

Every IBM i shop eventually faces the same deceptively simple question: *“How do we automatically monitor all USRPRF profiles and send a warning email listing every user whose password is about to expire?” It sounds straightforward… until you try to build it.

Password Expiration Command (PWDEXPMON.CMD) is: User-friendly command interface Optional DAYS parameter (default: 7)

Password Expiration Control (PWDEXPMON.pgm.clle) is: CL wrapper for command processing Error handling and message routing

The Password Expiration Monitor is a comprehensive IBM i security tool that monitors all user profiles (`*USRPRF`) and sends email warnings when passwords are approaching expiration. This helps maintain security compliance and prevents unexpected password expirations that could disrupt operations. ✅ Monitors all IBM i user profiles automatically ✅ Configurable warning period (default: 7 days) ✅ Email notifications with detailed user information ✅ Includes both enabled and disabled accounts ✅ Shows last sign-on dates for context ✅ Comprehensive job log messages ✅ Can be scheduled for automatic execution ✅ Handles edge cases (expired today, never signed on, etc.)

This lesson guides students through the design, architecture, and implementation of a production-ready IBM i Service Program (*SRVPGM). Using modern RPGIV (Free-Form), students will learn how to encapsulate reusable business logic that queries the DB2 for IBM-i system catalog (QSYS2.USER_INFO) to identify, sort, and format user profiles with expiring passwords.

Automated job scheduler setup Configures daily execution at 8:00 AM Includes both 7-day and 14-day warning schedules

Key Features: ✅ Automated Monitoring - Queries all user profiles using SQL services ✅ Email Notifications - Detailed reports with user info, expiration dates, last sign-on ✅ Flexible Scheduling - Can be run manually or scheduled automatically ✅ Error Handling - Comprehensive error checking and job log integration ✅ Customizable - Easy to modify email recipients, warning periods, filters ✅ Production Ready - Includes edge case handling, SQL injection prevention, resource cleanup

IBM i Password Expiry Code Review The Problem: Overnight password expiration causes immediate support headaches and user panics. The Scope: A comprehensive code review examining password expiration on IBM i from three distinct technical perspectives: Utilizing native commands. Implementing logic within Control Language (CL). Managing logic correctly inside Report Program Generator (RPG) programs. The Goal: To understand the underlying mechanics of password expiration for auditing existing legacy code or developing new solutions, ultimately reducing administrative friction.

Mapepire is a modern, open‑source database access layer for IBM i that allows applications to connect to Db2 for i using secure WebSockets and JSON, instead of traditional ODBC or JDBC drivers. It runs as a server (daemon) on the IBM i system and acts as a gateway between Db2 for i and modern application runtimes.

If you’ve ever tried to connect modern apps to IBM i, you’ve probably hit at least one of these: * ODBC setup that feels like it came from 1998 * JDBC drivers that work, but take some effort * REST layers that need a small army to configure And then along comes Mapepire to solve these issues!!!

If you're building modern applications that need to connect to Db2 for i, Mapepire is a real game changer. It runs a lightweight daemon directly on your IBM i and allows Node.js, Python, Java, and .NET apps to connect securely over WebSockets. Say goodbye to clunky ODBC or JDBC drivers on the client side.