RE: SQL in Java - Performance

> -----Original Message-----
> From: java400-l-bounces@xxxxxxxxxxxx 
> [mailto:java400-l-bounces@xxxxxxxxxxxx]On Behalf Of Franco Biaggi
> Sent: Wednesday, June 29, 2005 2:42 PM
> To: Java Programming on and around the iSeries / AS400
> Subject: Re: SQL in Java - Performance
> 
> 
> Hi,
> from my experience,
> 
> 1. A JDBC query to DB2/400 with a  LIKE '%acb%' is  always  NOT case
> sensitive, I  have similars queries that run in JBoss to 
> DB2/400 and do
> not have any UCASE or UPPER keywords

Not true.  Given the following values in column NAME of TESTTBL
Charles
charles
CHARLES

the following JDBC query:

select name from testtbl.cwlibr where name like '%arl%';

returns only
Charles
charles

unless you have the "sort weight = shared" property set.  Having "sort weight = 
shared" is not the default value.

> 
> 2. A LIKE operation always do not can use any index...

Also not true.  If you have LIKE 'ABC%' an index can be used.  It is only when 
you have the leading % that an index can't be used.

In addition, it is my understanding that the DB2 Text Extender *LICPGM can use 
its own special indexes even with the leading %.


HTH,

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