DB2 Version 9.7 for Linux, UNIX, and Windows

SYSTS_ALTER procedure - Change the update characteristics of an index

This procedure changes the update characteristics of an index.

The procedure issues an ALTER INDEX text search administration command on the database server.

Syntax


>>-SYSTS_ALTER--(--index_schema--,--index_name--,--------------->

>--| update characteristics |----------------------------------->

>--| options  |--,--message_locale--,--message--)--------------><

update characteristics

|--+--------------------------------------------+--------------->
   '-UPDATE FREQUENCY--+-NONE-----------------+-'   
                       '-| update frequency |-'     

>--+----------------------------+-------------------------------|
   '-UPDATE MINIMUM--minchanges-'   

update frequency

|--D-(-+-*------------+-)--H-(-+-*------------+-)--------------->
       | .-,--------. |        | .-,--------. |     
       | V          | |        | V          | |     
       '---integer1-+-'        '---integer2-+-'     

       .-,--------.     
       V          |     
>--M-(---integer3-+-)-------------------------------------------|

options

|--+----------------------------------+-------------------------|
   +-| index configuration options  |-+   
   '-| activation options |-----------'   

index configuration options

|--+-------------------------------------------+----------------|
   '-INDEX CONFIGURATION--(-| option-value |-)-'   

option-value

|--+--------------------------------------+---------------------|
   '-UPDATEAUTOCOMMIT--commitcount_number '   

activation options

|--+-----------------------------------+------------------------|
   SET--+-ACTIVE---+--+------------+---'   
        '-INACTIVE-'  '-UNILATERAL-'

The schema is SYSPROC.

Procedure parameters

index_schema

An input argument of type VARCHAR(128) that specifies the schema of the text search index. The index_schema must follow the naming restriction for DB2® schema names. If the argument is null or an empty string, the value of CURRENT SCHEMA is used. The index_schema is case-sensitive.

index_name

An input argument of type VARCHAR(128) that specifies the name of the index. Together with index_schema, it uniquely identifies a text search index in a database. The index_name is case-sensitive.

update characteristics

An input argument of type VARCHAR(32K) that specifies the alter options. The alter options allowed are as follows:

UPDATE FREQUENCY

Specifies the frequency with which index updates are made. The index will be updated, if the number of changes is at least the value set for UPDATE MINIMUM. The update frequency NONE indicates that no further index updates will be made. This can be useful for a text column in a table with data that will not change. It is also useful when the user intends to manually update the index (using the UPDATE INDEX command). Automatic updates can only be done if the START FOR TEXT command has been run and the DB2 Text Search instance services are running.

The default frequency value is taken from the view SYSIBMTS.TSDEFAULTS, where DEFAULTNAME='UPDATEFREQUENCY'.

NONE

No automatic updates will be applied to the text index. Any further index update will have to be started manually.

D

The day(s) of the week when the index is updated.

*: Every day of the week.
integer1: Specific days of the week, from Sunday to Saturday: 0 to 6

H

The hour(s) of the specified day(s) when the index is updated.

*: Every hour of the day.
integer2: Specific hours of the day, from midnight to 11 pm: 0 to 23

M

The minute(s) of the specified hour(s) when the index is updated.

integer3: Specified as top of the hour (0), or in multiples of 5 minute increments after the hour: 0, 5, 10, 15, 20, 25, 30, 35, 40, 45, 50 or 55

If you do not specify the UPDATE FREQUENCY option, the frequency settings are left unchanged.

UPDATE MINIMUM minchanges

Specifies the minimum number of changes to text documents that must occur before the index is incrementally updated. Multiple changes to the same text document are treated as separate changes. If you do not specify the UPDATE MINIMUM option, the setting is left unchanged.

INDEX CONFIGURATION (option-value)

Starting with Version 9.7 Fix Pack 3 and later fix packs, this is an optional input argument of type VARCHAR(32K) that allows altering text index configuration settings. The following option is supported:

Table 1. Specifications for option-value
Option	Value	Data type	Description
UPDATEAUTOCOMMIT	`commitcount_number`	Integer	Specifies the number of index updates after which a commit is executed to preserve the previous work automatically for either initial or incremental updates. For initial updates, the index update will process batches of documents from a base table after the trigger to capture data updates is activated. After the amount of documents updated reaches the `COMMITCOUNT` number, the server will do an interim commit. Log entries generated by unprocessed documents will be removed from staging table. Using the `UPDATEAUTOCOMMIT` option for an initial text index update will lead to a significant increase of execution time. For incremental updates, log entries which have been processed will be removed correspondingly from staging table with each interim commit. `COMMITCOUNT` counts the number of documents updated, not the number of staging table entries.

