About this task
You
can create a text index on all data types, although there are different
requirements for the following data types:
- Binary data types
- Unsupported data types
There are also different requirements for creating a text
index for stored procedure search.
When you
create a text index, Net
Search Extender automatically
creates the following objects, depending on whether the extended text-maintained
staging infrastructure is enabled for the text index or not.
Note: It is mandatory to specify the
ADMINISTRATION
TABLES IN clause if an index is created on a range partitioned
table. See
CTE0150E for more information.
With
the regular log infrastructure:
- A log table
- This keeps track of all changes to rows in the
user table. Note that if you select the RECREATE INDEX ON
UPDATE option or use replication capture tables, the log
table is not created.
- An event table
- This collects information about all updates and potential problems
during an update of the text indexes.
- Triggers on the user table (added with initial update)
- These add information to the log table whenever a document in
the user table is added, deleted, or changed. The information is necessary
for index synchronization during the next scheduled or manual index
update.
Note that triggers are only created if you create a log
table, and the text index is created on a base table and not on views
or nickname tables.
With the extended log and staging
infrastructure:
- A log table
- This keeps track of updates to the documents.
- An auxiliary staging table
- This keeps track of inserts and deletes.
- An event table
- This collects information about all updates and potential problems
during an update of the text indexes.
- An update trigger on the user table (added during initial update)
- The update trigger adds the primary key of the affected row to
the log table, when a document in the indexed column is updated.
To optimize performance and disk space, the CREATE
INDEX command has an option to specify a different table
space for the tables.
Note: If you use the
LOAD command
to import your documents, the triggers do not fire and incremental
indexing of the loaded documents is not possible with the regular
infrastructure. In this case, it is preferable to use the
DB2
IMPORT command as this activates the triggers.
If the
extended text-maintained infrastructure is configured for the text
index, documents inserted with a load insert operation are captured
in the auxiliary staging table, and incremental indexing is possible.
Example
The following example creates a text index on text column
HTMLFILE in
table
htmltab.
db2text create index DB2EXT.HTMLIDX for text on DB2EXT.HTMLTAB
(HTMLFILE) format HTML
A primary key must exist on this table.
The
default values for index creation are taken from the db2ext.dbdefaults
view.
To reverse the changes made by CREATE INDEX,
use the DROP INDEX command. See Dropping a text index for this information.
To
populate the created index with data from the text column, use the
following command:
db2text update index DB2EXT.HTMLIDX for text
Note
that you can only search for documents successfully after the text
index is synchronized with the table using a
db2text update command.
If
errors occur during indexing, index update event rows are added to
the event table. This happens, for example, when a document queued
for indexing can not be found or if the document format is invalid.
For additional information, see the description of the Event view.
What to do next
Note: Search summary
Depending on the options
selected during index creation, different ways of searching are possible:
- The SQL scalar search functions work on all text indexes, except
those created on views.
- The stored procedure search function only works on text indexes
that are created with a cache.
- The SQL table-valued function works on all text indexes, including
those created on views.