SYSTS_CLEAR_COMMANDLOCKS procedure - Remove command locks for text search indexes

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

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 database 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 Text Search view SYSIBMTS.TSLOCKS is updated.