SYSTS_CLEAR_COMMANDLOCKS procedure - Remove command locks for text search indexes

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.

A cleanup is done automatically of all locks associated with processes that are no longer alive. This is done to make a text search index accessible to a new search request. 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.

Authorization

The username for the database connection must have DBADM authority if an index name is not specified. For clearing a command lock on a specific index, the username for the database connection must have CONTROL privilege on the table for which the text search index was created.

Examples

  CALL SYSPROC.SYSTS_CLEAR_COMMANDLOCKS('db2ts', 'myTextIndex', 'en_US', ?)

  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : Operation completed successfully.

  Return Status = 0

  CALL SYSPROC.SYSTS_CLEAR_COMMANDLOCKS('db2ts', 'myTextIndex', 'en_US', ?)

  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.
• The process and thread information in the view SYSIBMTS.TSLOCKS can be used to check if the thread or process that holds the lock still exists. The locks for existing processes belonging to running text search administration procedure or command (for example, SYSTS_UPDATE or UPDATE INDEX) should not be cleared.
• 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 SYSTS_CREATE procedure dies suddenly. It has created some index catalog and collection information, but not all. The command lock is left intact. After clearing the command lock, you can execute the SYSTS_DROP and SYSTS_CREATE procedures.
• When this procedure is run, the content of the DB2 Text Search view SYSIBMTS.TSLOCKS is updated.