activation options

Starting with Version 9.7 Fix Pack 3 and later fix packs, this input argument of type integer sets the status of a text index.

ACTIVE: Sets the text index status to active

INACTIVE: Sets the text index status to inactive

UNILATERAL: Specifies a unilateral change which only affects the status of DB2 Text Search indexes. If this argument is specified, only the status of a DB2 Text Search index is changed to active or inactive. Without the UNILATERAL argument, the activation status of the DB2 Text Search and DB2 Net Search Extender indexes is jointly switched so that only one of the text indexes is active.

Note: After altering the status of a text search index from active to inactive, or vice versa, issue the FLUSH PACKAGE CACHE DYNAMIC command. This removes all cached dynamic SQL statements from the package cache and forces the next request for the same SQL statement to be implicitly compiled by DB2.

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.

Authorization

The privileges held by the authorization ID of the statement must include at least one of the following authorities:

CONTROL privilege on the table on which the text index is defined
DBADM authority

Examples

Example 1: In the following example, the update characteristics of a text search index are being altered. This index was originally created with index_schema 'db2ts' and index_name 'myTextIndex'. By using 'UPDATE FREQUENCY NONE', the intention is to make no further updates to the text search index as possibly no changes are expected for the associated table column. Any error messages are requested to be returned in English. When the procedure succeeds, the output parameter message indicative of the successful operation is returned to the caller.

  CALL SYSPROC.SYSTS_ALTER('db2ts', 'myTextIndex', 
   'UPDATE FREQUENCY NONE', 'en_US', ?)

The following example is a sample output from this query.

  Value of output parameters
  --------------------------
  Parameter Name  : MESSAGE
  Parameter Value : Operation completed successfully.

  Return Status = 0

Example 2: In the following example, the SYSTS_ALTER stored procedure is called to alter the update-characteristics for a text search index with index_schema 'db2ts' and index_name 'myTextIndex'. The intention is to ensure that updates to the index occur every hour on the hour. However, this index does not exist and results in an error.

  CALL SYSPROC.SYSTS_ALTER('db2ts', 'myTextIndex', 
   'update frequency D(*) H(*) M(0)', 'en_US', ?)

The following example is a sample output from this query.

  SQL20427N An error occurred during a text search administration 
  procedure or command. The error message is "CIE00316 Text search 
  index "db2ts"."myTextIndex" does not exist. ". SQLSTATE 38H14

Usage notes

Text search administration 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 for the connection.
Note: You may run into a deadlock if a transaction is not committed before executing a text search administration operation, as the same database objects may be affected and the operation waits for the previous step to commit. This may occur, for example, if AUTOCOMMIT is turned off and a table is created followed by creating a text index without an explicit commit between the two transactions.
Multiple procedures or commands cannot be run concurrently on a text search index if they might conflict. Some of the conflicting procedures and commands are:
- SYSTS_ALTER procedure or ALTER INDEX db2ts command
- SYSTS_CLEAR_EVENTS procedure or CLEAR EVENTS FOR INDEX db2ts command
- SYSTS_DISABLE procedure or DISABLE DATABASE FOR TEXT db2ts command
- SYSTS_DROP procedure or DROP INDEX db2ts command
- STOP FOR TEXT db2ts command
- SYSTS_UPDATE procedure or UPDATE INDEX db2ts command
If there is a conflict, the procedure returns an SQLCODE -20426 and SQLSTATE 38H13.
When this procedure is run,
- the content of the DB2 Text Search view SYSIBMTS.TSLOCKS is updated.
- the index entry in the Text Search Index Data file is updated. The file contains a persistent representation of update schedules (also empty ones) for each index in the instance.

The result of activating indexes depends on the original index status. The following table describes the results.

Table 2. Status changes without invalid index:
Initial DB2 Text Search or Net Search Extender Status	Request Active	Request Active Unilateral	Request Inactive	Request Inactive Unilateral
Active / Inactive	No change	No change	Inactive / Active	Inactive / Inactive
Inactive / Active	Active / Inactive	Error	No change	No change
Inactive / Inactive	Active / Inactive	Active / Inactive	Inactive / Active	No change

SQL20427N and CIE0379E error messages are returned for active index conflicts.