Logical Files were the AS/400 way of providing alternative access paths and simple projections. In the SQL world those jobs are split between Indexes and Views.
Simple keyed Logical File → SQL Index
If the Logical File was basically just a different key order over the same fields, turn it into an index:
ON CUSMAST (CUSNAM, CUSNUM);
Native I/O programs that used to open the Logical File can now open the index (or you keep a thin Logical File that shares the index). The SQL optimizer can also use the same index for queries.
Select/Omit or field-selection Logical → View (often plus Index)
When the Logical File selected only some fields or applied select/omit criteria, create a view:
SELECT CUSNUM, CUSNAM, CUSZIP, CUSCRD
FROM CUSMAST
WHERE CUSCRD > 0;
If programs still need keyed access over that subset, create an index on the underlying table that supports the view’s usage patterns.
Important behavioural differences to watch
- A classic Logical File always returned records in key order. A View does not guarantee order. If your RPG programs rely on sequential order from a Logical, either keep an ordered index access or add explicit ordering when you move to SQL.
- Multi-format Logical Files and some complex join Logical Files do not convert cleanly. Those usually need more design work.
- You can still create a DDS Logical File over an SQL table during the transition period. This is a common “bridge” technique.
Practical conversion sequence for Logical Files
- List all Logical Files over the Physical File you just converted (DSPDBR or the ACS related objects view).
- Decide for each one: pure access path → Index, or projection/filter → View.
- Generate the SQL, create the new objects, then either:
- Point existing programs at the new index/view, or
- Leave a surrogate Logical File with the original name that sits on top of the SQL objects.
- Test both native I/O paths and any SQL that previously used the Logical File.
Once the Logical Files are modernized you have removed another layer of AS/400-era limitations and given the optimizer cleaner, more flexible access paths.