SYSTS_UPGRADE_INDEX - Upgrade text search indexes

This procedure updates Text Search index information in database catalog tables and text search catalog tables.

Text search index collections are managed by the Text Search server. See Upgrading Text Search for more information.

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_UPGRADE_INDEX(message_locale,message)

The schema is SYSPROC.

Procedure parameters

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, the database was enabled for text search in an older release and the procedure SYSTS_UPGRADE_CATALOG has already been completed successfully. The procedure SYSTS_UPGRADE_INDEX will complete the upgrade for the text index metadata in the database catalog. When the procedure succeeds, the output parameter message indicative of the successful operation is returned to the caller.
CALL SYSPROC.SYSTS_UPGRADE_INDEX('en_US', ?)

Parameter Name  : MESSAGE
Parameter Value  : CIE00001 Operation completed successfully. 

Return Status = 0
Example 2: In the following example, the database was not enabled for text search in the older release of the product. If a NULL value is set for message_locale, it means the system locale will be used. If the system locale is not available then the default locale 'en_US' will be used.
CALL SYSPROC.SYSTS_UPGRADE_INDEX('', ?)

SQL20427N  An error occurred during a text search administration procedure 
or command. The error message is "CIE0323E Specified or default database 
not enabled for text. ". 
SQLSTATE=38H14
Example 3: In the following example, the Text Search catalog and the text search indexes were already upgraded to the current version. 
CALL SYSPROC.SYSTS_UPGRADE_INDEX('en_US', ?)

Value of output parameters
--------------------------
Parameter Name  : MESSAGE
Parameter Value  : CIE0002I The Db2 Text Search release level is current for 
the database. The system has not been upgraded. 

Return Status = 0
Example 4: In the following example, the Text Search catalog was not upgraded. 
CALL SYSPROC.SYSTS_UPGRADE_INDEX('en_US', ?)

CIE0409E The Db2 Text Search catalog has not been upgraded to the current version.

Usage notes

  • The SYSTS_UPGRADE_INDEX procedure is integrated into the DB2 UPGRADE DATABASE command to perform upgrades for text search indexes. When issuing the DB2 UPGRADE DATABASE command , this procedure will be executed as well. If the upgrade of text search indexes fails, the procedure must be run manually.
  • The SYSTS_UPGRADE_CATALOG procedure should be executed before SYSTS_UPGRADE_INDEX procedure. The version value in the SYSIBMTS.TSDEFAULTS administrative view will not be updated until the SYSTS_UPGRADE_INDEX procedure is executed successfully.
  • Text Search administrative procedures use an existing connection to the database. The current transaction might be committed or rolled back depending on the completion of the procedures. As such, you might want to commit all transaction changes to avoid any unexpected impact from such a commit or rollback. One way to achieve this is to turn on AUTOCOMMIT.