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
>>-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
- Multiple procedures or commands cannot be executed concurrently
on a text search index if they might conflict. Some of the conflicting
procedures are:
- SYSTS_ALTER procedure
- SYSTS_CLEAR_EVENTS procedure
- SYSTS_DISABLE procedure
- SYSTS_DROP procedure
- SYSTS_UPDATE procedure
- SYSTS_CONFIGURE procedure
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 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.