CLOB and searching by it

[db2admin]

Hello all,

I have table with CLOB column length of ~65K. application is searching
from this table by CLOB and i can not create index on it.
I know IBM recommends using DB2 Net Search Extender or DB2 UDB Text
Information Extender for searching CLOBS but what are the other
solutions out there. can i use some other data type and create index
on it ?
any ideas ?

regards,
db2admin

[Serge Rielau]

Re: CLOB and searching by it

db2admin wrote:

Hello all,
>
I have table with CLOB column length of ~65K. application is searching
from this table by CLOB and i can not create index on it.
I know IBM recommends using DB2 Net Search Extender or DB2 UDB Text
Information Extender for searching CLOBS but what are the other
solutions out there. can i use some other data type and create index
on it ?
any ideas ?

Are you searching for headers? LIKE 'xyz%' or any piece in the middle
LIKE '%xyz%'?

In the first case you could define a generated VARCHAR column based on
an appropriate SUBSTR of the CLOB and search that.
To search for any piece in the middle you'd have to write a user defined
functions and indexes are out.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[db2admin]

Re: CLOB and searching by it

On Jun 25, 7:31 am, Serge Rielau <srie...@ca.ibm .comwrote:

db2admin wrote:

Hello all,

>

I have table with CLOB column length of ~65K. application is searching
from this table  by CLOB and i can not create index on it.
I know IBM recommends using DB2 Net Search Extender or DB2 UDB Text
Information Extender for searching CLOBS but what are the other
solutions out there. can i use some other data type and create index
on it ?
any ideas ?

>
Are you searching for headers? LIKE 'xyz%' or any piece in the middle
LIKE '%xyz%'?
>
In the first case you could define a generated VARCHAR column based on
an appropriate SUBSTR of the CLOB and search that.
To search for any piece in the middle you'd have to write a user defined
functions and indexes are out.
>
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

thankyou serge,

the keyword search is looking for anything LIKE '%xyz%'.
can you please explain what would be the nature of user defined
function ?

regards,
db2admin

[Serge Rielau]

Re: CLOB and searching by it

db2admin wrote:

On Jun 25, 7:31 am, Serge Rielau <srie...@ca.ibm .comwrote:

>db2admin wrote:

>>Hello all,
>>I have table with CLOB column length of ~65K. application is searching
>>from this table by CLOB and i can not create index on it.
>>I know IBM recommends using DB2 Net Search Extender or DB2 UDB Text
>>Information Extender for searching CLOBS but what are the other
>>solutions out there. can i use some other data type and create index
>>on it ?
>>any ideas ?

>Are you searching for headers? LIKE 'xyz%' or any piece in the middle
>LIKE '%xyz%'?
>>
>In the first case you could define a generated VARCHAR column based on
>an appropriate SUBSTR of the CLOB and search that.
>To search for any piece in the middle you'd have to write a user defined
>functions and indexes are out.
>>
>Cheers
>Serge
>--
>Serge Rielau
>DB2 Solutions Development
>IBM Toronto Lab

>
thankyou serge,
>
the keyword search is looking for anything LIKE '%xyz%'.
can you please explain what would be the nature of user defined
function ?

Essentially the UDF would take the CLOB and the pattern as input and
you'd have to implement your own search.
There ought to be some open source code flying around that you could use
or perhaps Java provides some classes.
You could also look at ICU:

https://www.ibm.com/software/globalization/icu/index.jsp

There is a regular expression library.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab