DB2 Version 10.1 for Linux, UNIX, and Windows

db2ts UPDATE INDEX command

The db2ts UPDATE INDEX command updates the text search index (collection in DB2® Text Search) to reflect the current contents of the text column with which the index is associated. While the update is being performed, a search is possible. Until completion of the update, the search operates on a partially updated index.

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

Authorization

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

DATAACCESS authority
CONTROL privilege on the table on which the text index is defined
INDEX with SELECT privilege on the base table on which the text index is defined

In addition, for an initial update the authorization requirements apply as outlined in the CREATE TRIGGER statement.

Required connection

Database

Command syntax


>>-UPDATE INDEX--index-name--FOR TEXT--+----------------+------->
                                       '-UPDATE OPTIONS-'   

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

connection options

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

Command parameters

UPDATE INDEX index-name

Specifies the name of the text search index to be updated. The index name must adhere to the naming restrictions for DB2 indexes.

UPDATE OPTIONS

An input argument of type VARCHAR(32K) that specifies update options. If no options are specified the update is started unconditionally. The possible values are:

UPDATE OPTIONS value	Description
USING UPDATE MINIMUM	This option enforces the use of the UPDATE MINIMUM value that is defined for the text search index and processes updates if the specified minimum number of changes have occurred.
FOR DATA REDISTRIBUTION	This option specifies that a text search index in a partitioned database needs to be refreshed after data partitions were added or removed and a subsequent data redistribution operation completed. Search results may be inconsistent until the text search index was updated with the FOR DATA REDISTRIBUTION option.
ALLROWS	This option specifies that an initial update should be attempted unconditionally.
UPDATEAUTOCOMMIT	This option 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 number of documents updated reaches the `COMMITCOUNT` number, the server does an interim commit. Log entries generated by unprocessed documents 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 which have been processed are removed correspondingly from staging table with each interim commit. `COMMITCOUNT` counts the number of documents updated, not the number of staging table entries. Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, 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 operational identifier '4'. This key is used to restart update processing either after a failure or after the number of specified `commitcycles` are completed. If a `commitcycles` is specified, 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`	Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, specifies rows or hours for the UPDATEAUTOCOMMIT index configuration option. The default is rows.
COMMITCYCLES`commitcycles`	Starting in DB2 Version 10.1 Fix Pack 3 and later fix packs, 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`.

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. This clause can be omitted 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 authorization name and password that are 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. In addition, there are some additional restrictions. For example, these identifiers can only be of the form:

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

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

If synonym dictionaries have been created for a text index, executing the ALLROWS and FOR DATA REDISTRIBUTION update options removes dictionaries from existing collections. There can be new collections associated with the text index after database partitions are added. The synonym dictionaries for all associated collections have to be added again.

This command does not return until all index update processing is completed. The duration depends on the number of documents to be indexed and the number of documents already indexed. The collection name for the index can be retrieved from the SYSIBMTS.TSCOLLECTIONNAMES view (column COLLECTIONNAME).

Multiple commands cannot be executed concurrently on a text search index if they might conflict. If you run this command while a conflicting command is running, an error occurs and the command fails, after which you can try to run the command again. The following commands are some of the common conflicting commands:

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

Note: In cases of individual document errors, the documents must be corrected. The primary keys of the erroneous documents can be looked up in the event table for the index. The next UPDATE INDEX command reprocesses these documents if the corresponding rows in the user table are modified.

Changes to the database:

Insert rows to the event table (including parser error information from DB2 Text Search).
Delete from the index staging table in case of incremental updates.
Before first update, create triggers on the user text table.
The collection is updated.
New or changed documents are parsed and indexed.
Deleted documents are discarded from the index.

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.

When you specify UPDATEAUTOCOMMIT, COMMITTYPE or COMMITSIZE values for the update operation, they override existing configured values only for the specific update and are not persisted.