Dropping a text search index

When you no longer intend to perform text searches in a text column, you can drop the text search index.

Before you begin

For details, including authorization requirements, see the command description for DROP INDEX or the procedure SYSTS_DROP.

About this task

When you drop a text search index, the following other objects are also dropped:

Index staging and event tables
Triggers on the user table

If the text search index has an associated schedule, make sure no task is running. Otherwise the scheduled task may need to be removed manually.

Always drop the text search indexes on a table before dropping a table space. If you drop table spaces that contains text search indexes, you may create what is called an orphaned collection. When you create a text search index, a collection (the file system representation of the index) is created with an automatically generated name. If the collection remains after the index has been dropped, it can lead to problems with future queries if the following are also true:

the same database connection is being used,
a table is created with the same table name,
a text index with the same name as before is created on this table, and
the same query is reissued as before.

In this case, a cached query plan might be reused, which could result in a wrong query result.

The db2ts CLEANUP FOR TEXT command can only drop obsolete collections and relevant text index catalog records. Administration Tool can be used to remove orphaned collections in this case.

If you plan to drop a database that is enabled for text search, make sure all text search indexes are dropped to avoid orphaned collections.

Procedure

To drop a text search index, use one of the following methods:

Issue the DROP INDEX command:
```
	db2ts "DROP INDEX index-name FOR TEXT"
```
Call the SYSPROC.SYSTS_DROP stored procedure:
```
	CALL SYSPROC.SYSTS_DROP('index-schema', 'index-name', 'locale', ?)
```
Where locale is the five-character locale code, such as en_US, that specifies the language in which messages will be written to the log file.

What to do next

Note: If any orphaned collections exist after you drop a text search index, you can remove them using the Administration Tool.

If, after dropping a text search index, you plan to create a new one on the same text column, you must first disconnect from and then reconnect to the database.