Creating a text index

Summary

When
Once for each column that contains text to be searched.
Command
CREATE INDEX ... FOR TEXT ... (See the following examples)
Authorization
CONTROL on the table

You can create a text index on supported data types, although there are different requirements for the following data types:

There are also different requirements for creating a text index for a stored procedure search.

When you create a text index, you also create the following objects:

A log table
This keeps track of all changed 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 problems during an update of the text indexes.
Triggers on the user table
These add information to the log table whenever a document in the column is added, deleted, or changed. The information is necessary for index synchronization when indexing time next occurs.

Note that you only create triggers if you create a log table, and the text index is created on a base table and not on views or nickname tables.

To optimize performance and disk space, use the CREATE INDEX command to specify a different tablespace for the tables.

Note

Using the DB2 LOAD command to import your documents can cause problems, as triggers do not fire and incremental indexing of the loaded documents is not possible.

Therefore, it is preferable to use the DB2 IMPORT command as this activates the triggers.

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 on this table is necessary.

The default values for index creation are from the db2ext.dbdefaults view.

If errors occur during indexing, so-called index update events are added to the event table. For example, when a document queued for indexing can not be found. For additional information, see the Event view.

To reverse the changes made by CREATE INDEX, use the DROP INDEX command. See Dropping a text index for this information.

To synchronize the text index with the database, use the following command:

db2text update index DB2EXT.HTMLIDX for text

Note that you can only find documents after synchronization.

Search summary

Depending on the options selected during index creation, different ways of searching are possible: