The FTP Execution Engine, Line by Line: Staging, Validation, and the Atomic Swap
The one program that actually moves the file — and why production is always the last thing it touches
In the last post, I walked through why I replaced a pile of ad-hoc FTP CL programs with a single framework: one config table, one execution engine, one housekeeping job. I sketched the execution engine with generic pseudocode and moved on, because the post was already long and the point I was making was architectural, not line-by-line.
People always ask "okay, but what does the program actually look like" that it's worth a second post. This one has no pseudocode in it. Every block below is the real shape of the program that runs, on a schedule, for every FTP interface in the framework — genericized names, same logic.
The data this program touches
Before the code makes sense, you need the shapes it's working with. Three things:
A config row, one per interface — host, credentials (encrypted, see the credentials post, remote path, staging file, production file, a record length for auto-creating either file if it's missing, max allowed variance percent, allow-empty flag, and the record count from the last successful run.
A history row, opened at the start of every run and closed at the end — interface, run ID, state (Running / Complete / Failed), start and end timestamps, reason code if it failed.
Two physical files on the IBM i side for each interface: the staging file (always cleared before use, always the FTP target, never anything else) and the production file (never written to except by the final copy). There's no separate archive library — the "previous version" of whichever file is about to be overwritten gets archived as a new member on that same file, not copied somewhere else. That turns "go back to yesterday's data" into picking a different member on a file you already know the name of, instead of hunting through another library.
That's the whole shape. Nothing about it is exotic. The discipline is entirely in the order operations happen in, which is what the rest of this post is actually about.
CREATE TABLE ftpcfg (
iface CHAR(10) NOT NULL,
host VARCHAR(100) NOT NULL,
port INT DEFAULT 21,
remote_path VARCHAR(200) NOT NULL,
user_id VARCHAR(50) NOT NULL,
enc_pwd CHAR(256) NOT NULL, -- see article 4
file_lib VARCHAR(10) NOT NULL,
stg_file VARCHAR(10) NOT NULL,
prod_file VARCHAR(10) NOT NULL,
rcd_len INT DEFAULT 0, -- for auto-create, 0 = don't auto-create
max_var_pct DEC(5,2) DEFAULT 0.00,
allow_empty CHAR(1) DEFAULT 'N',
dup_check CHAR(1) DEFAULT 'Y',
last_rec_cnt INT DEFAULT 0,
PRIMARY KEY (iface)
);
CREATE TABLE ftphist (
run_id CHAR(20) NOT NULL, -- YYYYMMDD-HHMMSS-IFC
iface CHAR(10) NOT NULL,
state CHAR(1) NOT NULL, -- R / C / F
reason VARCHAR(100),
start_ts TIMESTAMP NOT NULL,
end_ts TIMESTAMP,
rec_cnt INT,
PRIMARY KEY (run_id)
);
Opening the run
The first thing the engine does, before it touches FTP or the filesystem at all, is write a Running row. This has to happen before anything else, because it's the thing that makes a crashed job detectable later — a row stuck in R with no matching C or F is, by definition, a job that died without telling anyone.
dcl-s runId char(20);
dcl-s iface char(10) inz('WHSEFEED'); // passed in as a parameter
runId = %char(%date(): *iso0) + '-' + %char(%time(): *iso0) + '-' + %trim(iface);
exec sql
INSERT INTO ftphist (run_id, iface, state, start_ts)
VALUES (:runId, :iface, 'R', CURRENT_TIMESTAMP);
runId is a plain date-time stamp plus the interface, not a sequence number and not a random value — deliberately readable, and, as it turns out, useful for more than logging. Keep this in mind; it comes back later in a way that's more interesting than "it's a unique key."
Reading the config
One row, one SELECT INTO, into a qualified data structure:
dcl-ds cfg qualified;
host varchar(100);
port int(10);
remotePath varchar(200);
userId varchar(50);
encPwd char(256);
fileLib varchar(10);
stgFile varchar(10);
prodFile varchar(10);
rcdLen int(10);
maxVarPct packed(5:2);
allowEmpty char(1);
dupChk char(1);
lastRecCnt int(10);
end-ds;
exec sql
SELECT host, port, remote_path, user_id, enc_pwd,
file_lib, stg_file, prod_file, rcd_len,
max_var_pct, allow_empty, dup_check, last_rec_cnt
INTO :cfg
FROM ftpcfg
WHERE iface = :iface;
if sqlcode <> 0;
closeRun(runId : 'F' : 'config row not found');
return;
endif;
pwd = Decrypt(cfg.encPwd); // service program from the credentials post
Nothing in cfg ever gets logged, displayed, or written anywhere except back into local variables. pwd exists only long enough to log in during the transfer step, and gets cleared explicitly the moment that call returns — same rule as the credentials post, applied at every call site, not just the one I wrote about there.
Auto-creating the files it needs, if they're missing
The first run against a brand-new interface shouldn't require someone to pre-create two physical files by hand before anything works. CHKOBJ through QCMDEXC doubles as an existence check — a non-zero SQLCODE after it means the object isn't there:
dcl-s chkCmd char(200);
chkCmd = 'CHKOBJ OBJ(' + %trim(cfg.fileLib) + '/' + %trim(cfg.prodFile) +
') OBJTYPE(*FILE)';
exec sql CALL QSYS2.QCMDEXC(:chkCmd);
if sqlcode <> 0;
if cfg.rcdLen > 0;
exec sql CALL QSYS2.QCMDEXC(
'CRTPF FILE(' + %trim(cfg.fileLib) + '/' + %trim(cfg.prodFile) +
') RCDLEN(' + %char(cfg.rcdLen) + ') MAXMBRS(*NOMAX)');
else;
closeRun(runId : 'F' : 'target file missing and no record length configured');
return;
endif;
endif;
The same check runs against the staging file. If the config row carries a record length, a missing file is a five-minute setup problem the engine fixes itself; if it doesn't, the run stops here with a reason that says exactly what's missing, instead of failing three steps later with a much less obvious error.
Archiving before anything risky happens
This is the step that makes every failure after this point recoverable instead of catastrophic. Before the staging file is touched, before FTP even runs, whatever's currently sitting in the file that's about to be overwritten gets archived — as a new member on that same file, not a copy somewhere else:
dcl-s archMbr char(10);
archMbr = %char(%date(): *iso0 : '') + %char(%time(): *iso0 : '');
archMbr = %subst(archMbr : 3 : 10); // YYMMDDHHMM, 10 chars
exec sql
CALL QSYS2.QCMDEXC('ADDPFM FILE(' + %trim(cfg.fileLib) + '/' +
%trim(cfg.prodFile) + ') MBR(' + archMbr + ')');
exec sql
CALL QSYS2.QCMDEXC('CPYF FROMFILE(' + %trim(cfg.fileLib) + '/' +
%trim(cfg.prodFile) + ') TOFILE(' + %trim(cfg.fileLib) + '/' +
%trim(cfg.prodFile) + ') FROMMBR(*FIRST) TOMBR(' + archMbr +
') MBROPT(*REPLACE)');
exec sql
CALL QSYS2.QCMDEXC('CLRPFM FILE(' + %trim(cfg.fileLib) + '/' +
%trim(cfg.stgFile) + ')');
ADDPFM adds a new member to the production file; CPYF copies the current data — still sitting in the *FIRST member — into it. The archive is a snapshot living right next to the data it's protecting, addressable by a member name, not a filename in some archive library nobody remembers the naming convention for.
If this step fails — disk full, authority problem, member limit reached — the program stops here. Staging hasn't been touched. Production hasn't been touched. Nothing downstream has any idea a run was even attempted.
Running the actual transfer
This part is intentionally boring, because the interesting design decision already happened: don't write your own FTP client logic. The engine binds against an FTP API service program and calls its exported procedures directly — open a session, log in, get the file, quit:
dcl-s sess int(10);
sess = ftp_open(%trim(cfg.host) : cfg.port : 60 : *0 : *0 : *0);
if sess < 0;
closeRun(runId : 'F' : 'connection failed');
return;
endif;
if ftp_login(sess : %trim(cfg.userId) : %trim(pwd) : '') < 0;
clear pwd;
closeRun(runId : 'F' : 'login failed');
ftp_quit(sess);
return;
endif;
clear pwd; // the moment login returns, this is done, win or lose
if ftp_get(sess : %trim(cfg.remotePath) :
'/qsys.lib/' + %trim(cfg.fileLib) + '.lib/' +
%trim(cfg.stgFile) + '.file/' + %trim(cfg.stgFile) + '.mbr') < 0;
closeRun(runId : 'F' : 'transfer failed: ' + ftp_errorMsg(0));
ftp_quit(sess);
return;
endif;
ftp_quit(sess);
A few things worth calling out:
-
clear pwdhappens immediately after the login call returns, not at the end of the procedure, and not conditionally on success. Whether the login worked or not, the plaintext's job is done the instant that call comes back. -
ftp_gettargets a fully-qualified IFS path into the staging member, not a stream file. On IBM i,/qsys.lib/LIB.lib/FILE.file/MBR.mbris a normal filesystem path into a database file member — the FTP API doesn't need to know it's talking to a table instead of a flat file. - There's no custom retry, no custom protocol handling, no hand-rolled command sequencing anywhere in this block. That's deliberate. FTP client behavior is exactly the kind of thing worth getting from a maintained library instead of reimplementing — the framework's value is everything wrapped around this call, not the call itself.
Validating before anything in production changes
FTP finishing without an error doesn't mean the data is right. This is the check that actually protects production:
dcl-s newCnt int(10);
dcl-s varPct packed(7:2);
exec sql
SELECT COUNT(*) INTO :newCnt
FROM ftpcfg_lib.stgFile; -- resolved to the actual staging file
if newCnt = 0 and cfg.allowEmpty = 'N';
closeRun(runId : 'F' : 'EMPTYFILE: zero records, expected data');
return;
endif;
if cfg.maxVarPct > 0 and cfg.lastRecCnt > 0;
varPct = ((cfg.lastRecCnt - newCnt) * 100) / cfg.lastRecCnt;
if varPct > cfg.maxVarPct;
closeRun(runId : 'F' :
'VARIANCE: dropped ' + %char(varPct) + '% vs last run (' +
%char(cfg.lastRecCnt) + ' -> ' + %char(newCnt) + ')');
return;
endif;
endif;
Say the warehouse feed brought in 300,000 rows last night and the config's max_var_pct is 50. Tonight it brings in 140,000. That's a 53% drop — past the threshold — so the run halts here with reason VARIANCE, and the reason string records the exact before/after counts, not just "failed." Nobody has to guess what tripped it; the row says it.
This check runs before production is touched, which means a bad upstream batch on someone else's system stops at the staging file and never gets the chance to overwrite something that was correct.
One more check, worth adding once the first two exist almost for free: if this interface's data genuinely doesn't change every run — a reference table, a slow-moving price list — an exact match against the last successful count is a cheap signal that tonight's file is identical to last night's, not new data that happens to be the same size.
if cfg.dupChk = 'Y' and cfg.lastRecCnt > 0 and newCnt = cfg.lastRecCnt;
closeRun(runId : 'C' : 'skipped: unchanged since last successful run');
return;
endif;
It's gated behind a flag per interface, not a default, because for most feeds an unchanged count from one night to the next is exactly what a real problem looks like, not evidence of nothing happening. Where it is turned on, it turns "processed the same file twice" from a silent waste of a production overwrite into a one-line skip logged as a success, not a failure.
The only step that touches production
Everything up to here has been building toward one command:
exec sql
CALL QSYS2.QCMDEXC(
'CPYF FROMFILE(' + %trim(cfg.fileLib) + '/' + %trim(cfg.stgFile) +
') TOFILE(' + %trim(cfg.fileLib) + '/' + %trim(cfg.prodFile) +
') MBROPT(*REPLACE)');
exec sql
UPDATE ftpcfg SET last_rec_cnt = :newCnt WHERE iface = :iface;
closeRun(runId : 'C' : *blank : newCnt);
That's it. One CPYF, one baseline update, one history close. Everything before this point exists to make sure this exact moment is the only time production data changes — and that by the time it runs, the data has already been counted, compared against last night, checked against duplication, and proven not to be garbage.
closeRun is the mirror of the open at the top:
dcl-proc closeRun;
dcl-pi *n;
p_runId char(20) const;
p_state char(1) const;
p_reason varchar(100) const options(*nopass);
p_cnt int(10) const options(*nopass);
end-pi;
exec sql
UPDATE ftphist
SET state = :p_state,
reason = :p_reason,
rec_cnt = :p_cnt,
end_ts = CURRENT_TIMESTAMP
WHERE run_id = :p_runId;
end-proc;
Every exit from the program, success or failure, goes through this one procedure. There's no path where a run just... stops, and the history table doesn't know about it. If the job itself gets killed mid-run — someone ends it, the subsystem goes down — that's the one case closeRun can't catch. That's exactly the case a follow-up piece on this framework's locking and crash-recovery logic covers: because runId is a plain date-time stamp, not a random value, a row still stuck in R is trivially the oldest running row for its interface the next time anything checks — which turns out to be enough to detect it without any arbitrary timeout at all.
What this buys you, concretely
Walk a bad night through this: the upstream system on the other end has a bug and only writes half its usual output. The old CL script would FTP that partial file straight into production, the ERP job would run against it at 4 a.m., and the warehouse would spend the day building pick lists from data that was quietly wrong. Nobody would know until someone downstream noticed something was off — hours later, several systems away from where the actual problem started.
With this version: the file lands in staging, the count comes back roughly half of last night's, VARIANCE fires, production is exactly as correct as it was yesterday, and the history table has one Failed row with a reason string that tells you precisely what tripped it before anyone had to go looking. The bug on the other system is still a bug. It just isn't your outage anymore.
None of the individual pieces here are clever. CHKOBJ, CRTPF, ADDPFM, CPYF, CLRPFM, a couple of calls into a bound FTP library, a couple of SELECT COUNT(*) checks. The only thing that took real thought was the order — auto-create, archive, stage, validate, and only then touch production, with a history row open the entire time that can prove, after the fact, exactly which of those steps a given run got to before it stopped.
The next post in this series covers the other half of the engine: the SQL-only lock table that keeps two runs of the same interface from racing each other, why it's deliberately never touched with native record-level I/O, and what happens when retries run out and the system operator gets asked Retry / Skip / Cancel.
Jaya Krushna Mohapatra is a Warehouse Management Systems Architect focused on enterprise integrations, IBM i modernization, and scalable backend systems.
Top comments (0)