SYSTS_UPGRADE_CATALOG procedure - Upgrade the text search catalog

This procedure upgrades the Text Search catalog, including the administrative tables and administrative views, to the latest product version.

This procedure creates new catalog tables and views which are used in the latest version of the product and also updates the existing catalog tables and views. It removes obsolete catalog tables and views.

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

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. Calling the SYSTS_UPGRADE_CATALOG procedure upgrades the text search catalog tables, and if the procedure succeeds, the output parameter message indicating successful operation is returned to the caller.

CALL SYSPROC.SYSTS_UPGRADE_CATALOG('en_US',?)

Value of output parameters
--------------------------
Parameter Name  : MESSAGE
Parameter Value  : CIE0213W The Db2 Text Search Catalog has been upgraded to 
the current version. You will now need to update the text search index using 
the SYSPROC.SYSTS_UPGRADE_INDEX stored procedure. 
index also needs to be updated by calling "SYSPROC.SYSTS_UPGRADE_INDEX".
  		
Return Status = 0

Example 2: In the following example, the database was not enabled for Text Search in an older release before calling the procedure. A NULL value in the place message_locale means the a default locale of 'en_US' will be used. All error messages are returned in English.

   CALL SYSPROC.SYSTS_UPGRADE_CATALOG('',?)

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 are already upgraded to the current version.

CALL SYSPROC.SYSTS_UPGRADE_CATALOG('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

Usage notes

The SYSTS_UPGRADE_CATALOG procedure is integrated into the DB2 UPGRADE DATABASE command to perform the text search specific catalog upgrade. If the database upgrade fails to upgrade the text search catalog, the SYSTS_UPGRADE_CATALOG procedure must be executed separately by the user to complete the database upgrade.

To upgrade the text search index catalog, proceed as follows:

Make sure that the Text Search instance service is stopped.
Execute the SYSTS_UPGRADE_CATALOG procedure.
Note: 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.
The SYSTS_UPGRADE_CATALOG procedure attempts to populate the Text Search server information in the catalog. Review the SYSIBMTS.TSSERVER content and update the Text Search server information as necessary.
Upgrade the text search indexes by following the procedure outlined for the 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.