This procedure drops an existing text search index associated
with any table column.
After successful execution of this procedure, text search
queries cannot be run on that column.
The procedure issues
a DROP INDEX text search administration command
on the database server.
Syntax
>>-SYSTS_DROP--(--index_schema--,--index_name--,---------------->
>--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.
- 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.
Authorization
The privileges
held by the authorization ID of the statement must include at least
one of the following authorities:
- CONTROL privilege on the table on which the index is defined
- DBADM authority
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', ?)
The following example is a sample 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 'myTextIndex'. This index does not exist and results in
an error.
CALL SYSPROC.SYSTS_DROP('db2ts', 'myTextIndex', 'en_US', ?)
The following example is a sample 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"."myTextIndex" does not exist. ". SQLSTATE 38H14
Usage notes
- Text search administration procedures use an existing
connection to the database. The current transaction might be committed
or rolled back depending on the completion of the procedures. It is
therefore recommended to commit all transaction changes before executing
a text search administration procedure to avoid any unexpected impact
from a commit or rollback in the procedure. One way to achieve this
is to turn on AUTOCOMMIT.
Note: You may run into a deadlock if a transaction
is not committed before executing a text search administration operation,
as the same database objects may be affected and the operation waits
for the previous step to commit. This may occur, for example, if AUTOCOMMIT
is turned off and a table is created followed by creating a text index
without an explicit commit between the two transactions.
- Multiple procedures or commands cannot be executed
concurrently on a text search index if they might conflict. Some of
the conflicting procedures and commands are:
- SYSTS_ALTER procedure or ALTER INDEX db2ts command
- SYSTS_CLEAR_EVENTS procedure or CLEAR EVENTS FOR INDEX db2ts command
- SYSTS_DISABLE procedure or DISABLE DATABASE FOR TEXT db2ts command
- STOP FOR TEXT db2ts command
- SYSTS_UPDATE procedure or UPDATE INDEX db2ts command
If there is a conflict, the procedure returns an SQLCODE -20426
and SQLSTATE 38H13.
- Dropping the user table in DB2 does not trigger dropping of indexes, they must be dropped manually
before or after dropping the table.
- When this procedure is run,
- the text search catalog information is updated. The index staging
and event tables are dropped. Triggers on the user table are deleted.
- the index entry in the Text Search Index Data file is deleted.
The file contains a persistent representation of update schedules
(also empty ones) for each index in the instance.
- the collection associated with the text search index definition
is removed.
- If, after dropping a
text search index, you plan to create a new one on the same text column,
you must first disconnect from and then reconnect to the database
before creating the new text search index.