SYSTS_CLEANUP procedure - Remove invalid text search indexes

This procedure removes invalid text search indexes and their associated collections from the database.

An index can become invalid when database operations are executed that affect all of the table content, for example, truncate.

The procedure serves as an alternative to the db2ts CLEANUP FOR TEXT command for the database scope. To remove obsolete collections on the instance level, the command must be used.

Authorization

The privileges held by the authorization ID of the statement must include the SYSTS_ADM role with DBADM and DATAACCESS authority.

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagramSYSTS_CLEANUP(options, message_locale,message )

The schema is SYSPROC.

Procedure parameters

options
An input argument of type VARCHAR(32K). Only a NULL or an empty parameter is accepted. Reserved for internal IBM use.
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.

Example

Example 1: In the following example, all invalid text search indexes in the current database are dropped. The message language is set to English, and when the procedure succeeds, the output parameter message indicative of the successful operation is returned to the caller. 
CALL SYSPROC.SYSTS_CLEANUP('', 'en_US', ?)

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

Return Status = 0
Example 2: Output where an error is returned:
SQL0462W  Command or routine "SYSTS_CLEANUP" (specific name "*N") has returned a
warning SQLSTATE, with diagnostic text "CIE00212W 2 of 20 collections could not be
deleted. Check db2diag.log for details.". SQLSTATE=01H14