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
sort sequence.

For a stored procedure, UDF, or embedded SQL, add the statement

For JDBC connections, add the follow to the connection string:
sort=language;sort weight=shared

jdbc:as400://MYAS400;date format=iso;sort=language;sort weight=shared

Charles WiltÂ
Software Engineer
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,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.

This thread ...


Return to Archive home page | Return to MIDRANGE.COM home page