SYSTS_DISABLE procedure - Disable current database for text search

The procedure disables Text Search for the current database.

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.

Authorization

The privileges held by the authorization ID of the statement must include the following authorities:

DBADM with DATAACCESS authority.
SYSTS_ADM role

Default PUBLIC privilege

None

Syntax

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.

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 to be returned in English. The message output parameter is set to an informational message string.

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

The following output is an example of 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, Text Search is disabled for a database with existing text search indexes using the SYSTS_DISABLE procedure without specifying the FORCE option. This results in an error message to the caller. It is preferable to drop all existing text search indexes before disabling the Text Search feature or alternatively to specify the FORCE option for the options input parameter value.

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

The following output is an example of sample output from this query.

  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. 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.
Multiple procedures or commands cannot be executed concurrently on a text search index if they might conflict. Some of the conflicting procedures are:
- SYSTS_ALTER procedure
- SYSTS_CLEAR_EVENTS procedure
- SYSTS_DISABLE procedure
- SYSTS_CONFIGURE procedure
- SYSTS_UPDATE procedure
If there is a conflict, the procedure returns an SQLCODE -20426 and SQLSTATE 38H13.
Note: The lock is set at the database level for the SYSTS_DISABLE procedure.
When this procedure is run,
- the 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. See the "db2ts DROP INDEX command" or "SYSTS_DROP procedure" for reference.
This procedure does not influence the Net Search Extender enablement status of the database. It deletes the Text Search catalog tables and views that are created by the SYSTS_ENABLE procedure or the ENABLE FOR TEXT command.
Before dropping a 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.
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 the database system. Here are some cases that may cause orphaned collections:
- When a DROP DATABASE CLP command is executed without running 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.