SYSTS_CONFIGURE procedure - Configure current database for text search

The SYSTS_CONFIGURE procedure applies text search server connection information to the text search catalog.

Certain text search server properties have to be reflected in the text search administration tables (text search catalog). These properties are associated with a text index when the index is created. When parameters such as the token are updated periodically, the change has to be reflected in the database and the properties for all the indexes have to be updated as well.

This procedure is required initially for:

Incomplete enablement
Stand-alone text search server setups
Partitioned databases
and further on, following any updates to text search server connection information

For subsequent updates, ensure that no text search administrative operation is active and shut down the currently configured text search server.

During database enablement the SYSIBMTS.TSSERVER administrative view is updated with connection information for the integrated text search server. Review and update the text server information in the SYSIBMTS.TSSERVER view with the relevant text search server data and run the SYSTS_CONFIGURE procedure to apply the updated information. For multiple databases in the instance, configure each database with the information for the same text search server.

Generally the sequence of operations is as follows:

Configure a text search server. Integrated text search servers can be configured during installation or when a database instance is created. A stand-alone text search server is configured separately.
Enable a database for text search by using the db2ts ENABLE command, or the SYSTS_ENABLE or SYSTS_ADMIN_CMD procedures with the ENABLE option.
Determine parameters for the text search server as needed for the SYSIBMTS.TSSERVERS view.
Update the SYSIBMTS.TSSERVERS administrative view with the parameters for the text search server.
- If the view is empty then use an INSERT statement. For example:
```
INSERT INTO SYSIBMTS.TSSERVERS (HOST, PORT, TOKEN, SERVERSTATUS) 
VALUES ('localhost', 55000, '9kfsjg48=', 0); 
```
- If the view already contains a row then use a SQL UPDATE statement. For example:
```
UPDATE SYSIBMTS.TSSERVERS SET (HOST, PORT, TOKEN) = 
('tsmach1.ibm.com', 55002, 'k3j4fjk9u=')
```
Execute the SYSTS_CONFIGURE procedure.

Authorization

The privileges held by the authorization ID of the statement must include the SYSTS_ADM role.

Default PUBLIC privilege

None

Syntax

The schema is SYSPROC.

Procedure parameter

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.

message-locale

An input argument of type VARCHAR(33) that specifies the locale to be used for any returned error message. If the argument is null, 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 an operation that is considered successful.

Example

Example 1: Prepare a database for text search services and return any messages in English.

CALL SYSTS_ENABLE('', 'en_US', ?)"

INSERT INTO SYSIBMTS.TSSERVERS                         
 (HOST,PORT,TOKEN,KEY,LOCALE,SERVERTYPE,SERVERSTATUS)   
  VALUES ('tsmach1.ibm.com', 55000, '9kfsjg48=', 'en_US', 0);  
                     
CALL SYSPROC.SYSTS_CONFIGURE('', 'en_US', ?)

An example of output from this query:

Value of output parameters 
-------------------------- 
Parameter Name  : MESSAGE 
Parameter Value : Operation completed successfully. 
  
Return Status = 0

Usage notes

A SYSIBMTS.TSSERVERS view is created when a database is enabled for text search. This view is updated with information about the integrated text search server during database enablement. If the text search server configuration cannot be obtained, the enable operation will end with an 'incomplete enablement' warning.
The SYSTS_CONFIGURE procedure must be issued anytime a row is inserted or updated into SYSIBMTS.TSSERVERS. Make sure that no text search administration operation is active and shut down the text search server before updating SYSIBMTS.TSSERVERS.
When updating SYSIBMTS.TSSERVERS in a database, all text search enabled databases should be updated with the same parameters. Only a single text search server is supported with a given database instance.
Running the SYSTS_CONFIGURE procedure for a database registers the use of the configured text search server for the instance. Not running the procedure does not result in a severe error but the response to some commands can be unexpected.
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
- SYSTS_DISABLE procedure
- SYSTS_CONFIGURE procedure
- SYSTS_UPDATE procedure
If there is a conflict, the procedure returns an SQLCODE -20426 and SQLSTATE 38H13.
Certain aspects relating to the text search installation and database instance configuration for text search have to be updated. They include:
- An indication whether the search server utilized by the database instance is integrated (configured by the database system as part of the database instance), or if it is a separate stand-alone installation of the ECMTS server.
- An indication if the text search setup is enabled for rich text support.