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.
DB2 Version 10.1 for Linux, UNIX, and Windows