f



Using Net Search Extender on Typed Tables

Hello,
I have been using IBM Net Search Extender to perform full text searches
on text columns in relational tables in DB2 without any problems until
now. However it doesn't seam to function properly for text indexes
created on attributes of typed tables.

Here are the issued commands:

//create UDT
db2 CREATE TYPE CompMetadata_T AS (dummy Integer,Text CLOB(100K),
Comment CLOB(100K), Description CLOB(100K), Relic DB2XML.XMLVARCHAR)
MODE DB2SQL

//create typed table
db2 CREATE TABLE CompMetadata OF CompMetadata_T (REF IS OID USER
GENERATED, dummy with options not null, primary key(dummy))"

//create the text index
db2text create index comp_comment_idx for text on CompMetadata(Comment)
connect to dbname

//update index
db2text update index comp_comment_idx for text connect to dbname

(the text index was created and updated properly)

 //full text query
select oid from CompMetadata where contains(Comment,'"Hamburg"')=1

(I also tried: select oid from only(CompMetadata) where
contains(Comment,'"Hamburg"')=1)

//then the following error message is returned:
SQL0443N  Routine "*RCH_1K16" (specific name "") has returned an error
SQLSTATE with diagnostic text "CTE0199 No text index corresponding to
column "COMMENT" of table "COMPMETADATA_HIERARCHY". SQLSTATE=38799

Obviously the text index was not created for the automatically
generated hierarchy table. But how can I get it created when I don't
have direct access to the hierarchy table?

Does anyone have an idea how to solve this problem or should I consider
using the Net Search Extender on typed tables impossible.

I would appreciate any hint.

Temenushka Ignatova

0
ignatova
11/23/2005 8:35:12 AM
comp.databases.ibm-db2 12448 articles. 3 followers. arunrocks (9) is leader. Post Follow

8 Replies
9361 Views

Similar Articles

[PageSpeed] 15

ignatova@gmx.de wrote:
> Hello,
> I have been using IBM Net Search Extender to perform full text searches
> on text columns in relational tables in DB2 without any problems until
> now. However it doesn't seam to function properly for text indexes
> created on attributes of typed tables.
> 
> Here are the issued commands:
> 
> //create UDT
> db2 CREATE TYPE CompMetadata_T AS (dummy Integer,Text CLOB(100K),
> Comment CLOB(100K), Description CLOB(100K), Relic DB2XML.XMLVARCHAR)
> MODE DB2SQL
> 
> //create typed table
> db2 CREATE TABLE CompMetadata OF CompMetadata_T (REF IS OID USER
> GENERATED, dummy with options not null, primary key(dummy))"
> 
> //create the text index
> db2text create index comp_comment_idx for text on CompMetadata(Comment)
> connect to dbname
> 
> //update index
> db2text update index comp_comment_idx for text connect to dbname
> 
> (the text index was created and updated properly)
> 
>  //full text query
> select oid from CompMetadata where contains(Comment,'"Hamburg"')=1
> 
> (I also tried: select oid from only(CompMetadata) where
> contains(Comment,'"Hamburg"')=1)
> 
> //then the following error message is returned:
> SQL0443N  Routine "*RCH_1K16" (specific name "") has returned an error
> SQLSTATE with diagnostic text "CTE0199 No text index corresponding to
> column "COMMENT" of table "COMPMETADATA_HIERARCHY". SQLSTATE=38799
> 
> Obviously the text index was not created for the automatically
> generated hierarchy table. But how can I get it created when I don't
> have direct access to the hierarchy table?
> 
> Does anyone have an idea how to solve this problem or should I consider
> using the Net Search Extender on typed tables impossible.
> 
> I would appreciate any hint.
> 
> Temenushka Ignatova
>
I recommend opening a PMR with support.

Cheers
Serge
-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
11/23/2005 8:53:30 AM
There is a limitation on typed tables that you cannot add columns to
them.

Check whether Index creation adds a new column - this can be the reason
for it not to work.
(XML Extender cannot work with typed tables either)

