Brewing File Handling Choices: Native RPG vs. Embedded SQL and When to Use Each

 Brewing File Handling Choices: Native RPG vs. Embedded SQL and When to Use Each

By George | The RPG Blend – Real Code, Real Coffee, No Nonsense

---

Today’s coffee: Nucoffee Ethiopia Dark Espresso Roast — bold, rich, and unapologetically strong. Like the right file-handling choice in RPG, it delivers intensity and clarity exactly when you need it. The right brew — and the right I/O method — can make all the difference.

On the IBM i, file handling is the heartbeat of almost every application. For decades, RPG developers relied on native file I/O — opcodes like CHAIN, READ, and WRITE — to run the business. Then SQL brought a new way to talk to the database.

Now, each time we touch data, we face the same question:
Do we stick with the time-tested reliability of native I/O, or take advantage of the flexibility and power of embedded SQL?

Both approaches are excellent — but for different reasons. The skill is knowing when to use which, how to combine them safely, and how to manage commitment control when you do.

---

1. Understanding Native RPG File Handling

Native file handling is what most long-time RPG developers grew up with. It works directly against DDS-defined physical and logical files, reading and writing records one at a time.

Core traits:

• Tight integration with RPG opcodes
• Record-oriented access
• Direct key-based lookups or sequential reads
• Requires files to be declared in the F-spec

Example: Retrieve a Customer by Key Using Native I/O

Dcl-F Customer Usage(*Input) Keyed;

Chain ('C00045') Customer;

If %Found(Customer);

   Dsply ('Customer: ' + CustName);

EndIf;

When it shines:

• You need a specific record quickly and efficiently
• Simple batch updates or sequential processing
• Minimal learning curve for traditional RPG teams
• Debugging is straightforward — step through the opcodes

---

2. Embracing Embedded SQL in RPG

Embedded SQL lets you run DB2 SQL queries directly inside your RPG code. You’re speaking the same language as the database engine, unlocking set-based logic, complex joins, and optimizer-driven performance.

Core traits:

• SQL inside EXEC SQL blocks or free-form syntax
• Can operate on multiple tables at once
• Works with DDS or DDL-defined tables
• No F-specs required for most SQL operations

Example: Retrieve a Customer by Key Using SQL

Exec SQL

   Select CustName

     Into :CustName

     From Customer

    Where CustID = 'C00045';

If SQLCOD = 0;

   Dsply ('Customer: ' + CustName);

EndIf;

When it shines:

• Multi-table joins, aggregations, and filtering
• Returning multiple rows in a single statement
• Writing modern, database-driven applications
• Integrating with SQL-based tools or APIs

---

3. Native vs. SQL — The Key Differences

Aspect	Native RPG I/O	Embedded SQL
Syntax	RPG opcodes (CHAIN, READ)	SQL statements (SELECT, UPDATE)
Access style	Record-by-record	Set-based or record-by-record
Performance	Excellent for keyed single-row	Often better for complex queries
Joins/Aggregations	Manual loops	Native to SQL
Modernization fit	Traditional	Future-facing
Learning curve	Easier for RPG veterans	Requires SQL knowledge

---

4. Commitment Control — The Overlooked Middle Ground

Whether you use native I/O or embedded SQL, commitment control determines how and when your changes are saved to disk.

Key points:

• You must open files with COMMIT(*CS) or *ALL to participate
• Native I/O changes are held until a COMMIT or ROLLBACK
• Embedded SQL automatically participates in the same unit of work when run under commitment control
• Without it, partial updates can be saved even if the program fails mid-process

Example of Mixing Native and SQL Under Commitment Control:

 

Dcl-F Orders Usage(*Update: *Delete) Commit Keyed;

Exec SQL Set Option Commit = *CS;

// Update with native I/O

Chain ('O12345') Orders;

If %Found;

   Status = 'CLOSED';

   Update Orders;

EndIf;

 

// Insert with SQL

Exec SQL

   Insert Into OrderHistory (OrderID, Action)

   Values ('O12345', 'Closed by system');

 

// Commit both at once

Exec SQL Commit;

If either statement fails, you can roll everything back — ensuring data integrity.

---

5. Special Case: When an RPG Program Is Called by a Non-IBM i Java Program

When a Java program (or any remote call) invokes RPG through JDBC or a stored procedure:

• Commitment control is dictated by the caller’s transaction context.
• If the Java code starts a transaction, your RPG program may be part of it.
• SQL statements will participate automatically; native I/O will only participate if the file is opened under commitment control.
• Locks can persist until the Java side issues a commit or rollback.

Pro Tip: Always document commitment expectations in your program’s interface contract.

---

6. More Real-World Examples

Batch Update Using Native I/O

Dcl-F Customer Usage(*Update) Keyed;

Setll *Start Customer;

Read Customer;

Dow Not %Eof;

   Status = 'ACTIVE';

   Update Customer;

   Read Customer;

EndDo;

Fast, efficient, no SQL overhead — perfect for full-file updates.

Multi-Table Join Using SQL

Exec SQL

   Select c.CustName, Sum(o.Amount)

     Into :CustName, :TotalSales

     From Customer c

     Join Orders o

       On c.CustID = o.CustID

    Where o.OrderDate >= Current_Date - 30 Days

    Group By c.CustName;

One SQL statement replaces dozens of native I/O loops and conditional checks.

---

Final Sips

Native RPG is like your trusty drip coffee maker — reliable, predictable, and perfect for simple, consistent brews. Embedded SQL is the espresso machine — capable of pulling bold, complex shots that native I/O can’t match.

The best developers keep both on the counter and know which to grab for the moment. Your role as a leader is to make sure your team understands both, uses commitment control to protect data, and avoids mixing approaches without a clear plan.

☕ The RPG Blend: Real code. Real coffee. No nonsense.