SQL + FILE OPERATIONS

RPG I/O and SQL side by side

Plain-English IBM i learning notes with examples, comparisons, and official references.

One keyed lookup

RPG operation

CHAIN customerId CUSTOMER

SQL pattern

SELECT * FROM MYLIB.CUSTOMER WHERE CUSTOMER_ID = :customerId

CHAIN returns a record buffer and %FOUND; SQL returns a result row and SQLSTATE. Both need a not-found branch.

Check whether a key exists

RPG operation

SETLL key MYFILE + %EQUAL

SQL pattern

SELECT 1 FROM MYLIB.MYFILE WHERE KEY = :key FETCH FIRST 1 ROW ONLY

SETLL can be a cheaper existence check than CHAIN in suitable RPG access paths. SQL still needs a deliberate index and plan.

Read all equal-key rows

RPG operation

SETLL customerId ORDER; READE customerId ORDER

SQL pattern

SELECT * FROM MYLIB.ORDER WHERE CUSTOMER_ID = :customerId ORDER BY ORDER_ID

READE stops at the equal-key boundary; SQL expresses the whole row set. Always define ordering when the application depends on it.

Sequential processing

RPG operation

READ MYFILE in a loop

SQL pattern

SELECT ... FROM MYLIB.MYFILE ORDER BY key

RPG uses a file cursor and EOF status; SQL uses a cursor and SQLSTATE 02000. Do not process stale host variables after end-of-data.

Create a row

RPG operation

WRITE MYFILE

SQL pattern

INSERT INTO MYLIB.MYFILE (...) VALUES (...)

Both can fail on duplicate keys or constraints. Handle the failure and preserve the diagnostic.

Change a row

RPG operation

UPDATE MYFILE

SQL pattern

UPDATE MYLIB.MYFILE SET ... WHERE KEY = :key

RPG update depends on the current record and lock state; SQL should make the predicate and optimistic version check explicit.

Remove a row

RPG operation

DELETE MYFILE

SQL pattern

DELETE FROM MYLIB.MYFILE WHERE KEY = :key

Preview the predicate and check affected-row count. A missing key is different from a successful delete.

Find the previous key

RPG operation

SETGT key; READP MYFILE

SQL pattern

SELECT ... WHERE key < :key ORDER BY key DESC FETCH FIRST 1 ROW ONLY

The SQL ordering and strict less-than boundary must match the RPG cursor intent.

Lookup with handled I/O errors

RPG operation

CHAIN (E) key MYFILE

SQL pattern

SELECT ... WHERE key = :key; inspect SQLSTATE and diagnostics

The RPG E extender routes an operation exception to program status handling; SQL must check SQLSTATE and preserve diagnostic details.

Read without holding an update lock

RPG operation

CHAIN(N) key MYFILE

SQL pattern

SELECT ... FOR READ ONLY

The exact lock behavior depends on file and isolation settings. Treat both as read intent and revalidate before a later update.

Optimistic concurrency

RPG operation

UPDATE MYFILE after CHAIN

SQL pattern

UPDATE MYLIB.MYFILE SET ... WHERE key = :key AND version = :version

SQL makes the version predicate explicit. RPG must compare a refreshed record or version before writing to avoid overwriting a concurrent change.

Process user-edited rows

RPG operation

READC subfile; validate changed rows

SQL pattern

SELECT staged rows WHERE changed_by = :user

READC is a display-file state operation. SQL can persist staged edits, but it cannot replace the workstation change indicator.

Create a header with detail rows

RPG operation

WRITE header; WRITE detail; COMMIT

SQL pattern

INSERT header; INSERT detail; COMMIT

Both patterns need one transaction boundary and a rollback path so a partial order is not left behind.

Previous page navigation

RPG operation

READP / READPE for backwards paging

SQL pattern

ORDER BY key DESC OFFSET :skip ROWS FETCH NEXT :page ROWS ONLY

Keyset paging is often more stable than OFFSET for changing data; choose the method after measuring the workload and defining a stable order.

Partial or composite key access

RPG operation

SETLL %KDS(compositeKey) MYFILE

SQL pattern

WHERE region = :region AND warehouse = :warehouse ORDER BY sku

Both depend on key field order and compatible values. Verify the access path and boundary semantics before assuming identical results.

Conditional delete

RPG operation

DELETE MYFILE after CHAIN

SQL pattern

DELETE FROM MYLIB.MYFILE WHERE key = :key AND status = 'C'

Make the business condition part of the final predicate or revalidate under the appropriate lock; a prior read alone can become stale.

One keyed customer lookup with a not-found branch

RPG operation

**free
chain customerId CUSTOMER;
if %found(CUSTOMER);
  customerName = NAME;
  customerStatus = STATUS;
else;
  // Customer does not exist.
endif;

SQL pattern

**free
exec sql
  select NAME, STATUS
    into :customerName, :customerStatus
    from MYLIB.CUSTOMER
   where CUSTOMER_ID = :customerId;

if SQLSTATE = '02000';
  // Customer does not exist.
endif;

Both shapes represent a zero-or-one-row contract. CHAIN reports %FOUND; the SQL singleton SELECT reports SQLSTATE 02000 for no row. Neither branch should use fields left over from a prior read.

Does the key exist?

RPG operation

