SYSPROC.SYSTS_RESTORE

Invoke the SYSPROC.SYSTS_RESTORE stored procedure to restore a text search index from a Db2 administration table to a text search server. After this stored procedure completes successfully, the new server is used for searching and index maintenance.

Use this stored procedure in the following two scenarios:
  • Invoke this stored procedure after a Db2 restore operation that involves the text search administration tables and the tables that have been indexed for full-text. This stored procedure restores the correct version of the text search index on the text search server that is listed in the index entry (row) in the SYSIBMTS.SYSTEXTINDEXES table. In this scenario, the serverID parameter of SYSPROC.SYSTS_RESTORE stored procedure must be null.
  • When you prepare for a planned outage of a text search server, invoke this stored procedure to copy the specified text search index to a different server. After this stored procedure completes successfully, the new server is used for text searches. In this scenario, you must not create new text search indexes on the server for which the outage is planned. You can prevent the SYSPROC.SYSTS_CREATE stored procedure from choosing the text search server that is planned for an outage by updating the STATUS column for the server entry in the SYSIBMTS.SYSTEXTSERVERS table to a value other than zero.

The original text search server remains unchanged, and the text search index remains searchable while this stored procedure is running. If the specified text search index already exists on the target server, the text search index is replaced if the version of the collection on the text search server does not match the version in the Db2 index table.

While this stored procedure is running, the SYSPROC.SYSTS_UPDATE stored procedure, the SYSPROC.SYSTS_DROP stored procedure, and the SYSPROC.SYSTS_TAKEOVER stored procedure will fail for the specified text search index.

Note: All stored procedures can perform COMMIT statements on the current connection. This capability makes it possible to keep changes on the text search server consistent with your Db2 subsystem. After you invoke a stored procedure, you cannot roll back the changes that are made on your Db2 subsystem or the text search server. Rollback in a stored procedure is done only to a savepoint that is set when you invoke the stored procedure.

Prerequisites

Before you invoke the SYSPROC.SYSTS_RESTORE stored procedure, verify the following prerequisites:
  • Db2 text search functionality was started by invocation of the SYSPROC.SYSTS_START stored procedure.
  • The text search index was created (by invocation of the SYSPROC.SYSTS_CREATE stored procedure).
  • The DB2ENCRYPTEDPW column in the SYSIBMTS.SYSTEXTSERVERS table has a valid, encrypted password value for the user ID that the text search server uses to connect to Db2 for z/OS®.
  • The following stored procedures are not running for the text search index that you want to restore: SYSPROC.SYSTS_CREATE, SYSPROC.SYSTS_UPDATE, SYSPROC.SYSTS_DROP, and SYSPROC.SYSTS_TAKEOVER.
  • At least one text search server is running.
  • The distributed data facility (DDF) must be started, even if you call the stored procedure locally on your z/OS system. The DDF is required to allow the text search server to connect to Db2 to store a text search index.

Authorization

The user ID under which this stored procedure is invoked must have the following privileges:
  • EXECUTE on the procedure
  • EXECUTE on the packages SYSIBMTS.*
  • INDEX privilege on the table that the text search index is created on

In addition, the user ID that is listed in the SYSIBMTS.SYSTEXTCONNECTINFO table must have SELECT, INSERT, UPDATE, and DELETE privileges for the text search index table. These privileges must be valid for the user ID after the user ID is used to connect to Db2 for z/OS on a T4 Java™ connection and going through DRDA connect processing.

Syntax

Read syntax diagramSkip visual syntax diagramSYSTS_RESTORE(indexSchemanull,indexName,serverIDnull)

The schema qualifier is SYSPROC.

Parameters

indexSchema
Identifies the schema of the text search index. If this parameter is null, the value of the CURRENT SCHEMA special register for the invoker is used.

Recommendation: Use a valid SQL name for this parameter.

The data type of this parameter is VARCHAR(128).

indexName
Identifies the name of the text search index. The name of the text search index together with the index schema uniquely identifies the text search index in the Db2 subsystem. You must specify a non-null value for this parameter.

Recommendation: Use a valid SQL name for this parameter.

The data type for this parameter is VARCHAR(128).

serverID
Identifies the server that you want to use as the new server to handle text searching and index maintenance. The server is defined by the SERVERID column in the SYSIBMTS.SYSTEXTSERVERS table.

If this parameter is null, the stored procedure uses the server ID that is listed in the SYSIBMTS.SYSTEXTINDEXES table for this search index.

Example

This example shows how to use the Db2 CALL statement to invoke the SYSPROC.SYSTS_RESTORE stored procedure. This example uses the SYSPROC.SYSTS_RESTORE stored procedure to restore a text search index from a Db2 administration table to a text search server. After this stored procedure completes successfully, the new server is used for searching and index maintenance.

CALL SYSPROC.SYSTS_RESTORE ('SCHEMA1','IVC2','2')
Here is a list of the parameters used in this example.
  • 'SCHEMA1' = indexSchema
  • 'IVC2' = indexName
  • '2' = serverID

In this example the serverID is set to 2 so that the stored procedure restores the index to the text search server that has '2' as the serverID.