Updating a text search index

You can update a text search index automatically or manually. Automatic updates occur based on how you defined the update frequency for the text search index. You can update indexes manually by issuing a command or by calling a stored procedure.

Before you begin

Updating a text search index requires the SYSTS_MGR role and either the CONTROL privilege or DATAACCESS authority on the target table.

About this task

After creating and updating (filling) the text search index for the first time, you must keep it up to date. For example, when you add a text document to a database or change an existing document in a database, you must index the document to keep the content of the text search index synchronized with the content of the database. Also, when you delete a text document from a database, you must remove its terms from the text search index.

You should plan periodic indexing carefully because indexing text documents is a time- and resource-consuming task. The time taken depends on many factors, including how big the documents are, how many documents you added or changed since the previous text search index update, and how powerful your processor is.

The Administration Tool's status option can be used to retrieve information about the progress of document updates while the db2ts UPDATE INDEX command is running. If an index update is still in progress when a new update starts, the new update fails.

Automatic updates
To have text search index updates performed automatically, use one of the following commands to set an UPDATE FREQUENCY:
- db2ts CREATE INDEX
- db2ts ALTER INDEX
The UPDATE FREQUENCY parameter has a minimum setting of five minutes. The UPDATE MINIMUM parameter specifies the minimum number of text changes that must be queued.

If there are not enough changes in the staging table for the specified day and time, the text search index is not updated.
Manual updates
There are also times when you want to update a text search index immediately. For example, after you create a text search index, when the index is still empty, or after you have added several text documents to a database and want to search.
To fill or synchronize (update) a text search index with the table data, use one of the following methods:
- Issue the UPDATE INDEX command:
```
db2ts "UPDATE INDEX index-name FOR TEXT"
```
- Call the SYSPROC.SYSTS_UPDATE administrative SQL routine.

Example

For example, suppose that there are two text search indexes on the PRODUCT table: MYSCHEMA.MYTEXTINDEX on the NAME column and MYSCHEMA.MYXMLINDEX on the DESCRIPTION column. A new entry is added to PRODUCT as follows:

	INSERT INTO PRODUCT VALUES ('100-104-01', 'Wheeled Snow Shovel', 99.99, NULL, 
	NULL, NULL, XMLPARSE(DOCUMENT '<product xmlns="http://posample.org/wheelshovel" 
	pid="100-104-01"><description><name>Wheeled Snow Shovel</name>
	<details>Wheeled Snow Shovel, lever assisted, ergonomic foam grips, gravel wheel,
	clears away snow 3 times faster</details><price>99.99</price>
	</description></product>'))

To make the information in the new entry searchable, issue the following command:

	db2ts "UPDATE INDEX MYSCHEMA.MYTEXTINDEX FOR TEXT"

To make the information in the new entry searchable, use the following stored procedure:.

	db2 "call sysproc.systs_update('MYSCHEMA', 'MYXMLINDEX', '', 'en_US', ?)'