Guru: SQL and Expanding Subfiles

October 26, 2020 Ted Holt

I’ve heard it said on more than one occasion that SQL does not work as well as record-level access (RLA) when loading subfiles. I understand why people feel that way. They’re usually thinking about repositioning to a key value, and there is no SETLL (Set Lower Limit) op code in an SQL cursor.

Yet I think SQL is better, and today I’d like to share one case that I think presents a good illustration. I had in mind the expanding subfile. There’s a parallel between the two. Consider:

• Every time you clear the subfile, you open a new cursor.
• Every