SYSTS_DISABLE procedure - Disable current database for text search

Once the Text Search feature has been disabled, text search indexes and commands are no longer available for use with the database.

The procedure issues a DISABLE DATABASE FOR TEXT text search administration command on the database server.

Syntax

>>-SYSTS_DISABLE--(--options--,--message_locale--,--message--)-><

The schema is SYSPROC.

Procedure parameters

options

An input argument of type VARCHAR(128) that specifies the options to be used when disabling the database. The argument can be set to FORCE. When this value is specified, all indexes are dropped and the Text Search feature is disabled by force. No text search indexes are preserved and no error message or warning is returned. If the argument is null or an empty string, an attempt is made to disable the Text Search feature for the database.

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 DBADM authority.

Examples

Example 1: In the following example, Text Search is disabled for a database using the SYSTS_DISABLE procedure. The FORCE option is specified to ensure that the feature is disabled even if text search indexes still exist on tables in the database. Error messages are specified requested to be returned in English. The message output parameter is set to an informational message string.

  CALL SYSPROC.SYSTS_DISABLE('FORCE', 'en_US', ?)

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

  Return Status = 0

  CALL SYSPROC.SYSTS_DISABLE('', 'en_US', ?)

  SQL20427N An error occurred during a text search administration 
  procedure or command. The error message is "CIE00326 Text search 
  index active in specified or default database. ". 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. As such, you might want to commit all transaction changes to avoid any unexpected impact from such a commit or rollback. 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.
• When this procedure is run,
  • the DB2 Text Search catalog information is updated. The index log and event tables are dropped. Triggers on the user text table are deleted.
  • if the FORCE option is specified, all text index information is removed from the database and all associated collections are deleted. In addition, the text service is updated to remove any remaining update schedule information. See the "db2ts DROP INDEX command" or "SYSTS_DROP procedure" for reference.
• This procedure does not influence the DB2 Net Search Extender enablement status of the database. It deletes the DB2 Text Search catalog tables and views that are created by the SYSTS_ENABLE procedure or the ENABLE FOR TEXT command.
• Before dropping a DB2 database that has text search index definitions, run this procedure and make sure that the text indexes and collections have been removed successfully.
• If some indexes could not be deleted using the FORCE option, the collection names are written to the db2diag log files. If the text search index procedure SYSTS_DISABLE or the command DISABLE DATABASE FOR TEXT is not executed before the CLP command DROP DATABASE, the text search index services must also be cleaned up using the CLEANUP FOR TEXT command. See the SYSTS_DROP procedure or DROP INDEX command for more about dropping indexes, and the CLEANUP FOR TEXT command for information about text search collections and their relationship to text search indexes.
  Note: The user is discouraged from usage that results in orphaned collections, that is, collections that remain defined on the text search server but are not used by DB2. Here are some cases that may cause orphaned collections:

  • When a DROP DATABASE CLP command or DROP TABLE statement is executed without running the SYSTS_DISABLE procedure or a DISABLE DATABASE FOR TEXT command.
  • When the SYSTS_DISABLE procedure is run or a DISABLE DATABASE FOR TEXT command is executed using the FORCE option.
  • Some other error conditions. The CLEANUP FOR TEXT command can be used in some scenarios.