SQL on V4: Index Optimization (Hobbyist question)

Hello,

maybe some folks can remember 20 years ago when V4 was still common… So certainly NOT SQE.

I have a PF created with DDS. The important field is a 20 character field. This field is also defined as key field (with a K line in the DDS).

The database contains 484,507 records with all uppercase english words. Goal is to have decent performance to retrieve a list of words according to LIKE pattern matches.

Example: 'METAL__R_I_GS%' => METALWORKINGS (only one match).
Another: 'FL_W____TING%' => FLOWCHARTING, FLOWCHARTINGS.
But also: '______C_U%' => Many matches.
But never: '%SOMESTRING'

Tests within STRSQL showed that the index in the physical file itself wasn't suitable, so a temporary index is built. Obviously this is very slow.

After creating an additional index in SQL like this:

CREATE UNIQUE INDEX ucwrdlst ON ucwrdlstpf (ucword)

the first examples match in a blink of the eye on my slow 150, but for '______C_U%', I see records being counted in the message line for dozens of seconds.

I tinkered a bit with visual explain in the old Operations Navigator. Reason stated was "OPTIMIZER CHOSE TABLE SCAN OVER AVAILABLE INDEXES".

Based on that, I found this page:

https://use-the-index-luke.com/sql/where-clause/searching-for-ranges/like-performance-tuning

and the summary is: Wildcards in front of a search string force a table scan (in DB2). Apparently, I can't work around that. Correct? Do I maybe overlook something?

Side question: Is it possible to create an index "within" the PF satisfying the at least the base LIKE needs? If yes, how?

Reminder, I'm using V4R5 on my 150 for hobbyist purposes.

Thanks a lot!

:wq! PoC