SYSPROC.SYSTS_DROP

You can call the SYSPROC.SYSTS_DROP stored procedure to drop a text search index that was defined by using the SYSPROC.SYSTS_CREATE stored procedure.

It is recommended that you drop a text search index by using the SYSPROC.SYSTS_DROP stored procedure before dropping the table.

Dropping the view representing the text search index, even as the result of a DROP TABLE CASCADE statement, attempts to drop the text search index. However, because the text search index cannot be dropped under commitment control, the SQL view cannot be dropped under commitment control.

If the text search server cannot be reached, the collection on the server might become orphaned. If that happens, the collection needs to be deleted manually. When the server is available again, use the OmniFind Text Search Server for DB2® for i administration tool to delete the collection on the server.

In Administration tools, you can find information about the tools to identify orphaned indexes and the stored procedure STSPROC.SYSTS_REMOVE or SYSPROC.SYSTS_CLEAR_INDEXES to delete orphaned indexes.

Prerequisites

Before you call the SYSPROC.SYSTS_DROP stored procedure, verify the following prerequisites:
  • DB2 text search functions were started by calling the SYSPROC.SYSTS_START stored procedure.
  • The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).
  • Ensure that the following stored procedures are not running for the text search index that you want to drop: SYSPROC.SYSTS_CREATE, SYSPROC.SYSTS_UPDATE, and SYSPROC.SYSTS_DROP.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following privileges:
  • The following system authorities:
    • The system authorities of *OBJOPR and *OBJEXIST on the text index to be dropped
    • The system authority *EXECUTE on the library that contains the text index to be dropped
  • Administrative authority

For information about the system authorities corresponding to SQL privileges, see GRANT (Table or View Privileges).

Syntax

Read syntax diagramSkip visual syntax diagramSYSTS_DROP(indexSchemanull,indexName)

The schema qualifier is SYSPROC.

Parameters

indexSchema
Identifies the schema of the text search index. If this parameter is null, the value of the CURRENT SCHEMA special register for the invoker is used.

The data type of this parameter is VARCHAR(128).

indexName
Identifies the name of the text search index. The name of the text search index together with the index schema uniquely identifies the text search index in the DB2 subsystem. You must specify a non-null value for this parameter.

The data type of this parameter is VARCHAR(128).