× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. JAVA400-L
  3. June 2005

RE: SQL In Java

Nigel,

Short answer: no.

Long answer: IBM's DB2 text extender *LICPGM (5722-DE1) may be of help.  From 
what I understand, it provides some improvement to text searching in DB2.  
5722-DE1 option 3 is in fact named "Text Search Engine".


Complex answer: it seems to me you've got a error in you DB design.  While the 
DBMS might accept 'ABC' being different than ' ABC', it doesn't seem to me that 
the business process should/would.  I'd trim off any leading blanks in the 
field before trying to write it to the DB.  Now if the business process 
actually requires a unique value, the users would need to use 'ABC' and 'ABC 
#2'.  If the business process doesn't really need a unique value, you can 
remove the constraint and they can have as many 'ABC' as they want.


Just my .02

Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx
> [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of NGay@xxxxxxxxxxxxx
> Sent: Wednesday, June 29, 2005 11:03 AM
> To: java400-l@xxxxxxxxxxxx
> Subject: RE: SQL In Java
> 
> 
> 
> Charles,
> 
> I didn't know you could create indices in specific sort modes 
> like that.
> Where we've needed fast case insentive searching, I've duplicated the
> field, so instead of
> 
> select * from table where upper (field) = 'VALUE'
> 
> if "field" is say 50 long, I created 2nd field called 
> "upperfield", also 50
> long, that is always set to the same value as field, but 
> shifted into upper
> case.  Then we can create an index over it and do
> 
> select * from table where upperfield = 'VALUE'
> 
> and it'll use the index.  I've also done similar for 
> situations where I
> need to trim the field value.  There's some fields in our database in
> particular where because the fields are enforced to be 
> unique, the users
> put spaces at the front to bypass this :-|  e.g. "ABC", " 
> ABC" and "  ABC",
> but then they want to be able to do a search for = "ABC" and 
> still find all
> 3 records.  So again I made an uppercased & trimmed copy of 
> the field value
> and created an index over this - or do you have any 
> alternative suggestions
> to speed up trim () queries as well?
> 
> Thanks,
> 
> Nigel Gay.
> 
> 
> 
> |---------+------------------------------>
> |         |           "Wilt, Charles"    |
> |         |           <CWilt@xxxxxxxxxxxx|
> |         |           >                  |
> |         |           Sent by:           |
> |         |           java400-l-bounces@m|
> |         |           idrange.com        |
> |         |                              |
> |         |                              |
> |         |           29/06/2005 11:57 AM|
> |         |           Please respond to  |
> |         |           Java Programming on|
> |         |           and around the     |
> |         |           iSeries / AS400    |
> |         |                              |
> |---------+------------------------------>
>   
> >-------------------------------------------------------------
> -------------------------------------------------|
>   |                                                           
>                                                    |
>   |       To:       "Java Programming on and around the 
> iSeries / AS400" <java400-l@xxxxxxxxxxxx>                |
>   |       cc:                                                 
>                                                    |
>   |       Subject:  RE: SQL In Java                           
>                                                    |
>   
> >-------------------------------------------------------------
> -------------------------------------------------|
> 
> 
> 
> 
> Note that josep's suggestion of
> 
> jdbc:as400://AS400;date format=iso;sort=language;sort weight=shared
> 
> Will perform better than using upper().
> 
> By using upper(), the query engine can't make use of any indexs.
> 
> You'll want to create an index from a session with sort 
> weight = shared.
> (or via STRSQL, hit F13 and change the "Sort sequence" attribute to
> *LANGIDSHR).
> 
> 
> HTH,
> 
> Charles Wilt
> --
> iSeries Systems Administrator / Developer
> Mitsubishi Electric Automotive America
> ph: 513-573-4343
> fax: 513-398-1121
> 
> 
> > -----Original Message-----
> > From: java400-l-bounces@xxxxxxxxxxxx
> > [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Eric Lee
> > Sent: Wednesday, June 29, 2005 8:13 AM
> > To: Java Programming on and around the iSeries / AS400
> > Cc: Java Programming on and around the iSeries / AS400;
> > java400-l-bounces@xxxxxxxxxxxx
> > Subject: Re: SQL In Java
> >
> >
> > Ron,
> >
> > Why don't you just change your statement to account for case?
> >
> > eg.
> > select * from table where upper(tabledata) like upper(searchstring)
> >
> > Eric
> >
> >
> >
> >
> > RPower@xxxxxxxxxx
> > Sent by: java400-l-bounces@xxxxxxxxxxxx
> > 29/06/2005 10:39
> > Please respond to
> > Java Programming on and around the iSeries / AS400
> > <java400-l@xxxxxxxxxxxx>
> >
> >
> > To
> > "Java Programming on and around the iSeries / AS400"
> > <java400-l@xxxxxxxxxxxx>
> > cc
> >
> > Subject
> > SQL In Java
> >
> >
> >
> >
> >
> >
> > Hitting a DB2, and trying to get the SQL statement to ignore
> > the case.  Is
> >
> > that possible?
> >
> > Ron Power
> > Programmer
> > Information Services
> > City Of St. John's, NL
> > P.O. Box 908
> > St. John's, NL
> > A1C 5M2
> > Tel: 709-576-8132
> > Email: rpower@xxxxxxxxxx
> > Website: http://www.stjohns.ca/
> > ______________________________________________________________
> > _____________
> > Success is going from failure to failure without a loss of
> > enthusiasm. -
> > Sir Winston Churchill
> > --
> > This is the Java Programming on and around the iSeries /
> > AS400 (JAVA400-L)
> > mailing list
> > To post a message email: JAVA400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/java400-l
> > or email: JAVA400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/java400-l.
> >
> >
> >
> >
> > **************************************************************
> > ****************************
> > 'This e-mail message is intended solely for the person to
> > whom it is addressed and may contain confidential
> > or privileged information. If you have received it in error,
> > please notify postmaster@xxxxxxxxxxxx and
> > destroy this e-mail and any attachments. In addition, you
> > must not disclose, copy, distribute or take any
> > action in reliance on this e-mail or any attachments. Any
> > views or opinions presented in this e-mail are
> > solely those of the author and do not necessarily represent
> > those of the company. When addressed to our
> > customers, any quotations contained in this e-mail are
> > subject to contract and are on the terms of The
> > Company's standard Conditions, a copy of which is available
> > on request. Any errors or omissions in any
> > quotations or other information issued by The Company shall
> > be subject to correction without any liability
> > on the part of The Company. Due to the nature of Internet
> > communications, DCS cannot guarantee that this
> > communication, or any attachments, do not contain software
> > viruses. We have taken every precaution to
> > minimize this probability but cannot accept any liability for
> > damage which you may sustain as a result of
> > software viruses. We recommend you carry out your own virus
> > checks before opening attachments.'
> >
> > DCS Transport & Logistics Solutions Ltd.
> > Registered office: Newstead House, Lake View Drive, Sherwood
> > Park, Annesley, Nottinghamshire, NG15 0DT
> > Registered in England no: 3373490
> > **************************************************************
> > ****************************
> >
> > --
> > This is the Java Programming on and around the iSeries /
> > AS400 (JAVA400-L) mailing list
> > To post a message email: JAVA400-L@xxxxxxxxxxxx
> > To subscribe, unsubscribe, or change list options,
> > visit: http://lists.midrange.com/mailman/listinfo/java400-l
> > or email: JAVA400-L-request@xxxxxxxxxxxx
> > Before posting, please take a moment to review the archives
> > at http://archive.midrange.com/java400-l.
> >
> >
> 
> --
> This is the Java Programming on and around the iSeries / 
> AS400 (JAVA400-L)
> mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> 
> 
> 
> 
> 
> 
> 
> 
> **************************************************************
> ******************
> The information in this message is confidential and may be legally
> privileged. It is intended solely for the addressee; access to this
> email by anyone else is unauthorised.
> 
> If you are not the intended recipient: (1) you are kindly requested
> to return a copy of this message to the sender indicating that you
> have received it in error, and to destroy the received copy; and (2)
> any disclosure or distribution of this message, as well as any action
> taken or omitted to be taken in reliance on its content, is prohibited
> and may be unlawful.
> **************************************************************
> ******************
> 
> -- 
> This is the Java Programming on and around the iSeries / 
> AS400 (JAVA400-L) mailing list
> To post a message email: JAVA400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/java400-l
> or email: JAVA400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/java400-l.
> 
>

As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.