Text search indexes on nicknames for a federated database

In Db2® Version 10.5 Fix Pack 1 and later fix packs, you can create a text index on nickname tables for a federated database that point to tables in a remote database.

Nicknames, like base tables, must have a primary key to be eligible for text index support. When you create a text index on a nickname, entries are created in the SYSIBMTS schema in the text search catalog, and an informational text search index object is added to the SYSIBM.SYSINDEXES system catalog table. You can view the object by using the SYSIBMTS.TSINDEXES administrative view.

Indexes on nicknames behave in the same manner as indexes on base tables, with the following exceptions:
  • Only the LOGTYPE index configuration option with a value of CUSTOM is supported. By default, the LOGTYPE option is set to CUSTOM. If you set the LOGTYPE option to BASIC, an error message is returned.
  • Nickname tables are not supported in a partitioned database environment.
  • XML non-relational nicknames are not supported.

In addition, you cannot create triggers on nicknames. To populate the log table for incremental updates, use an approach such as time stamps or replication.

Also, MQT staging tables on nicknames are not supported for capturing updates in the base table. The AUXLOG option for capturing incremental updates of a text search index on a nickname is not supported either. To propagate the changes into a text index on a nickname, you can use either of the following approaches:
  • Manually replicate or update the log table; then, perform manual index updates to copy the changes
To search text search indexes on nicknames, you can use the CONTAINS or SCORE function.
Remember: If a text search index exists on a table, a DROP operation on the nickname table fails.

Example

Example 1: Create a text search index on a nickname table and search the document:
  1. Create a text search index on the db2ts.nktable nickname table for the firstnme column:
    db2ts "create index db2ts.nkidx for text on db2ts.nktable (firstnme)"
  2. Search the document by using the CONTAINS scalar function:
    db2 "select empno from db2ts.nktable where contains(firstnme,'JOHN') = 1"
Example 2: Manually populate the log table and update the index:
  1. Insert some data, where mypk is an integer primary key and last_modified is a field of type TIMESTAMP in the base table:
    insert into sysibmts.tsstaging_12345 (select 1, last_modified, mypk where
last_modified > sometime)
  2. Update the index:
    db2ts "update index db2ts.nkidx for text"