Replace Native IO with Embedded SQL

If you've been writing RPG for more than a few years, chances are your programs are full of CHAIN, SETLL, READE and UPDATE operations against physical and logical files. It works. It's fast. It's also increasingly the wrong default.

Embedded SQL in RPGLE isn't a fad, it's been production-ready for over two decades, and IBM i's SQL Query Engine (SQE) has matured to the point where, for most business logic, SQL is not just easier to write but often faster than native I/O. This post walks through why and shows practical before/after code you can start using today.

Why Move Away from Native I/O?

Native I/O operations are tied tightly to logical file definitions. Every CHAIN or READE depends on an access path being built and maintained, often a logical file someone created years ago for a report that no longer exists.

Over time, shops accumulate dozens of logical files per physical file, each one consuming disk, CPU on updates and brain power for anyone new to the codebase.

Embedded SQL removes that dependency. You express what data you want, not how to navigate to it and let the SQE decide the most efficient access path, using existing indexes if they help or building temporary ones on the fly.

That means:

• Fewer logical files to maintain
• Set-based operations instead of row-by-row loops
• Easier joins across multiple files without maddening multi-level logicals
• Cleaner, more readable business logic

A Practical Example: Order Lookup

Here's a typical native I/O pattern for finding open orders for a customer:

dcl-f ORDERHDR keyed usage(*input);

dcl-s custNo packed(6);

custNo = 100234;

setll custNo ORDERHDR;
dou %eof(ORDERHDR);
  reade custNo ORDERHDR;
  if %eof(ORDERHDR);
    leave;
  endif;
  if ordStatus = 'O';
    // process open order
  endif;
enddo;

This requires a keyed logical file over ORDERHDR by customer number and manual loop control to skip closed orders.

Here's the same logic using embedded SQL:

dcl-s custNo packed(6) inz(100234);

exec sql
  declare openOrders cursor for
    select orderNo, orderDate, orderTotal
    from orderhdr
    where custNo = :custNo
      and ordStatus = 'O'
    order by orderDate desc;

exec sql open openOrders;

dou sqlcode <> 0;
  exec sql fetch next from openOrders
    into :orderNo, :orderDate, :orderTotal;
  if sqlcode <> 0;
    leave;
  endif;
  // process open order
enddo;

exec sql close openOrders;

No logical file needed.

The filtering, sorting and status check are all handled in the WHERE and ORDER BY clauses, logic that used to live buried inside your loop is now visible and self-documenting in the SQL statement itself.

Single-Row Fetches: FETCH vs CHAIN

For simple key lookups, you don't need a cursor at all. Compare:

// Native
chain custNo CUSTMAST;
if %found(CUSTMAST);
  custName = cmName;
endif;

// Embedded SQL
exec sql
  select cmName
  into :custName
  from custmast
  where custNo = :custNo;

if sqlcode = 0;
  // found
endif;

The SQL version reads almost like English, and critically, it doesn't care whether custNo is the first key field of an existing logical, the optimizer will use whatever index exists or a full scan if the table is small enough that it's cheaper.

Set-Based Updates: The Real Win

This is where embedded SQL really shows its value.

Suppose you need to apply a 5% discount to every open order for a customer. Native I/O means a read/update loop:

setll custNo ORDERHDR;
dou %eof(ORDERHDR);
  reade custNo ORDERHDR;
  if %eof(ORDERHDR);
    leave;
  endif;
  if ordStatus = 'O';
    orderTotal *= 0.95;
    update ORDERHDR;
  endif;
enddo;

With SQL, it's one statement:

exec sql
  update orderhdr
  set orderTotal = orderTotal * 0.95
  where custNo = :custNo
    and ordStatus = 'O';

One statement, no loop, no cursor and the database engine handles locking and commitment control semantics for you.

For anything touching more than a handful of rows, this is typically faster too, since it avoids the round-trip overhead of individual I/O operations.

Things to Watch For

Embedded SQL isn't a univeral answer:

• Row-by-row still has its place. If you're processing millions of rows with complex per-row logic that can't be expressed in SQL, a native or SQL cursor loop may still be the right tool, just be deliberate about it.
• Journal and commitment control behavior can differ slightly between native I/O and SQL, particularly around isolation levels. Test under your actual commitment control environment before assuming parity.
• SQLCODE and SQLSTATE need proper checking. Unlike %found() and %eof(), SQL error handling is more nuanced, get comfortable with SQLCODE and SQLSTATE.
• Performance isn't automatic. SQE needs decent indexes to work with, just like native I/O needs logicals. Use Visual Explain in ACS to check your access plans rather than assuming SQL is always faster.

Getting Started

The easiest on-ramp is picking one program with heavy logical file dependency and rewriting just the read logic first, leaving your business rules untouched. Once you're comfortable with cursors and host variables, move on to set-based updates and deletes.

Within a few programs, embedded SQL stops feeling like "the new way" and starts feeling like the obvious way, because for most business logic, it is.

If your shop is still writing new logical files in 2026, it might be time to ask why.