0
juliane26
11/23/2005 10:13:58 AM
Serge Rielau wrote:
> ignatova@gmx.de wrote:
>> Hello,
>> I have been using IBM Net Search Extender to perform full text searches
>> on text columns in relational tables in DB2 without any problems until
>> now. However it doesn't seam to function properly for text indexes
>> created on attributes of typed tables.
>>
>> Here are the issued commands:
>>
>> //create UDT
>> db2 CREATE TYPE CompMetadata_T AS (dummy Integer,Text CLOB(100K),
>> Comment CLOB(100K), Description CLOB(100K), Relic DB2XML.XMLVARCHAR)
>> MODE DB2SQL
>>
>> //create typed table
>> db2 CREATE TABLE CompMetadata OF CompMetadata_T (REF IS OID USER
>> GENERATED, dummy with options not null, primary key(dummy))"
>>
>> //create the text index
>> db2text create index comp_comment_idx for text on CompMetadata(Comment)
>> connect to dbname
>>
>> //update index
>> db2text update index comp_comment_idx for text connect to dbname
>>
>> (the text index was created and updated properly)
>>
>>  //full text query
>> select oid from CompMetadata where contains(Comment,'"Hamburg"')=1
>>
>> (I also tried: select oid from only(CompMetadata) where
>> contains(Comment,'"Hamburg"')=1)
>>
>> //then the following error message is returned:
>> SQL0443N  Routine "*RCH_1K16" (specific name "") has returned an error
>> SQLSTATE with diagnostic text "CTE0199 No text index corresponding to
>> column "COMMENT" of table "COMPMETADATA_HIERARCHY". SQLSTATE=38799
>>
>> Obviously the text index was not created for the automatically
>> generated hierarchy table. But how can I get it created when I don't
>> have direct access to the hierarchy table?
>>
>> Does anyone have an idea how to solve this problem or should I consider
>> using the Net Search Extender on typed tables impossible.
>>
>> I would appreciate any hint.
>>
>> Temenushka Ignatova
>>
> I recommend opening a PMR with support.
> 
> Cheers
> Serge

The Net Search Extender User Guide says (somewhere around page 34/35):

"To create an index, the text columns must be one of the following data 
types: CHAR, VARCHAR, LONG VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, LONG 
VARGRAPHIC, DBCLOB, BLOB, DATALINK

If the documents are in a column of a different type, such as a 
user-defined type (UDT), you must provide a function that takes the user 
type as input and provides as an output type one of the above-mentioned 
types."

So, the steps to make your example run are to specify a UDF, say, 
"get_comment" that takes your UDT as input and returns the Comment part 
of it as a CLOB, and then specify the name of this UDF in the CREATE 
INDEX statement.

CREATE INDEX comp_comment_idx for text ON CompMetaData(get_comment(Comment))

The contains UDF then still uses 'comment' as its first argument (no 
transformation function to be specified there).

select oid from CompMetadata where contains(Comment,'"Hamburg"')=1

should return the rows that have "Hamburg" in the comment part of your 
UDT column.

If it doesn't, follow Serge's advice :-)

Cheers,

-- stefan
0
Stefan
11/23/2005 10:28:29 PM
juliane26 wrote:
> There is a limitation on typed tables that you cannot add columns to
> them.
> 
> Check whether Index creation adds a new column - this can be the reason
> for it not to work.
> (XML Extender cannot work with typed tables either)
> 

Net Search Extender index creation does not add columns to your table. 
Text Extender (its pre-predecessor) used to do that.

Cheers,

-- stefan
0
Stefan
11/23/2005 10:31:14 PM
Stefan Momma wrote:

