DB2 Version 10.1 for Linux, UNIX, and Windows
DB2 Version 10.1 for Linux, UNIX, and Windows
To improve performance, a text search index is not synchronized with its associated user table within the scope of a DB2® transaction that updates, deletes text documents from, or inserts text documents into that table. Instead, text search indexes are updated asynchronously.
Staring in DB2 Version 10.1 and later fix packs, to facilitate the asynchronous update of a text search index, a staging table (also known as a log table), is created for each text search index. Triggers on the text table capture any changes, for example, inserts, deletes, or updates, to a text column that the text search index is associated with and write them to the staging table. An auxiliary staging table may be used to capture changes that are recognized through integrity processing. The updates to the text search index are applied at a later stage, during either a manual update or an automatic update. The update is made to a copy of a very small part of the index. During the update, you can still perform searches on the index, but you cannot access the updated text search index until the synchronization is complete.
When using the updateautocommit option, consider that each commit cycle requires significant processor usage, which increases the total time for completing an index update. Setting this updateautocommit option is especially useful for initial updates, or updates that involve all rows or a majority of rows, if the total elapsed time is large to avoid losing completed work due to a rollback caused by a system or server failure.
Staring in DB2 Version 10.1 Fix Pack 3 and later fix packs, to facilitate the asynchronous update of a text search index, create a staging table, which is also known as a log table, for each text search index. With the default logtype BASIC option enabled, triggers are created on the text table to capture any changes to a text column that the text search is associated with. The triggers then write these changes to the staging table. In cases where the use of triggers is not possible or not required, you can use the logtype CUSTOM option to create a logtable without adding triggers to the text table. With the logtype CUSTOM option, there is no automatic detection of changes for incremental updates. Instead, you must manually populate the logtable parameter.
You can use an auxiliary staging table to capture changes that are recognized through integrity processing. The updates to the text search index are applied at a later stage, during either a manual update or an automatic update. The update is made to a copy of a small part of the index. During the update, you can still do searches on the index, but you cannot access the updated text search index until the synchronization is complete.
Text index update processing provides a feature to specify the commit size by using the updateautocommit argument. To provide further control, more settings are now available to determine whether the commit size must be treated as rows or hours and to help determine how many batches to process. For example, with the committype hours option, you can control how much time is acceptable for a potential reprocessing in case of failure, such as, 2 hours or 4 hours.
If you set the commitcycle parameter, an initial update processes data in index key order and saves the last committed key. This key is then used to continue the process when the update is restarted. For an incremental update, the log entries are deleted after a cycle is completed, and there is no need for a committed key to restart processing. However, new changes on previously processed keys are processed again before the incremental update continues with the remaining keys.
Consider that each commit cycle requires significant processor usage if using the updateautocommit or commitcycle options, which increases the total time for completing an index update. You should set these options for updates that have a large total elapsed time, such as initial updates or updates that involve all or most of the rows. By using these settings you can avoid losing completed work due to a rollback that is caused by a system or server failure.