Hi,

We are trying to perform a very straightforward search but when we
compare it to a standard SQL LIKE the result set is wrong ???

e.g
select p.oid, p.part_number
from jabs.part as p
where
p.part_number like 'LM%'
;

Finds a part_number (amongst others) of 'LMA1010GMB-40'


the 'equivalent' TEXT search (with index built) of the same Table
doesn't ?????
i.e
select p.oid, p.part_number
from jabs.part as p,
TABLE(db2ext.textsearch('"LM%"','DB2EXT','PART_PAR T_NUMBER_IX',0,500,cast(NULL
as bigint))) t
where
t.PrimKey = p.oid
;

Are we misusing the % wild-card in some way ?
are we correct in assuming t.primkey is derived automatically from the
definition of the table i.e p.oid ?

This just seems too fundamental to be a bug.

Any help appreciated.

Paul.

Actually, after further investigation this does look like a
bug/limitation.

The TVF returns the string correctly when the wilcard is applied to
more
of the string.

ie.
......db2ext.textsearch("LMA101%"..........) - finds the part (as
only 2 records match)
......db2ext.textsearch("LMA10%"..........) - finds the part (as
only 2 records match)
......db2ext.textsearch("LMA1%"..........) - finds the part (as only
2 records match).
......db2ext.textsearch("LMA%"..........) - finds the part (as only
2 records match)
......db2ext.textsearch("LM%"..........) - DOESN'T find/include the
part (as 6888 match)

only 1417 of the 6888 matching records are returned, with no error
message!

It appears that maybe there is some limit on the number of records
that can
be returned ??

NB. I actually started using a result limit of 0 rather than the 500
shown
in my previous append.

Any comments would be great, as we are investigating the feasibility
of
using NSE and currently this is a show stopper to going any further
with it.

Many thanks.

Paul.


paul@abacus.co.uk (Paul Reddin) wrote in message news:<1fd2a603.0404150420.216d428c@posting.google. com>...
> Hi,
>
> We are trying to perform a very straightforward search but when we
> compare it to a standard SQL LIKE the result set is wrong ???
>
> e.g
> select p.oid, p.part_number
> from jabs.part as p
> where
> p.part_number like 'LM%'
> ;
>
> Finds a part_number (amongst others) of 'LMA1010GMB-40'
>
>
> the 'equivalent' TEXT search (with index built) of the same Table
> doesn't ?????
> i.e
> select p.oid, p.part_number
> from jabs.part as p,
> TABLE(db2ext.textsearch('"LM%"','DB2EXT','PART_PAR T_NUMBER_IX',0,500,cast(NULL
> as bigint))) t
> where
> t.PrimKey = p.oid
> ;
>
> Are we misusing the % wild-card in some way ?
> are we correct in assuming t.primkey is derived automatically from the
> definition of the table i.e p.oid ?
>
> This just seems too fundamental to be a bug.
>
> Any help appreciated.
>
> Paul.

Paul Reddin wrote:
> Actually, after further investigation this does look like a
> bug/limitation.
>
> .....db2ext.textsearch("LMA%"..........) - finds the part (as only
> 2 records match)
> .....db2ext.textsearch("LM%"..........) - DOESN'T find/include the
> part (as 6888 match)
>
> only 1417 of the 6888 matching records are returned, with no error
> message!
>
> It appears that maybe there is some limit on the number of records
> that can
> be returned ??
>
> NB. I actually started using a result limit of 0 rather than the 500
> shown
> in my previous append.
>
> Any comments would be great, as we are investigating the feasibility
> of
> using NSE and currently this is a show stopper to going any further
> with it.
>
> Many thanks.
>
> Paul.
>
>

I would suggest trying this query and see what happens:

select p.oid, p.part_number
from jabs.part as p
where contains(p.part_number, '"LM%"') = 1
;

Please someone correct me if I'm wrong but wouldn't a normal index be
used in this case for a LIKE comparison? If so, then you may want to
consider simply using LIKE with appropriate indexes.

--
Rob Wilson
rob_wilson_at_@ameritech.net (remove _at_)

Rob,

fyi: I have opened an official PMR on this now too.

Rob Wilson <rob_wilson_at_@ameritech.net> wrote in message news:<NrRfc.182$IO7.96@newssvr16.news.prodigy.com> ...
> I would suggest trying this query and see what happens:
>
> select p.oid, p.part_number
> from jabs.part as p
> where contains(p.part_number, '"LM%"') = 1
> ;

Tried this, actually got exactly the same behaviour of the Table Function.
>
> Please someone correct me if I'm wrong but wouldn't a normal index be
> used in this case for a LIKE comparison?

I don't think so, as the performance of LIKE '%something' is 100x slower
than CONTAINS(..."%something")

Paul Reddin wrote:
> Rob,
>
> fyi: I have opened an official PMR on this now too.
>

Glad to hear it. I am unable to reproduce this is my environment
(although most of my indexes are built on CLOB and VARCHAR(4000) columns):

UDB v8.1.4 for Win2K
NSE " tx9_812" (" tx9_53a itlR3-60 COSLibR5-15") - I believe NSE fixpak 4.

> Rob Wilson <rob_wilson_at_@ameritech.net> wrote in message news:<NrRfc.182$IO7.96@newssvr16.news.prodigy.com> ...
>
>>I would suggest trying this query and see what happens:
>>
>>select p.oid, p.part_number
>>from jabs.part as p
>>where contains(p.part_number, '"LM%"') = 1
>>;
>
>
> Tried this, actually got exactly the same behaviour of the Table Function.
>
>>Please someone correct me if I'm wrong but wouldn't a normal index be
>>used in this case for a LIKE comparison?
>
>
> I don't think so, as the performance of LIKE '%something' is 100x slower
> than CONTAINS(..."%something")

Ah, but that's a different query than LIKE 'something%'!


--
Rob Wilson
rob_wilson_at_ameritech.net (replace _at_ with @)

fyi:

IBM have found the solution to this.

It basically comes down to the "EXPANSION LIMIT" variable. Apparently
, if not specified it defaults to around 1000 (not quite sure of the
full internal implications of this)

Anyway,

Specifying the query as

TABLE(db2ext.textsearch('EXPANSION LIMIT 100000 "LM%"',
'DB2EXT',
'PART_PART_NUMBER_IX',
0,
0,
cast(NULL as bigint))) t

ensures we get the full result set of 8000+ rows.

fwiw:
I'm not convinced the docs. are very clear on this!

Paul


Rob Wilson <rob_wilson_at_@ameritech.net> wrote in message news:<o5Qhc.660$AP4.314@newssvr15.news.prodigy.com >...
> Paul Reddin wrote:
> > Rob,
> >
> > fyi: I have opened an official PMR on this now too.
> >
>