You can now create text indexes on text data types and XML data types. For each text index, a text search collection is created on the file system. A text search collection holds the significant terms that are extracted from the documents.

Run the SQL in the Ad Hoc command window to the right. Note that case IS CASE SENSITIVE for the schema, index and table name used in the CALL commands.

Create a text index on column TITLE


db2ts "CREATE INDEX mytitleidx FOR TEXT ON books(title)"

Here in the Technology Explorer, we create the index using the administrative SQL routine.


CALL SYSTS_CREATE('MYSCHEMA','MYTITLEIDX', 'MYSCHEMA.BOOKS(TITLE)', '','en_US', ?);

Create a text index on column BOOKINFO


db2ts "CREATE INDEX myxmlidx FOR TEXT ON books(bookinfo)"

Here in the Technology Explorer, we create the index using the administrative SQL routine.


CALL SYSTS_CREATE('MYSCHEMA','MYXMLIDX', 'MYSCHEMA.BOOKS(BOOKINFO)', '','en_US', ?);

Few objects are created as a result of the previous command:


Populate text indexes

After creating a text index, an empty index structure has been prepared. Updating the text index for the first time will load and index the data from the column for which the text index was created. DB2 Text Search uses linguistic processing to parse the texts and documents.


db2ts "UPDATE INDEX mytitleidx FOR TEXT"
db2ts "UPDATE INDEX myxmlidx FOR TEXT"

Here in the Technology Explorer, we update the indexes using the administrative SQL routine.


CALL SYSTS_UPDATE('MYSCHEMA', 'MYTITLEIDX', '', 'en_US', ?);
CALL SYSTS_UPDATE('MYSCHEMA', 'MYXMLIDX'  , '', 'en_US', ?);

Upon successful completion of every UPDATE INDEX command or CALL SYSTS_UPDATE statment, the text index is marked for incremental update. Changes are recorded in a staging table so that the text index will be synchronized with the base table rows.