There is maybe one drawback to case-insensitive indexes - they are quite a bit bigger than *HEX ones. But the tradeoff in performance should be considerable.
-------------- Original message --------------
From: "Wilt, Charles" <WiltC@xxxxxxxxxx>
This is mostly for the archives as I couldnâ??t quickly and easily find a prior
Consider a CUSTOMERS table containing a mixed case CUSTNAME column.
To search it, many will use the UPPER() or LOWER() functions to mono case the
field like so:
Select * from customers
Where upper(custname) = upper(CustomerNameToFind)
However, this results in poor performance since an index canâ??t be used.
A better solution is to create a case insensitive index and perform your search
in a case insensitive
To create the case insensitive index, all you need to do is create the index
from a STRSQL or â??Run SQL
Scriptsâ?? session that is currently set to ignore case.
1) F13 â?? Services
2) Option 1 â?? Changes Session attributes
3) Change â??Sort Sequenceâ?? on the 2nd page to *LANGIDSHR
For iNavâ??s Run SQL Scripts
1) Connectionâ??JDBC Setup
2) Language Tab
3) Change â??Sort Based Onâ?? to Language ID
4) Make sure â??Sharedâ?? radio button under â??Sort Weightâ?? is selected
Now to make use of the indexâ?¦.
For interactive SQL , follow the same steps as above to switch your sessionâ??s
For a stored procedure, UDF, or embedded SQL, add the statement
SET OPTION SRTSEQ = *LANGIDSHR
For JDBC connections, add the follow to the connection string:
jdbc:as400://MYAS400;date format=iso;sort=language;sort weight=shared
CINTASÂ Corporation - IT 92B
This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are not a named
addressee you are hereby notified that you are not authorized to read, print,
retain, copy or disseminate this communication without the consent of the sender
and that doing so is prohibited and may be unlawful. Please reply to the
message immediately by informing the sender that the message was misdirected.
After replying, please delete and otherwise erase it and any attachments from
your computer system. Your assistance in correcting this error is appreciated.
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives