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.XMLVARCH AR)
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_id x for text on CompMetadata(Co mment)
connect to dbname
//update index
db2text update index comp_comment_id x for text connect to dbname
(the text index was created and updated properly)
//full text query
select oid from CompMetadata where contains(Commen t,'"Hamburg"')= 1
(I also tried: select oid from only(CompMetada ta) where
contains(Commen t,'"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_H IERARCHY". 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 8 3476 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.XMLVARCH AR) 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_id x for text on CompMetadata(Co mment) connect to dbname
//update index db2text update index comp_comment_id x for text connect to dbname
(the text index was created and updated properly)
//full text query select oid from CompMetadata where contains(Commen t,'"Hamburg"')= 1
(I also tried: select oid from only(CompMetada ta) where contains(Commen t,'"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_H IERARCHY". 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
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)
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.XMLVARCH AR) 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_id x for text on CompMetadata(Co mment) connect to dbname
//update index db2text update index comp_comment_id x for text connect to dbname
(the text index was created and updated properly)
//full text query select oid from CompMetadata where contains(Commen t,'"Hamburg"')= 1
(I also tried: select oid from only(CompMetada ta) where contains(Commen t,'"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_H IERARCHY". 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_commen t" 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_id x for text ON CompMetaData(ge t_comment(Comme nt))
The contains UDF then still uses 'comment' as its first argument (no
transformation function to be specified there).
select oid from CompMetadata where contains(Commen t,'"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
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
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.XMLVARCH AR) 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_id x for text on CompMetadata(Co mment) connect to dbname
//update index db2text update index comp_comment_id x for text connect to dbname
(the text index was created and updated properly)
//full text query select oid from CompMetadata where contains(Commen t,'"Hamburg"')= 1
(I also tried: select oid from only(CompMetada ta) where contains(Commen t,'"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_H IERARCHY". 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_commen t" 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_id x for text ON CompMetaData(ge t_comment(Comme nt))
The contains UDF then still uses 'comment' as its first argument (no transformation function to be specified there).
select oid from CompMetadata where contains(Commen t,'"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
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 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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Stanley Sinclair |
last post by:
I have a table in V8.1.2 (Windows) which contains about 17,000
constant rows. One column contains a string of about 20 English
words. I need to find all rows which contain three words specified by
their first letters.
For example, if I am looking for rows containing Jon (or Jonathan) and
Will (or William) and Terry (or Terri or Terrance), the SQL would be:
SELECT col1 FROM MYTAB WHERE col2 LIKE '%Jon%' AND col2 LIKE '%Will%'
AND...
|
by: Stanley Sinclair |
last post by:
Bear with me. I am being very calm; took a Valium. I have waited two
weeks to write this, because every time I wrote it before the message
was, at best, nasty.
I need to use the services of NET SEARCH EXTENDER. This is the first
time in over five years of IBM (DB2) developing that I need this.
I don't know how to do it. (Db2 v8.1.2, Windows) I see that I must
deal with indexes, with cache, with other stuff.
|
by: Philip Nelson |
last post by:
Folks,
I've been exercising my mind recently about the complexities of implementing
a "currency" data type within DB2 to cope with multiple currencies.
A monetary value is often simply represented as a DECIMAL column : for
example many times I've seen DECIMAL(12,2) used. The issue with this is
that there is nothing to interpret what currency this relates to (US
dollars, Canadian dollars, Euros, British pounds or whatever), and a...
|
by: Lan W via DBMonster.com |
last post by:
Hi,
I try to install Db2 Net Search Extender on my Db2 workgroup server edition
on windows machine. When I verify the installation from command window as
typed >nsesampla.bat sample, I received the following error:
DB2text.exe - Entry point not found. The procedure entry point sqlogmblk
could not be located in the dynamic link library DB2SYS.dll. Does anyone have
idea onhow to fix it?
Thanks very much!
|
by: Ralf Gross |
last post by:
Hi,
I'm looking for the Net Search Extender version for linux. We have a
company license for db2 and I got a white box with about
100 CDs in it. I think it's the 'development edition' (IBM DB2 Universal
DEV. ED. 8.1 Media Pack Deutsch). I successfully installed 8.1 with FP 10.
In the box I found the Net Search Extender disc for Windows and Unix
operation systems, but nothing for linux.
| |
by: Pete |
last post by:
I'm currently doing a database that uses comboboxes to look up records
in other tables, whether they be lookup tables or otherwise. When a
user needs to add an item to one of these tables, the user has to
either double-click on the combobox or go to the appropriate form via
an item on the main menu.
So, using an example, if someone is entering enrollment information,
they would definitely need a student and a course. If the course has...
|
by: 2803stan |
last post by:
I hear that DB2 V9.1 has dropped, or will soon drop Net Search
Extender.
I have occasion to require quick search for a particular word of
several columns of data, and very many rows.
What is there to replace this indexing feature?
SS
|
by: pike |
last post by:
Hi
Can anyone confirm whether DB2 9 Net Search Extender is compatible
with DB2 v8.2? If it isn't, can someone provide me with a link to the
trial software download page for a version of DB2 Net Search Extender
that is compatible?
Thank you.
|
by: William Youngman |
last post by:
We are developing an application that presents data to the user in a gridview and we are using the dropdown extender to give the user a SharePoint 2007 type dropdown menu attached to the cells of a given column. We are also using another dropdown menu that the user can use to select data using another quesry using the SelectedIndex change method. Upon initial page load everything works fine and the user is presented with a SharePoint type menu....
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
| |
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |