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
midrange.com
