SYSTS_ENABLE procedure - Enable current database for text search

The SYSTS_ENABLE procedure enables Text Search for the current database.

This procedure must be issued successfully before text search indexes on columns within the database can be created.

This procedure issues the ENABLE DATABASE FOR TEXT text search administration command on the database server.

Authorization

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

Default PUBLIC privilege

None

Syntax

Read syntax diagramSkip visual syntax diagramSYSTS_ENABLE( options,message_locale,message)
options
Read syntax diagramSkip visual syntax diagramADMINISTRATION TABLES IN tablespace-name

The schema is SYSPROC.

Procedure parameters

options
An input argument of type VARCHAR(32K) that specifies the options to be used. If no options are needed, the argument can be null or an empty string. Supported values are:
ADMINISTRATION TABLES IN table-space-name
Specifies the name of an existing regular table space for administration tables created while enabling the database for Text Search. If this clause is not specified, SYSTOOLSPACE is used as the table space. Regardless of whether SYSTOOLSPACE or an explicitly specified table space name is used, the table space has to meet certain requirements. It has to be:
  • A regular table space
  • For partitioned databases it would be recommended to define a bufferpool and table space with 32KB page size
To use a case-sensitive tablespace-name use a delimited identifier by enclosing the name in double quotation marks. By default it is treated as an ordinary identifier and converted to uppercase.
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: Enable the database for text search by creating administration tables in a table space with any output messages in English. 

  CALL SYSPROC.SYSTS_ENABLE ('ADMINISTRATION TABLES IN TSSPACE', 'en_US', ?)

Enable the database for text search by creating administration tables in a table space with any output messages in French.

  CALL SYSPROC.SYSTS_ENABLE ('ADMINISTRATION TABLES IN "tbs32k" ', 'fr_FR', ?)

As in previous releases, the SYSTS_ENABLE procedure is supported without the options argument, for example:

  CALL SYSPROC.SYSTS_ENABLE ('en_US', ?)
An example of output for this query:
  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : Operation completed successfully.

  Return Status = 0

Example 2: In the following example, SYSTS_ENABLE is called on a database that is already enabled for text search. This results in an error message to the caller.

  CALL SYSPROC.SYSTS_ENABLE('en_US', ?)
An example of output for this query:
  SQL20427N An error occurred during a text search administration 
  procedure or command. The error message from the text search 
  product is "CIE00322 Specified or default database already 
  enabled for text. ". 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.
  • When this procedure is run, the following events occur:
    • This procedure creates database objects, such as text search administration catalog tables and views, in the schema SYSIBMTS.
    • The established database defaults for text search index are available in view SYSIBMTS.TSDEFAULTS.
    • When the command has successfully completed, the text search catalog tables and views are created and are available.
  • When executing this procedure, if you do not have sufficient execution and file access privileges to retrieve the Text Search server configuration, the procedure will create the text search catalog with an 'Incomplete enablement' warning. In this case, the Text Search server connection information will need to be updated manually.