db2ts ALTER INDEX command

The db2ts ALTER INDEX command changes the update characteristics of an index.

Important: Net Search Extender (NSE) is no longer supported in Db2®. Use the Db2 Text Search feature.

For execution, you must prefix the command with db2ts at the command line.

Authorization

The privileges that are held by the authorization ID of the statement must include the SYSTS_MGR role and at least one of the following authorities:

DBADM authority
ALTERIN privilege on the base schema
CONTROL or ALTER privilege on the base table on which the text search index is defined

To change an existing schedule, the authorization ID must be the same as the index creator or must have DBADM authority.

Required connection

Database

Command syntax

update characteristics

update frequency

incremental update characteristics

options

index configuration options

option-value

activation options

connection options

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 is updated if the number of changes is at least the value that is set for UPDATE MINIMUM parameter. The update frequency NONE indicates that no further index updates are made. This can be useful for a text column in a table with data that does not change. It is also useful when you intend to manually update the index (by using the UPDATE INDEX command). You can do automatic updates only if you have issued the START FOR TEXT command 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 are applied to the text index. Any further index updates are started manually.

D

The days of the week when the index is updated.

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

H

The hours of the specified days when the index is updated.

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

M

The minutes of the specified hours 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 remain 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)

Specifies 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
SERIALUPDATE	`updatemode`	Integer	Specifies whether the update processing for a partitioned text search index must be run in parallel or in serial mode. In parallel mode, the execution is distributed to the database partitions and run independently on each node. In serial mode, the execution is run without distribution and stops when a failure is encountered. Serial mode execution usually takes longer but requires less resources. 0 = parallel mode 1 = serial mode
UPDATEAUTOCOMMIT	`commitsize`	String	Specifies the number of rows or number of hours after which a commit is run to automatically preserve the previous work for either initial or incremental updates. If you specify the number of rows: After the number of documents that are updated reaches the COMMITCOUNT number, the server applies a commit. COMMITCOUNT counts the number of documents that are updated by using the primary key, not the number of staging table entries. If you specify the number of hours: The text index is committed after the specified number of hours is reached. The maximum number of hours is 24. For initial updates, the index update processes batches of documents from the base table. After the `commitsize` value is reached, update processing completes a COMMIT operation and the last processed key is saved in the staging table with the operational identifier '4'. Use this key to restart update processing either after a failure or after the number of specified `commitcycles` are completed. If you specify a `commitcycles` , the update mode is modified to incremental to initiate capturing changes by using the LOGTYPE BASIC option to create triggers on the text table. However, until the initial update is complete, log entries that are generated by documents that have not been processed in a previous cycle are removed from the staging table. Using the UPDATEAUTOCOMMIT option for an initial text index update leads to a significant increase of execution time. For incremental updates, log entries that are processed are removed correspondingly from the staging table with each interim commit.
COMMITTYPE	`committype`	String	Specifies rows or hours for the UPDATEAUTOCOMMIT index configuration option. The default is rows.
COMMITCYCLES	`commitcycles`	Integer	Specifies the number of commit cycles. The default is 0 for unlimited cycles. If cycles are not explicitly specified, the update operation uses as many cycles as required based on the batch size that is specified with the UPDATEAUTOCOMMIT option to finish the update processing. You can use this option with the UPDATEAUTOCOMMIT setting with a `committype`.

activation options

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 that affects the status of Db2 Text Search indexes. If you specify this argument, 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.

CONNECT TO database-name

This clause specifies the database to which a connection is established. The database must be on the local system. If specified, this clause takes precedence over the environment variable DB2DBDFT. You can omit this clause if the following statements 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 user name and password that is used to establish the connection.

Usage notes

All limits and naming conventions that apply to Db2 database objects and queries also apply to Db2 Text Search features and queries. Db2 Text Search related identifiers must conform to the Db2 naming conventions. Also, there are some additional restrictions, such as identifiers of the following form:

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

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

You cannot issue multiple commands concurrently on a text search index if they might conflict. If a command is issued while another conflicting command is running, an error occurs and the command fails, after which you can try to run the command again. Some of the conflicting commands are:

ALTER INDEX
CLEAR EVENTS FOR INDEX
DROP INDEX
UPDATE INDEX
DISABLE DATABASE FOR TEXT

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

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.

You can specify the UPDATEAUTOCOMMIT index configuration option without type and cycles for compatibility with an earlier version. It is associated by default with the COMMITTYPE rows option and unrestricted cycles.

You can specify the UPDATEAUTOCOMMIT, COMMITTYPE and COMMITSIZE index configuration options for an UPDATE INDEX operation to override the configured values. Values that you submit for a specific update operation are applied only once and not persisted.