After creating and updating the text index for the first time, you must keep the text index 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 index synchronized with the content of the database. Likewise, when you delete a text document from a database, its terms must be removed from the index.
If the text index was created without the RECREATE INDEX ON UPDATE option, triggers automatically store information about new, changed, or deleted documents in an internal log table. So, the next time an index update takes place, the documents referenced in the log table are indexed. For a text index on views, see Text indexes on views for additional information.
If you specify the RECREATE option in the CREATE INDEX command, the index is totally rebuilt for each update. This option creates no log table or triggers.
Typically you update an index at intervals. You can change the update frequency for an existing index by using the ALTER INDEX command.
You specify the index update frequency in terms of when the update is to be made, and the minimum number of text changes that must be queued. If there are not enough changes in the log table at the day and time given, the index is not updated.
You should plan periodic indexing carefully; to index text documents is a time- and resource-consuming task. The time taken is dependent on many factors. These include the size of the documents, how many text documents have been added or changed since the previous index update, and how powerful the processor is.
Note |
---|
On a DB2 table, rollback and deadlock situations might occur in the following cases:
|
The UPDATE INDEX command lets you update an index immediately on request.
Summary |
---|
|
The following command updates the index:
db2text UPDATE INDEX comment FOR TEXT
This command is useful when you have added several text documents to a database and want to search them immediately.
To determine if manual reorganization is necessary, query the db2ext.textindexes view by using the following command:
db2 "select reorg_suggested from db2ext.textindexes where INDNAME = 'comment'"
If you specify MANUAL REORGANIZATION and often update a column, the update process becomes slower. To manually reorganize, use the following command:
dbtext UPDATE INDEX comment FOR TEXT reorganize
However, if you specify AUTOMATIC REORGANIZE during CREATE INDEX, the index will be automatically reorganized when necessary.