ALTER INDEX

The command changes the characteristics of a full-text index, for example, the update options and the storage options.

Authorization

According to DB2 catalog views, the user ID in this command must have CONTROL privilege on the table for which the full-text index was created.

Command syntax

Read syntax diagramSkip visual syntax diagram>>-ALTER-INDEX--+------------------+--index-name--FOR-TEXT------>
                '-index-schema-"."-'

>--+--------------------------+--+-------------------+---------->
   '-|update-characteristics|-'  '-|storage-options|-'

>--+----------------------+------------------------------------><
   '-|connection-options|-'

storage-options:

|--+----------------------------+------------------------------->
   '-INDEX-DIRECTORY--directory-'

>--+-------------------------------+---------------------------->
   '-WORK-DIRECTORY--workdirectory-'

>--+------------------------------------------------+----------->
   '-CACHE TABLE--+-PERSISTENT--+---------------+-+-'
                  |             '-IN--directory-' |
                  '-TEMPORARY---------------------'

>--+---------------------+--+-----------------------------+-----|
   '-PCTFREE--percentage-'  '-MAXIMUM CACHE SIZE--memsize-'

update-characteristics:

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

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

>--+-------------------------------+----------------------------|
   '-COMMITCOUNT-FOR-UPDATE--count-'

update-frequency:

|--D--(--+-*-------+--)--H--(--+-*--------+--)--M--------------->
         | .-,---. |           | .-,----. |
         | V     | |           | V      | |
         '---0...6-+-'           '---0...23-+-'

      .-,----.
      V      |
>--(----0...59-+--)-----------------------------------------------|

connection-options:

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

Command parameters

index-schema
The schema of the text index as specified in the CREATE INDEX command. If no schema is specified, the user ID of the DB2 connection is used.
index-name
The name of the text index as specified in the CREATE INDEX command.
INDEX DIRECTORY directory
The directory path where the text index is stored. As the directory will contain index data, ensure that the directory has read/write and run permissions for the DB2 instance owner user ID.

Note that in a distributed DB2 environment, this directory has to exist on every node. A subdirectory, NODE<nr>, is created under the directory to distinguish indexes on logical nodes of a server. Any index files from the previous index directory are deleted.

WORK DIRECTORY workdirectory
Stores temporary files during search and administration operations. You can change the separate work directory independently of a new index directory.

If the directory does not exist, it is created for the DB2 instance owner user ID. If it exists, ensure that the directory has read/write permissions on UNIX platforms for the instance owner.

Note that in a distributed DB2 environment, this directory has to exist on every node. A subdirectory, NODE<nr>, is created under the directory to distinguish indexes on logical nodes of a server. Any temporary index files from the previous index directory are deleted.

CACHE TABLE PERSISTENT IN directory
Specifies that after a deactivation or system reboot, the cached table in CREATE INDEX is persistent. In either case, this allows for a fast ACTIVATE CACHE execution. The persistent cache is stored in the specified directory.

The previously created persistent cache is moved to a new location. This location always requires a deactivated index.

CACHE TABLE TEMPORARY
Specifies that the cached result table is now temporary and any previously existing persistent cache has been deleted. Note that this change requires a deactivated index.
MAXIMUM CACHE SIZE memsize
Specifies the new maximum size of the cached table to be built during ACTIVATE CACHE. Specify the memsize parameter in megabytes as a positive integer.

If the integer is too small, the ACTIVATE CACHE command fails. The actual cache size is calculated during the ACTIVATE CACHE command. This change requires a deactivated index.

PCTFREE percentage
Specifies the percentage of the cache held free for additional documents. The percentage must be an integer value less than 100 and greater or equal to 0. Note that the previous persistent cache is deleted and that this change requires a deactivated index. See ACTIVATE CACHE.
UPDATE FREQUENCY
Using the following parameters, the index update frequency determines when the update occurs:
  • D. The day(s) of the week when the index is updated: * (everyday) or 0..6 (0=Sunday)
  • H. The hour(s) when the index is updated: * (every hour) or 0..23
  • M. The minute(s) when the index is updated: 0..59
  • NONE. No further index updates occur. This is intended for a text column in which no further changes are made.
If you do not specify the UPDATE FREQUENCY keyword, the frequency settings are left unchanged.
UPDATE MINIMUM minchanges
The minimum number of changes allowed for text documents before the index is incrementally updated. If you do not specify the UPDATE MINIMUM keyword, the setting does not change.

Note that you can only change the UPDATE MINIMUM if you did not create the index using the RECREATE ON UPDATE option.

COMMITCOUNT FOR UPDATE count
For update processing, you can specify a commitcount. See UPDATE INDEX for further information. This applies to both the UPDATE command and the UPDATE FREQUENCY specification, which schedules update processing.

Note that you can only change COMMITCOUNT if you did not create the index using the RECREATE ON UPDATE option.

Also note that you cannot change COMMITCOUNT if you did create the index with the REPLICATION clause.

CONNECT TO database-name
The name of the database that is target for this command. You can omit this parameter, if DB2DBDFT is set and the user is running the command on the server. Note that the user ID must have the required DB2 authorizations.
USER userid USING password
Use a password and userid to connect to the database. If not specified, a connection is attempted from the current user ID without a password.

Usage

You cannot issue the command if one of the following commands is running on the index:

If you create the index with a cache option, you can not use the ALTER INDEX command for the index directory when the index is activated. You must first deactivate the cache.

In a distributed DB2 environment, a text index with cache options is only allowed on a single-noded tablespace.

Changes to the database
Change DB2 Net Search Extender catalog views.
Changes to the file system
  • Creation of NODE<nr> subdirectories in the index, and work directories
  • Moving of index files
  • Creation of persistent cache directories
  • Moving of persistent cache files