RE: SQL: how to speed up join tiny file to giant file

I would try a traditional index. Like, CREATE INDEX. In V5r4, there was a
CQE (classic query engine) and an SQE (new query engine). Anyway, SQE
couldn't use traditional logicals until more recent releases. If the
optimizer is choosing to use SQE, it might not be able to use your logical.
There are some tools, like STRDBG and iNavigator that can tell you what
logicals if any were used during the query at that release. You could also
try to force the CQE by selecting against the logical instead of the
physical file, but its been a long time and I don't recall the exact
behavior of those two engines at that level of OS.

Yes there is a LF with a single primary unique key.

I always use SQL first if possible.

But this takes an hour to run in SQL, where RPG would run in a few
seconds - because I would read sequentially thru the tiny file and
then chain to the giant file.

It seems odd that SQL cant figure out to read sequentially thru the
tiny file and then link to the giant file where the keys match.

I was hoping I could force or nudge SQL to read the tiny file as primary.