Create text-search indices
After the database has been enabled for text search, you can create text-search indices on text data types and XML data types. For each text-search index, a text-search collection is created on the file system, which holds the significant terms that are extracted from the documents.
DB2 Text Search supports the following document formats:
- Text: Plain text document format
- HTML: Hypertext Markup Language
- XML: Extensible Markup Language
To create a plain text-search index on the "title" column, run the following command:
Listing 9. Command to create a text-search index on column 'title'
db2ts "CREATE INDEX myschema.mytitleidx FOR TEXT ON books(title)"
To create an index on the "bookinfo" XML document column, run:
Listing 10. Command to create a text-search index on column 'bookinfo'
db2ts "CREATE INDEX myschema.myxmlidx FOR TEXT ON books(bookinfo)"
Note that a text-search index that is created on an XML data type column is automatically created using the document format "XML", which enables using XQUERY/XPath syntax in your search queries. For other data types, the default format is "TEXT."
After completion of the command, you find an empty index, new insert, update, and delete triggers on the data column to record the changes to the data, and additional system catalog tables to manage the new text-search index.
To view the index characteristics, use the informational views provided by DB2 text search:
Listing 11. Query informational views
SELECT * FROM sysibmts.tscollectionnames SELECT * FROM sysibmts.tsindexes
Note that an event view and a staging view have been created for the new text-search index. The event view holds informational and error messages about index administration operations, while the staging view represents the change log for the data updates, which is used to synchronize the content of the data table with the content of the text-search index.
After creating a text-search index, an empty index structure has been prepared. Updating the text-search index for the first time (initial update) will load and index the data from the column for which the text-search index was created. DB2 Text Search uses linguistic processing to parse the texts and documents.
To populate the text-search indices, use the following commands:
Listing 12. Command to populate the text-search indices
db2ts "UPDATE INDEX myschema.mytitleidx FOR TEXT" db2ts "UPDATE INDEX myschema.myxmlidx FOR TEXT"
After successful completion, the text-search index is marked such that
each subsequent update command is executed as an incremental update
that uses the change log recorded in the staging view. Incremental
updates are applied asynchronously, that is, only when an
UPDATE INDEX command is issued, the text
search index is synchronized with the base table rows. The update can
be started manually, as shown here, or controlled through the
scheduling mechanism. For details about scheduling options, see the
DB2 Text Search User Guide.