DROP INDEX

This command drops a full-text index for a text column. If the cache for the index is activated, it is deleted using this command.

Authorization

According to DB2 catalog views, the userid in this command must have the CONTROL privilege on the table the full-text index was created for. Alternatively, the user can be the database administrator (DBADM).

Alternatively, the database administrator (DBADM), can drop the index as they must be able to disable the database using the FORCE option.

Command syntax

Read syntax diagramSkip visual syntax diagram>>-DROP-INDEX--+------------------+--index-name--FOR-TEXT------->
               '-index-schema-"."-'

>--+----------------------+------------------------------------><
   '-|connection-options|-'

connection-options:

|--+--------------------------------------------------------------+--|
   '-CONNECT-TO--database-name--+-------------------------------+-'
                                '-USER--userid--USING--password-'

Command parameters

index schema
The schema of the text index as specified in the CREATE INDEX command. If no schema is specified, the userid of the DB2 connection is used as the schema name.
index-name
The name of the index as specified in the CREATE INDEX command. With the index schema, it uniquely identifies the full-text index in a database.
CONNECT TO database-name
The name of the database that is target for this command. You can omit this parameter, if DB2DBDFT is set and the user is running the command on the server. Note that the user ID must have the required DB2 authorizations.
USER userid USING password
Use a password and userid to connect to the database. If not specified, a connection is attempted from the current user ID without a password.

Usage

The index is deleted, irrespective of the activation status of its cached table. For additional information, see ACTIVATE CACHE for more information.

Note that the command could not be issued when one of the following commands is running on the index:

Note

Indexes must be manually dropped before or after the user table in DB2 is dropped. If not, the results are not correctly cleaned up.

Changes to the database
  • Change DB2 Net Search Extender catalog views
  • Drop the DB2 index
  • Drop the index log/event tables
  • Delete triggers on the user text table

When using the replication capture tables, entries in the IBMSNAP_PRUNE_SET and IBMSNAP_PRUNCTRNL tables are removed.

Changes to the shared memory
The cached table is deleted.
Changes to the file system
  • The directory <internal index name> is deleted in the index and the work directories of the dropped index
  • Delete a persistent cache for the index