Good, time tested solution for case insensitive searches, Charles. Nice of
you to include it for the archive.
And with V6R1 it can be handled by the new SQE query engine.
As an alternative, I've also seen applications that add additional clone
column(s) to the table that contain uppercased version of the data and then
build an index over it (*HEX in this solution). With this solution, their
case insensitive queries would go against the cloned, uppercased column, and
leverage an index they've built over it.
To ensure real-time uppercasing on inserts & updates, you could instrument
the application code, or add a trigger on the original column if application
code is off-limits.
HTH, Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com
-----Original Message-----
Subject: RE: Case insensitive searching with SQL
For a stored procedure, UDF, or embedded SQL, add the
statement SET OPTION SRTSEQ = *LANGIDSHR
I believe (not on i right now) that SRTSEQ is also an option on the
CRTxxxPGM commands.
Also, IIUIC, older releases of SQE cannot use an index marked that way so
you'll be forced down CQE, but I believe that's now resolved (5.4, 6.1, not
sure which? I sure Elvis or Birgitta will chime in :-) )
-Walden
midrange.com