**free
setll customerId CUSTOMER;
if %equal(CUSTOMER);
  // An exact key exists.
else;
  // No exact key.
endif;

SQL pattern

**free
exec sql
  select 1 into :exists
    from MYLIB.CUSTOMER
   where CUSTOMER_ID = :customerId
   fetch first 1 row only;

if SQLSTATE = '00000';
  // An exact key exists.
endif;

Use an existence check only when you do not need the record fields. A subsequent change still needs its own final predicate or lock/concurrency policy.

Read every order for one customer

RPG operation

**free
setll customerId ORDERS;
reade customerId ORDERS;
dow not %eof(ORDERS);
  // Process ORDER_ID and TOTAL.
  reade customerId ORDERS;
enddo;

SQL pattern

**free
exec sql
  declare ordersCursor cursor for
    select ORDER_ID, TOTAL
      from MYLIB.ORDERS
     where CUSTOMER_ID = :customerId
     order by ORDER_ID;
exec sql open ordersCursor;
// FETCH until SQLSTATE = '02000'.
exec sql close ordersCursor;

READE stops automatically at the equal-key boundary. In SQL, the WHERE predicate defines that boundary and ORDER BY defines the processing order. A full cursor loop must test SQLSTATE after each FETCH.

Process a sequential range

RPG operation

**free
setll startOrderId ORDERS;
read ORDERS;
dow not %eof(ORDERS) and ORDER_ID <= endOrderId;
  // Process the current order.
  read ORDERS;
enddo;

SQL pattern

**free
exec sql
  declare rangeCursor cursor for
    select ORDER_ID, STATUS
      from MYLIB.ORDERS
     where ORDER_ID between :startOrderId and :endOrderId
     order by ORDER_ID;
exec sql open rangeCursor;
// FETCH until SQLSTATE = '02000'.
exec sql close rangeCursor;

The native record format and SQL result format are different, but both need a clear start, end, stable ordering, and a clean end-of-data branch.

Create a new order row

RPG operation

**free
ORDER_ID = nextOrderId;
CUSTOMER_ID = customerId;
STATUS = 'NEW';
write ORDERS;

if %error;
  // Inspect the file-operation error.
endif;

SQL pattern

**free
exec sql
  insert into MYLIB.ORDERS
    (ORDER_ID, CUSTOMER_ID, STATUS)
  values
    (:nextOrderId, :customerId, 'NEW');

if SQLSTATE = '23505';
  // Duplicate key.
endif;

WRITE and INSERT both create a row. SQL lets the target columns and constraints be visible in the statement; native I/O writes the populated record buffer. In both cases, keep the duplicate/error branch explicit.

Change a row only when its version is current

RPG operation

**free
chain(E) orderId ORDERS;
if %found(ORDERS) and VERSION = expectedVersion;
  STATUS = 'COMPLETE';
  VERSION += 1;
  update ORDERS;
endif;

SQL pattern

**free
exec sql
  update MYLIB.ORDERS
     set STATUS = 'COMPLETE',
         VERSION = VERSION + 1
   where ORDER_ID = :orderId
     and VERSION = :expectedVersion;

// A zero-row result means stale or missing data.

Both approaches need a concurrency decision. SQL expresses the final version test in the update predicate; native I/O must avoid overwriting a row that changed after it was read.

Delete only a closed order

RPG operation

**free
chain(E) orderId ORDERS;
if %found(ORDERS) and STATUS = 'CLOSED';
  delete ORDERS;
else;
  // Missing order or still open.
endif;

SQL pattern

**free
exec sql
  delete from MYLIB.ORDERS
   where ORDER_ID = :orderId
     and STATUS = 'CLOSED';

if SQLSTATE = '02000';
  // Missing order or status no longer qualifies.
endif;

Put the business condition in the final SQL predicate. In native I/O, recheck the record state immediately before DELETE and account for the chosen locking or commitment-control behavior.

Read an order with optional shipment data

RPG operation

**free
chain orderId ORDERS;
if %found(ORDERS);
  chain orderId SHIPMENT;
  if %found(SHIPMENT);
    shippedDate = SHIPPED_DATE;
  endif;
endif;

SQL pattern

**free
exec sql
  select o.TOTAL, s.SHIPPED_DATE
    into :orderTotal, :shippedDate :shippedDateNull
    from MYLIB.ORDERS o
    left join MYLIB.SHIPMENT s
      on s.ORDER_ID = o.ORDER_ID
   where o.ORDER_ID = :orderId;

Nested CHAIN calls are procedural and can be fine for a single request. A LEFT JOIN states the relationship in one query and uses a null indicator when the optional shipment does not exist.

Calculate an aggregate instead of looping to add totals

RPG operation

**free
totalAmount = 0;
setll customerId ORDERS;
reade customerId ORDERS;
dow not %eof(ORDERS);
  totalAmount += AMOUNT;
  reade customerId ORDERS;
enddo;

SQL pattern

**free
exec sql
  select coalesce(sum(AMOUNT), 0)
    into :totalAmount
    from MYLIB.ORDERS
   where CUSTOMER_ID = :customerId;

For a straightforward aggregate, let Db2 do the addition in one statement. Use the RPG loop only when every row has procedural work that cannot be expressed safely in SQL.

IBM documentation