db2ts ALTER INDEX command

For execution, the command needs to be prefixed with db2ts at the command line.

Authorization

Prerequisite

The instance owner must hold DBADM with DATAACCESS authority. The SYSADM no longer holds SECADM nor DBADM privilege in Version 9.7. SECADM must explicitly grant DBADM with DATAACCESS authority to the instance owner before running the ALTER INDEX command.

Required connection

Database

Command syntax

>>-ALTER INDEX--index-name--FOR TEXT---------------------------->

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

>--| connection options |--------------------------------------><

update characteristics

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

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

update frequency

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

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

incremental update characteristics

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

options

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

index configuration options

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

option-value

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

activation options

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

connection options

|--+----------------------------------------------------------------+--|
   '-CONNECT TO--database-name--+---------------------------------+-'   
                                '-USER--username--USING--password-'     

Command parameters

ALTER INDEX index-name

The schema and name of the index as specified in the CREATE INDEX command. It uniquely identifies the text search index in a database.

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 the 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.

CONNECT TO database-name

This clause specifies the database to which a connection will be established. The database must be on the local system. If specified, this clause takes precedence over the environment variable DB2DBDFT. This clause can be omitted if the following are all true:

• The DB2DBDFT environment variable is set to a valid database name.
• The user running the command has the required authorization to connect to the database server.

USER username USING password

This clause specifies the username and password that will be used to establish the connection.

Usage notes

[A-Za-z][A-Za-z0-9@#$_]*

"[A-Za-z ][A-Za-z0-9@#$_ ]*"

Changes to the database: Updates the DB2 Text Search catalog information.