What happens when you replace twenty years of ad-hoc scripts with one framework
Every IBM i shop I've worked in has a folder of CL programs that do FTP. Usually it's dozens of them.
Some were written when the current network admin was in high school. Some have the password sitting in clear text, one line below the FTP command. A lot of them have run fine for fifteen years, and nobody touches them because changing them feels riskier than leaving them alone. And every so often one dies quietly overnight and nobody notices until the morning report doesn't show up.
That's where this project started. There was no modernization strategy behind it and no compliance push. I just had a growing sense that what we had was going to hurt us eventually, and probably at a bad time.
The pile
If you've lived with this, you know what it looks like. If you haven't, here's roughly what you find when you go digging:
- Clear-text passwords in CL source members, readable by anyone with authority to the source file.
- No audit trail. You can't tell who wrote a script or who last changed the destination path. You display the source and hope the comments are accurate.
- No record of what ran when. If Tuesday's file didn't arrive, you end up reading spool files and piecing it together from raw FTP output.
- No retries. If the remote server refused the connection once, the job failed and somebody got an email at 3 a.m., assuming the email worked.
- No recovery. If the transfer crashed partway through, the production file was already half overwritten.
- No way to turn off a broken interface without editing production source, so in practice nobody ever turned anything off.
Everyone knew about it, and everyone had a story about the time it went wrong. Nobody had the time to fix all of it.
Deciding to actually do it
It started as a small fix. One of our FTP pulls from a mainframe kept failing on Sundays. I opened the CL, fixed the timeout, and when I went to save it I noticed the password right there in the source. It wasn't hashed or pulled from a data area. It was just typed into the CL, in a source file that half the shop could read because of how the library had been set up years earlier.
I fixed that one and opened the next program, and it had the same problem.
When I looked at the full list, there were dozens of them, going to a mainframe, to UNIX boxes, to a cloud SFTP gateway and to vendor SFTP servers. Each one handled errors its own way, and each one had its own password in the source.
At some point you stop fixing the individual programs and start thinking about replacing the whole category. That was the point for me.
Why FTP, and not an API
If you don't work on IBM i, the obvious question is why keep FTP at all. Why not replace it with REST calls or a message queue?
The answer is that I don't control the other end.
The systems on the other side of these transfers are a mainframe that predates me, a few UNIX boxes owned by other teams, a cloud SFTP gateway, and vendors whose integration options amount to "here's your SFTP login." None of them are going to build a REST API for me. Some of them are hard enough to get to rotate a password. FTP and SFTP weren't a design choice on my side. They were the only option the other side offered.
So the goal was never to get off FTP. It was to stop treating FTP as the place where anything goes. Where I control both ends, like internal services, I use APIs, and that's a much easier problem. This project was about the transfers where FTP is the only option, and where the difference between reliable and unreliable came down to whatever that CL program happened to do in 2011.
That distinction did more than anything else to keep the scope sane. I wasn't trying to compete with modern integration patterns. I was trying to make the legacy pattern I was stuck with behave a bit more like one.
What these transfers actually move
"FTP framework" sounds abstract until you look at what goes through it.
The ERP runs on the IBM i. Around it there's a mainframe that's still the system of record for some older transactional data, a warehouse management system on its own platform, EDI exchanges with vendors and trading partners, and a few internal reporting feeds. None of these talk to each other natively, so the FTP layer is what connects them. Nightly extracts come in for the ERP. Outbound files go out for the warehouse system. EDI documents go back and forth with partners, and price and inventory files land from vendors.
It's unexciting work, but a lot depends on it. If the inbound inventory feed hasn't landed by 4 a.m., the warehouse is working from yesterday's numbers. If the outbound order file doesn't reach the vendor, nothing ships.
That's why hardening it was worth about three weeks of evenings. It's the point where all the other systems meet.
The approach
I wasn't trying to build a platform. I wanted to pull all of these scripts into one framework that an operator could configure without touching source code, that logged everything, that kept credentials encrypted, and that wouldn't half-overwrite a production file when something went wrong.
It ended up as three layers:
Maintenance is a subfile UI. Operators define a transfer by filling in fields: host, port, remote path, local file, pre and post commands, retry counts and retention. The password is entered once and stored encrypted.
Execution is one program that reads the config, takes a lock, runs the transfer, validates what came back, logs everything and updates the status.
Housekeeping is a scheduled retention job. Archives age out automatically based on the rules the operator set up for each interface.
Overall it's a small set of objects: a handful of physical files for the data model, two service programs (one for encryption, one for logging), two main programs, a retention job, a CL wrapper for operator inquiries, plus display and printer files and a data area for the key.
Once the design settled, the code took about three weeks of evenings. Writing it wasn't the hard part. The hard part was all the problems I hadn't realized needed solving.
Pre and post commands
Not every interface is just connect, get the file and disconnect. Some need something to happen right before or right after.
A vendor drops a zipped file that needs unzipping before the ERP job that reads it runs. A file needs to be renamed out of the way before the transfer, because a downstream job is still reading the old one. Or the transfer is really step one, and step two is submitting the ERP load job as soon as the data is safely in staging, instead of waiting for a fixed schedule.
Instead of hardcoding any of that into the engine, each interface can have its own sequence of pre-commands and post-commands. They're plain CL, run through the same command executor in whatever order the operator sets:
Pre-commands (before FTP): unzip inbound archive, rename prior file out of the way
Post-commands (after FTP): submit the ERP load job, notify downstream job scheduler
They're stored like the rest of the interface config, as sequenced rows against that interface, so adding a step means adding a row rather than changing a compiled program.
This turned out to matter more than I expected. It meant the framework could do something because a file arrived, not just move the file safely.
The problems I didn't know I had
This part surprised me.
I went in thinking the job was to centralize the config, encrypt the passwords and add logging. What I hadn't expected was how many failure modes show up once you have one place to look at all of them.
Jobs that crashed and left things inconsistent
The old scripts copied the FTP output straight into production. If anything failed partway, whether it was a network blip, a full disk or someone ending the job, you were left with a partial file in production and no clean way to tell what had happened.
The fix wasn't complicated, but it changed how everything else worked. Before anything gets overwritten, the engine archives the current data into a new, timestamped member of the same file. The FTP always lands in a dedicated staging file, never in production. Copying from staging to production is the very last step. If anything fails before that copy, production hasn't been touched.
[archive current] → [clear staging] → [FTP into staging]
→ [validate] → [atomic swap to production]
Having a staging file shaped the rest of the program. It let me validate before committing anything, catch empty files, compare record counts against the last run, and reject a transfer that looked wrong before it replaced the existing data.
Stripped down, with generic names rather than our real field names, the execution engine looks roughly like this:
// Never touch production directly. Everything lands in staging first
archMbr = %char(%date(): *iso0) + %char(%time(): *iso0);
ADDPFM FILE(prodLib/prodFile) MBR(archMbr);
CPYF FROMFILE(prodLib/prodFile) TOFILE(prodLib/prodFile)
FROMMBR(*FIRST) TOMBR(archMbr) MBROPT(*REPLACE);
CLRPFM FILE(stgFile);
ftpGet(host: user: password: remotePath: stgFile);
if recordCount(stgFile) = 0 and not allowEmpty;
logAndHalt('E01: zero records received, expected data');
return;
endif;
varPct = ((lastRecCount - recordCount(stgFile)) / lastRecCount) * 100;
if varPct > maxVarPct;
logAndHalt('E02: record count dropped ' + %char(varPct) + '% vs last run');
return;
endif;
// Only now does production ever get touched
CPYF FROMFILE(stgFile) TOFILE(prodFile) MBROPT(*REPLACE);
There's nothing clever in there. It's all about the order: archive first, validate in staging, and write production last. Also note that the archive doesn't go to a separate archive library. It's ADDPFM plus CPYF onto a new member of the file being overwritten, so the archive sits next to the data it protects, and restoring it means picking a different member instead of hunting through another library.
Jobs that died without anyone knowing
Every run now starts by writing a row to a history table with a state of running. When the run finishes cleanly, the row is set to complete. If it fails, it's set to failed.
So if there's a row stuck in running and no job is actually running, something crashed. The next run of that interface spots the leftover row, logs it and carries on. Nobody has to clean up stale locks or wonder about mystery rows.
It's a small pattern, one column with three values, but it means crashes get reported instead of disappearing.
-- opening a run: insert first, so this row is immediately
-- the newest 'R' for this interface
INSERT INTO ftp_history (run_id, iface, state, start_ts)
VALUES (:runId, :iface, 'R', CURRENT_TIMESTAMP);
-- crash recovery, run right after: any 'R' row that ISN'T
-- the newest run for this interface has to be a leftover
UPDATE ftp_history
SET state = 'F', end_ts = CURRENT_TIMESTAMP,
reason = 'orphan from prior crash'
WHERE iface = :iface
AND state = 'R'
AND run_id <> (SELECT MAX(run_id) FROM ftp_history WHERE iface = :iface);
The order matters here too. The current run inserts its own R row first, so by the time the check runs, that row is already the newest one for the interface and the subquery can't flag it. Any other row still in R has to be left over from a run that never finished. I didn't need an arbitrary "older than an hour" cutoff, because the run ID works as a timestamp and the comparison is relative.
Two jobs racing the same interface
Nothing stopped someone from starting the same interface twice, once from the schedule and once by hand because it "looked stuck." Two jobs FTPing into the same staging file at once causes its own kind of corruption. It's also a different problem from crash recovery, because both jobs are still alive.
The fix is a lock table keyed by interface, holding the job name, user and timestamp of whoever took the lock. Before a run starts, it checks for an existing lock. If there is one and it's less than a couple of hours old, the run stops with a LOCKED error instead of competing with the job that's already going.
The one deliberate choice here, and it's the one that matters, is that this table is never accessed with native record-level I/O. There's no DCL-F, no CHAIN and no UPDATE against a declared record format. Every read, insert and delete goes through embedded SQL.
-- checking for an existing lock
SELECT lock_job, lock_user, lock_ts
FROM ftp_lock
WHERE iface = :iface
FETCH FIRST 1 ROW ONLY;
-- acquiring it
INSERT INTO ftp_lock (iface, lock_job, lock_user, lock_ts)
VALUES (:iface, :jobStr, :user, CURRENT_TIMESTAMP);
-- releasing it, success or failure
DELETE FROM ftp_lock WHERE iface = :iface;
With native RPG I/O against a shared record, a job takes a real record lock just to check whether another job holds the lock. That's exactly the kind of contention a lock table is supposed to prevent. If checking the lock can get stuck waiting on a lock, the mechanism works against itself. Using SQL for this table means checking the lock never becomes the reason a job is waiting.
Retries, and what happens when they run out
The old scripts didn't retry at all. If the server refused the connection once, the whole job failed.
Sometimes the server just needs a moment, so the new engine retries up to a set number of times, configurable per interface. That part was easy.
The more interesting question was what to do when the retries run out.
Previously the job just ended in failure. But the operator might be at their desk and know something the program doesn't. Maybe the remote server is down for maintenance, and they'd rather skip this transfer and let the rest of the batch finish.
So when retries are exhausted, the job sends an inquiry message to the system operator message queue with three choices:
- Retry resets the retry counter and tries again.
- Skip moves on to the next record in the batch.
- Cancel ends the whole run.
It's an old-fashioned pattern, but for a batch window that an operator is watching, it works well. The job doesn't fail when it doesn't have to, and it doesn't loop forever.
Interfaces that were broken for weeks
The old scripts had no way to say "this interface is broken, stop trying." If a vendor changed their SFTP credentials on a Monday without telling anyone, the job would fail every night at 2 a.m. for a month and send thirty failure emails, and someone would eventually read one.
The new rule is that if an interface fails a configured number of times in a row, it gets disabled automatically. The escalation email goes out once instead of thirty times, and an operator has to re-enable the interface by hand, which means someone has to look at what broke.
It's a small change, and the 3 a.m. emails stopped within the first month.
Transfers that overwrote good data with bad data
This is the one I'm happiest about.
Every interface stores the record count from its last successful run. On the next run, after the FTP finishes but before production is overwritten, the engine compares the new count with the old one.
If the count has dropped by more than a configured percentage (say 50%), the run stops with an error. Production stays as it was, and an operator gets pulled in to look.
I won't say how often this has caught an upstream batch that only wrote half its output. It's a little embarrassing how often that used to go through without anyone noticing.
The audit trail we didn't have
The maintenance UI writes an audit record for every add, change, delete or restore, at the field level, with before and after values, the user and a timestamp.
Password changes are always logged as asterisks. The real value is only ever stored in the encrypted field in the config table. It doesn't appear in the audit log, error messages, spool files, the job log or emails.
(Credential handling deserves its own article. Briefly: passwords are AES-256 encrypted with the database's own SQL encryption functions, and the key lives in a data area that's excluded from *PUBLIC and readable only by the service program that needs it. The plaintext exists in memory only for the length of the login call and is cleared as soon as it returns. My next post goes into the details.)
How it runs now
The whole system compiles in a documented order: physical files first, then the key data area, the two service programs, the CL wrapper, and finally the three main programs. Operators use a handful of simple calls:
call the maintenance program // open the UI
call the FTP engine with (interface, code) // run one specific transfer
call the FTP engine with (interface, blank)// run all transfers for an interface
submit the FTP engine to a batch queue // scheduled batch window
submit the retention cleanup on a schedule // nightly housekeeping
Nothing requires a source change or a recompile. A new interface is a new row. Changing a destination path means changing a field, and disabling an interface temporarily means flipping a flag. Restoring yesterday's archive is an option on the maintenance screen, and it gets written to the audit log.
What I'd tell anyone thinking about this
If you're on IBM i with a pile of FTP scripts like mine, you probably have more technical debt than you think, and paying it off is probably less work than you expect.
A few things that weren't obvious to me going in:
- Staging matters more than anything else. If production is never written to directly, most failures stop being disasters and turn into "run it again tomorrow."
- A history table with a state column is cheaper than a monitoring system. A running row that never became complete or failed means something crashed, and you find out the next morning instead of days later.
- Operators know things your program doesn't. An operator inquiry when retries run out looks dated, but it beats both failing silently and retrying forever.
- Variance checks catch other people's bugs too. Upstream batch jobs have bugs, and there's no reason to let them corrupt your files.
- Disabling an interface after repeated failures is a favor to everyone, especially the operators who used to delete thirty failure emails every Monday.
None of these ideas are new. Every pattern here exists in some IBM i shop already, written by someone who learned it the hard way. What's different is that they're in one framework that handles every FTP transfer we do.
The first week after rollout
In the first week, a transfer failed because the remote server was down. The retries ran out, the inquiry went to the operator, and they replied Skip. The run carried on and finished with one failed row and sixteen successful ones in the history table, plus a clean summary in the spool. The next morning someone saw the failed row, checked that the remote server was back, chose Retry from the maintenance screen, and that was the end of it.
Nobody called me or sent an urgent email, and nobody got paged at 3 a.m. The system told the operator what had happened, and they knew what to do about it.
To me that's what modernization on IBM i usually looks like in practice. It isn't replacing the platform or rewriting everything in Java. It's fixing the things that have been fragile for twenty years, using tools the platform already has, so the people running the business stop dreading their inbox.
Wrapping up
If you work in an IBM i shop and FTP makes you a little nervous, you're not alone, and it's probably fixable. The platform already has what you need: SQL encryption functions, subfile UIs, operator messaging, data areas and service programs. You don't need a new stack. You need to decide that the pile itself is the problem and replace it with one framework.
Next I want to cover the credential encryption in detail: how I'm using DB2 for i's own encrypt and decrypt SQL functions, where the key lives, how it's rotated, and why you shouldn't write your own cipher. It seems like it should be the easy part, and it never quite is.
And if anyone at one of my old shops still has my old FTP scripts running somewhere, sorry about that. I've done better since.
Updated November 2026: I corrected a few details against the production code. Archives go to a new member of the same file, not a separate archive library. Crash recovery compares against the latest run ID instead of using a one-hour cutoff. Credentials use DB2 for i's SQL encryption functions, not the Cryptographic APIs. I also added a section on the SQL-only lock table.
The author is an IBM i practitioner working on modernization, integration, and warehouse systems.
Top comments (0)