Asynchronous index updates

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.

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. 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 results in significant memory usage, which increases the total time for completing an index update. Setting a commitcount 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.

To calculate a commitcount value, consider that calculations for text index processing are very different from in-database transactions. Each cycle is treated as a complete index update and therefore executes preparation and closing steps, including necessary index segment merges or index reorganizations. Very short commit cycles will therefore significantly increase total elapsed time and should be avoided.