Using Net Search Extender on Typed Tables

[ignatova]

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

[Serge Rielau]

ig******@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

[juliane26]

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)

[Stefan Momma]

Serge Rielau wrote:

ig******@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

[Stefan Momma]

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

[Knut Stolze]

Stefan Momma wrote:

Serge Rielau wrote:

ig******@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

[ignatova]

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

[Serge Rielau]

ig******@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

[Knut Stolze]

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