db2ts DROP INDEX command

The db2ts DROP INDEX command drops an existing text search index.

For execution, the command needs to be prefixed with db2ts at the command line.

Authorization

The privileges held by the authorization ID of the statement must include the SYSTS_MGR role and one of the following privileges or authorities:
  • CONTROL privilege on the table on which the index is defined
  • DROPIN privilege on the schema on which the index is defined
  • If the text search index has an existing schedule, the authorization ID must be the same as the index creator, or must have DBADM authority.

Required connection

Database

Command syntax

Read syntax diagramSkip visual syntax diagramDROP INDEXindex-nameFOR TEXTdrop optionsconnection options
connection options
Read syntax diagramSkip visual syntax diagramCONNECT TOdatabase-nameUSERusernameUSINGpassword

Command parameters

DROP INDEX index-name FOR TEXT
The schema and name of the index as specified in the CREATE INDEX command. It uniquely identifies the text search index in a database.
drop_options
Reserved for internal IBM use.
CONNECT TO database-name
This clause specifies the database to which a connection is established. The database must be on the local system. If specified, this clause takes precedence over the environment variable DB2DBDFT. This clause can be omitted if the following statements are all true:
  • The DB2DBDFT environment variable is set to a valid database name.
  • The user running the command has the required authorization to connect to the database server.
USER username USING password
This clause specifies the authorization name and password that are used to establish the connection.

Usage notes

Multiple commands cannot be executed concurrently on a text search index if the command might conflict. If this command is issued while a conflicting command is running, an error occurs and the command fails, after which you can try to run the command again. The following commands are some common conflicting commands:
  • DROP INDEX
  • UPDATE INDEX
  • CLEAR EVENTS FOR INDEX
  • ALTER INDEX
  • DISABLE DATABASE FOR TEXT
A STOP FOR TEXT command that runs in parallel with the DROP operation will not cause a conflicting command message, instead, if the text search server is shut down before DROP has removed the collection, an error will be returned that the text search server is not available.

After a text search index is dropped, text search is no longer possible on the corresponding text column. If you plan to create a new text search on the same text column, you must first disconnect from the database and then reconnect before creating the new text search index.

The db2ts DROP INDEX FOR TEXT command makes the following changes to the database:
  • Updates the Db2® Text Search catalog information.
  • Drops the index staging and event tables.
  • Deletes triggers on the user text table.
  • Destroys the collection associated with the Db2 Text Search index definition.