DB2 Version 9.7 for Linux, UNIX, and Windows

Creating a text index

Issue the CREATE INDEX FOR TEXT command once for each column that contains text to be searched.

About this task

You can create a text index on all data types, although there are different requirements for the following 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.

Before you begin

One of the following authority levels is required:
  • CONTROL privilege on the index table
  • INDEX privilege on the table and either IMPLICIT_SCHEMA authority on database or CREATEIN privilege on index schema
  • DBADM authority

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.