DB2 Version 10.1 for Linux, UNIX, and Windows

SYSTS_DROP procedure - Drop text search index

The SYSTS_DROP procedure drops an existing text search index associated with any table column.

After this procedure runs successfully, text search queries cannot be run on the column for which the text search index was dropped.

The procedure issues a DROP INDEX text search administration command on the database server.

Authorization

The privileges held by the authorization ID of the statement must include the SYSTS_MGR role and at least 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

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagram
>>-SYSTS_DROP--(--index_schema--,--index_name--,--options------->

>--message_locale--,--message--)-------------------------------><

The schema is SYSPROC.

Procedure parameters

index_schema
An input argument of type VARCHAR(128) that specifies the schema of the text search index. The index_schema must follow the naming restriction for DB2® schema names. If the argument is null or an empty string, the value of CURRENT SCHEMA is used. The index_schema is case-sensitive.
index_name
An input argument of type VARCHAR(128) that specifies the name of the index. Together with index_schema, it uniquely identifies a text search index in a database. The index_name is case-sensitive.
options
An input argument of type VARCHAR(32000) that specifies the options to be used. If no options are needed, the argument can be null or an empty string.
message_locale
An input argument of type VARCHAR(33) that specifies the locale to be used for any error message returned. If the argument is null or an empty string, or the message files for the specified locale are not available on the server, 'en_US' is used.
message
An output argument of type VARCHAR(32K) that specifies a warning or informational message for a successfully completed operation.

Examples

Example 1: In the following example, the text search index that was created with index_schema 'db2ts' and index_name 'myTextIndex' is being dropped. Any error messages are requested to be returned in English. When the procedure succeeds, the output parameter message indicative of the successful operation is returned to the caller.

  CALL SYSPROC.SYSTS_DROP('db2ts', 'myTextIndex', '', 'en_US', ?)
As in previous releases, the SYSTS_DROP procedure is supported without the options argument, for example :
  CALL SYSPROC.SYSTS_DROP('db2ts', 'myTextIndex', 'en_US', ?)
The following is an example of output from this query.
  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : Operation completed successfully.

  Return Status = 0

Example 2: In the following example, SYSTS_DROP is called to drop a text search index with index_schema 'db2ts' and index_name 'myTextIndex2'. This index does not exist and results in an error.

  CALL SYSPROC.SYSTS_DROP('db2ts', 'myTextIndex2', 'en_US', ?)
The following is an example of output from this query.
  SQL20427N An error occurred during a text search administration 
  procedure or command. The error message is "CIE00316 Text search 
  index "db2ts"."myTextIndex2" does not exist. ". SQLSTATE 38H14

Usage notes