> Serge Rielau wrote:
>> ignatova@gmx.de wrote:
>>> Hello,
>>> I have been using IBM Net Search Extender to perform full text searches
>>> on text columns in relational tables in DB2 without any problems until
>>> now. However it doesn't seam to function properly for text indexes
>>> created on attributes of typed tables.
>>>
>>> Here are the issued commands:
>>>
>>> //create UDT
>>> db2 CREATE TYPE CompMetadata_T AS (dummy Integer,Text CLOB(100K),
>>> Comment CLOB(100K), Description CLOB(100K), Relic DB2XML.XMLVARCHAR)
>>> MODE DB2SQL
>>>
>>> //create typed table
>>> db2 CREATE TABLE CompMetadata OF CompMetadata_T (REF IS OID USER
>>> GENERATED, dummy with options not null, primary key(dummy))"
>>>
>>> //create the text index
>>> db2text create index comp_comment_idx for text on CompMetadata(Comment)
>>> connect to dbname
>>>
>>> //update index
>>> db2text update index comp_comment_idx for text connect to dbname
>>>
>>> (the text index was created and updated properly)
>>>
>>>  //full text query
>>> select oid from CompMetadata where contains(Comment,'"Hamburg"')=1
>>>
>>> (I also tried: select oid from only(CompMetadata) where
>>> contains(Comment,'"Hamburg"')=1)
>>>
>>> //then the following error message is returned:
>>> SQL0443N  Routine "*RCH_1K16" (specific name "") has returned an error
>>> SQLSTATE with diagnostic text "CTE0199 No text index corresponding to
>>> column "COMMENT" of table "COMPMETADATA_HIERARCHY". SQLSTATE=38799
>>>
>>> Obviously the text index was not created for the automatically
>>> generated hierarchy table. But how can I get it created when I don't
>>> have direct access to the hierarchy table?
>>>
>>> Does anyone have an idea how to solve this problem or should I consider
>>> using the Net Search Extender on typed tables impossible.
>>>
>>> I would appreciate any hint.
>>>
>>> Temenushka Ignatova
>>>
>> I recommend opening a PMR with support.
>> 
>> Cheers
>> Serge
> 
> The Net Search Extender User Guide says (somewhere around page 34/35):
> 
> "To create an index, the text columns must be one of the following data
> types: CHAR, VARCHAR, LONG VARCHAR, CLOB, GRAPHIC, VARGRAPHIC, LONG
> VARGRAPHIC, DBCLOB, BLOB, DATALINK
> 
> If the documents are in a column of a different type, such as a
> user-defined type (UDT), you must provide a function that takes the user
> type as input and provides as an output type one of the above-mentioned
> types."
> 
> So, the steps to make your example run are to specify a UDF, say,
> "get_comment" that takes your UDT as input and returns the Comment part
> of it as a CLOB, and then specify the name of this UDF in the CREATE
> INDEX statement.
> 
> CREATE INDEX comp_comment_idx for text ON
> CompMetaData(get_comment(Comment))
> 
> The contains UDF then still uses 'comment' as its first argument (no
> transformation function to be specified there).
> 
> select oid from CompMetadata where contains(Comment,'"Hamburg"')=1
> 
> should return the rows that have "Hamburg" in the comment part of your
> UDT column.

The thing is that there are no UDTs involved but rather typed tables.  So
its not the issue to dive into the structure itself.

Also, no hierarchy tables are involved in the example, so I would not think
this is the issue.  As Serge suggested, a PMR appears to be the first
logical step.

-- 
Knut Stolze
DB2 Information Integration Development
IBM Germany
0
Knut
11/24/2005 8:30:12 AM
Hi,
Thanks for the advice.

The response from IBM was that, NSE on typed tables is not supported by
the current versions of DB2 and NSE, and that perhaps this problem will
be repaired in the next FixPacks.

--
Temenushka Ignatova

University of Rostock
Department of Computer Science
Database Research Group

0
ignatova
12/1/2005 2:02:19 PM
ignatova@gmx.de wrote:
> Hi,
> Thanks for the advice.
> 
> The response from IBM was that, NSE on typed tables is not supported by
> the current versions of DB2 and NSE, and that perhaps this problem will
> be repaired in the next FixPacks.
> 
> --
> Temenushka Ignatova
> 
> University of Rostock
> Department of Computer Science
> Database Research Group
> 
Uni Rostock.. Unge-Heuer-liche Anforderungen. Wenn das mal Knut geht....

Gruss an Prof Heuer (Stichwort: EDBT 2000)
Serge
-- 
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
0
Serge
12/1/2005 3:04:15 PM
Serge Rielau wrote:

> Uni Rostock.. Unge-Heuer-liche Anforderungen. Wenn das mal Knut geht....

Ich habe nix direkt mit A. Heuer zu tun.  Ich komme schliesslich von einer
feindlichen Uni. ;-)

-- 
Knut Stolze
DB2 Information Integration Development
IBM Germany
0
Knut
12/1/2005 4:54:49 PM
Reply: