This procedure removes all command locks for a specific
text search index or for all text search indexes in the database.
Authorization
The privileges held by the authorization ID of the statement used
to clear locks on the index must include both of the following authorities:
- SYSTS_MGR role
- DBADM authority or CONTROL privilege on the base table on which
the index is defined
The privileges held by the authorization ID of the statement
used to clear locks on the database connection must include the SYSTS_ADM
role.
Default PUBLIC privilege
None
A command lock is created at the beginning of a text search
index command, and is destroyed when the command has completed. It
prevents undesirable conflict between different commands. Use of this
procedure is required in the rare case that locks remain in place
due to an unexpected system behavior, and need to be cleaned up explicitly.
This procedure issues the CLEAR COMMAND LOCKS text search administration command on the database server.
Syntax
>>-SYSTS_CLEAR_COMMANDLOCKS--(--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 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. If the argument is null
or an empty string, the procedure deletes command locks for all text
search indexes in the 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.
Examples
Example 1: In the following
example, SYSTS_CLEAR_COMMANDLOCKS is issued for a text search index
with
index_schema 'db2ts' and
index_name 'myTextIndex'. 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_CLEAR_COMMANDLOCKS('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_CLEAR_COMMANDLOCKS
is called to clear the command locks for a text search index with
index_schema 'db2ts' and
index_name 'myTextIndex2'. This index does not exist and the
procedure returns an error message.
CALL SYSPROC.SYSTS_CLEAR_COMMANDLOCKS('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
- Text search administration procedures use an
existing connection to the database. It is 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.
- You would invoke this procedure because
the process owning the command lock is dead. In this case, the command
(represented by the lock) may not have completed, and the index may
not be operational. You need to take appropriate action. For example,
the process executing the DROP INDEX command dies suddenly. It has
deleted some index data, but not all the catalog and collection information.
The command lock is left intact. After clearing the DROP INDEX command
lock, you may want to re-execute the SYSTS_DROP procedure. In another
example, the process executing the UPDATE INDEX command is interrupted.
It has processed some documents, but not all, and the command lock
is still in place. After reviewing the text search index status and
clearing the UPDATE INDEX command lock, you can re-execute the UPDATE
INDEX command.
- When this procedure is run, the content of the DB2 Text Search view SYSIBMTS.TSLOCKS is updated.