Changes to the data column resulted from INSERT, UPDATE, and DELETE statements are tracked in the staging table associated to the text index. Upon successful completion of an UPDATE INDEX command or CALL SYSTS_UPDATE statement, the text index will be synchronized with the base table rows.

Let's use an example to demonstrate the above. Run the Adhoc SQL on the right.


Insert a row into BOOKS

This step stimulates activities on the BOOKS table. A row is inserted to the table.


INSERT INTO books
VALUES ( '456-456456456'
       , 'John Doe'
       , 'The Database Book'
       , 2005
       , XMLPARSE(DOCUMENT ' John Doe The Database BookThe ultimate book about contemporary databases.  2005 55.00176'))

Populate the text indexes

To synchronize the text index with the changed data, update the text indexes.


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 synchronized with the base table rows.


Text search with XPath expression

Search the XML documents again. The recently added row is also returned.


SELECT author, year, substr(title,1,30)
  FROM books
 WHERE CONTAINS ( bookinfo
                , '@xpath:''/bookinfo/story [. contains("database")]''') = 1