Create and update a text search index

You can create a text search index by defining and declaring the properties of the index. You can update a text search index by adding new data from a DB2® table to the index. You can also update a text search index by changing the existing data in the index.

For each text search index that you create, a new collection is created on the OmniFind Text Search Server for DB2 for i. After initial creation, the text search index contains no data.

You add data to the text search index by calling the SYSPROC.SYSTS_UPDATE stored procedure. The first update process adds all the text documents from the text column to the text search index. This process is known as the initial update. The subsequent updates are incremental.

When a text search index is created, the following objects are created or updated:
  • The staging table is created in the QSYS2 library.
  • The INSERT, DELETE, and UPDATE triggers are added to the base table.
  • An SQL view with the name of the text search index is created in the schema of the text search index. This view contains information about the text index. For example, the view can be used to obtain the base table name and the staging table name. The view also shows the number of pending changes to the base table that are not yet reflected in the text search index.
  • The text search index catalogs (SYSTEXTINDEXES and SYSTEXTCOLUMNS) in the QSYS2 library are updated with a new entry added for the new text search index.
Staging table considerations:
  • Do not perform any DB operation on the staging table except saving and restoring the file, or changing authorities.
  • If you are changing the authorities on the base table, change the authorities on the staging table also.
Base table considerations:
  • Do not remove the DELETE, UPDATE, and INSERT triggers that are added when a text search index is created.
  • Dropping the text search index removes the triggers.
  • Do not alter or remove the ROWID, primary key, or unique column that was used as the key in the text search index.
  • Altering the data column of the base table that results in data truncation might result in false positive matches in the text search index.